Pessimistic locking

I am attempting to try a pesimistic lock, meaning that i want to lock a row or table for a period of time and then relase it when i am done. To test this i wrote the following:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANsaction
Select * From configurationitem WITH (ROWLOCK,xlock)
where name = 'NextReceiptNumber' and category = 'AR';

Declare @.i int
set @.i = 0
while @.i < 300000
Begin
print @.i
set @.i = @.i + 1
end
COMMIT TRANsaction

To test, while the above is looping i open another query window and select from the same table using the following:

Select ConfigurationItemValue From configurationitem where ItemID = 418

This does not work because this query returns IMMEDIATELY. However, if I change the query to the following:

Select ConfigurationItemValue From configurationitem where name = 'NextReceiptNumber' and category = 'AR';

It does not return until the transaction query above is finished (which is the way it should work).

So, my question is, why does it not lock when i select by a primary key but lock when i do NOT select by a primary key (ItemID is a primary key).

thanks in advance.

Ok, I think we are missing something here. Is the primary key value for this row = 418? You should only have an exclusive lock on the row.

This is the table that I tested with, and it did wait when I looked for 418, and not for any other row. Any query that requires a table scan (snapshot isolation not withstanding) will not be able to complete (which would be the case for a query that looks for name and category, no matter what your values are.) This is because other queries will take a lock on every row in the table eventually and will get stuck on the locked rows.

drop table configurationItem
go
create table configurationItem
(
itemId int primary key,
name varchar(100),
category char(2),
configurationItemValue varchar(10)
)
insert into configurationItem
select 418,'NextReceiptNumber','AR','sals'
union all
select 2,'asldfjlka','AT','sals'
union all
select 3,'aqjsadklfaj','DR','sals'
union all
select 4,'ao2ioi23jkasd','DD','sals'
union all
select 5,'alifdjald','CD','sals'
union all
select 6,'ajsdflkasdlkja','CF','sals'
union all
select 7,'juqoiwfewoijlk','TT','sals'
union all
select 8,'asdancas','QR','sals'

|||Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design. So you will have to use a more aggressive locking hint like UPDLOCK with ROWLOCK. But what are you trying that requires such pessimistic locking strategies? Why do you want to do row-by-row procedural processing? Can't you use set-based operations instead?

Persisting Code (Repost)

I have created a SRS report with 6 columns. I would like every 3rd Row of
the report to have a silver background and white on the rest. When I run
the report the first time I get the desired result. Then, depending on the
number of rows the report returns, successive runs of the report will have
the silver row starting on 1, 2 or 3. My guess is that SRS is remembering
where the code left off and picks up from there on the successive runs.
I added the following code to my report:
Private Shared count As Integer = 0
Private Shared colors As String() = {"White","White","White","White","White","White","White","White","White","White","White","White","Silver","Silver","Silver","Silver","Silver","Silver"}
Public Function GetColor() As String
Dim c as string = colors(count Mod colors.Length)
count = count + 1
Return c
End Function
Then, on the background Color I have:
=Code.GetColor()
George F Grund IVWould removing the shared keyword achieve your desired results?
You could also try and reset the count by creating a function like
below and then calling from your page header.
public function ResetVariable() as string
count = 0
return ""
end function|||Well, I'll asumme that you are using a table with no groups. If so, what you
are trying to do is very simple and you don't that code. Select the detail
row, then in the background color porperty type something like this:
=IIF(RowCount("YourDataSet") mod 3 = 0, "#D3D3D3", "#FFFFFF")
D3D3D3 is the RBG code for Light Gray, and FFFFFF is for White
I hope this helpssql

Persistent autoincrementing value, not attached to row insertion?

I need to get a unique value to use for a record *before* the record is added to a table. It doesn't have to be contiguous with existing records, but it must always be unique, has to be persistent over multiple instantiations of an ASP.NET application, and has to work in that sort of a multi-session environment (where other sessions could need additional unique values before the first session gets around to actually adding a record to the table).

I considered generating and using unique CLSIDs for this, but the resulting value also needs to become part of the filename of some files that are being saved to the disk (and those names also saved in the table), and including text CLSIDs along with other filename data would make for some unpleasantly long and difficult to work with filenames.

I also don't think there's a practical way for me to use a trigger associated with an identity column for this, because I need to save files to disk using the unique value before I even know if the record will in fact end up being added to the table, and what's more, the numbers and names of those files will vary in ways that might be difficult to handle in a stored procedure.

What I'm thinking I will have to do is create a separate database table called something like "UniqueIDGen". This table would have a single record in it with a single integer value, initialized to a value of 1. Then, each time an ID is needed, this one record would be locked, read and incremented by 1. The only reason for doing it this way instead of with an application variable, as I see it, is that the values need to be unique and continue incrementing in perpetuity, no matter how many times the ASP.NET application is recycled or the server is rebooted.

But I still have to wonder if there might be a more efficient method provided by SQL Server for this type of unique value generation ... something that is equally as persistent without requiring an entire table with only a single record to be allocated to such a basic task. Does anyone know of a more elegant solution for this?do a hash of the current date.time.milliseconds, or a unix timestamp in the backend of the asp.net, unless you plan on data being entered in the same second.|||Is there no better option based upon SQL Server or some other persistent technology, other than the workable but awkward solution I mentioned? For some reason I thought there might be a non-table-based identity value, or something like that, specifically for situations like this. Perhaps that is something I'm remembering from some other RDBMS software I dealt with in the past, and not SQL Server?|||Hi,

You could do this, it is a bit like Oracle's NextVal:

CREATE TABLE dbo.Sequences
(
ID int NOT NULL
)

INSERT INTO Sequences (ID) VALUES (0)

CREATE PROCEDURE dbo.NextValue
@.ID int output
AS
UPDATE Sequences SET ID = ID + 1, @.ID = ID + 1
RETURN

And call the stored proc to get the ID.

private void Button1_Click(object sender, System.EventArgs e)
{
string connectionString = @."Server=wpeude-masonix2;Database=TestDatabase;User ID=sa;Password=sa;Trusted_Connection=False";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "dbo.NextValue";
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@.ID", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);
connection.Open();
command.ExecuteNonQuery();
int pk = (int)param.Value;
connection.Close();
lblNextValue.Text = pk.ToString();
}

The ID would be unique in the Database. You could change the sproc to use NEWID() and have a varchar column instead, if you wanted to use a GUID.

A.|||asmason,

Your example is precisely the solution I proposed in my original post (unless I did an insufficient job of describing what I was thinking of). If there's no method available that's superior to creating a table for this purpose, then I'll go ahead and do it that way.

Thanks for taking the time to write it up.|||Couldn't you take and make a stored procedure that makes it for you? Basically when you go to insert a value you do a :

select @.IDVariable = Max(IDField) from Table

Then when you insert:

 Insert into Table (IDField) Values (@.IDVariable)

I have done something very similar to this in several projects and it remains unique and it is available when you create it.|||Regarding the suggestion of:

select @.IDVariable = Max(IDField) from Table

... and then ...

Insert into Table (IDField) Values (@.IDVariable)

I don't think I should do that. In my case, fair amount of time (seconds or even minutes) can potentially elapse between the time I need the unique ID (the first line) and the actual INSERT (the second line). What happens when, when session #1 is sitting at some point in between those two statements, and session #2 comes along and performs the first statement to get an ID for itself? It will get the same one that the first session got ... and then. if both sessions end up inserting a new row using the provided ID ... *biff*, collisions.

In fact, generally it seems like this wouldn't be wholly safe in any multi-threaded environment (even a delay of milliseconds could theoretically lead to a collision, though it might be much less likely than in my current project). Maybe it would be okay if both statements were encased within a single transaction, but that doesn't allow the ID value to be used for things outside of the database, which is a requirement of what I'm working on.

Also, I can only assume that the performance of the MAX function degrades as the number of rows in the table increases, so that may be a bit of a negative as well, at least compared to the standalone "ID table" method.

For what it is worth, I've already gone ahead and implemented a table for this task, very similar to the code that asmason included above, and it works very nicely. I did add one thing: a "counter_name" field, so that the same table could be used to maintain additional unique counters or values if I run across similiar needs again in the future. Thus, the statement in my stored procedure looks like this:

UPDATE AppCounters SET id = id + 1, @.RETURNID = id + 1 WHERE counter_name = @.ctrname

Persistant VariableName error in Row Count task

I am getting this error:

The variable "MyVariable" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

This is my scenario:

I had a Row Count task on a data flow that was writing to a locally scoped variable (called MyVariable for this example.)

I needed to access the value of this variable at the Control Flow (global scope), so I deleted it and recreated it at that level.

My new variable has the same name as the old variable, just different scope.

Now I get this error every time I run the package.

BUT WAIT THERE'S MORE!

I have another Row Count step in the same data flow that is presently writing to a globally scoped variable called "ErrorRows."

If I change this step to write to MyVariable it works fine. If I change my other step to use ErrorRows, it works fine. If I change them back I get the error again.

I have tried deleting and recreating the step, and the variable, and using different names for them. Something is very jiggy with this variable!!

Variables names are case sensitives; make sure you are using the proper casing and that the variable is defined in the right scope.

|||

Can you share the package?

|||

I have discovered that the problem lies not in the Row Count task, but somehow in the Script task that immediately follows it.

I still haven't been able to isolate the issue, but for some reason the error is showing as related to the Row Count task.

(That is why I had been unable to shake the error despite deleting and re-creating the task.)

The script is unexceptional. It is simply incrementing a counter to generate a new row id. For some reason it is using the row count, but since the row count in my present tests is 0 the script should not be executing at all, and the error is not an execution error.

In answer to the suggestion above, the yes the case of the variable name is correct. The steps are all passing validation (no little red circles with x's in them) but failing at runtime.

Dylan.

|||Do you have the variable you are referencing in the Row Count marked as read or read/write in the script component? If so, the issue may be that the variable is locked when the Row Count tries to access it.|||

I have removed the variable altogether from the script step, as I realised on reflection that it really wasn't necessary there.

This really was confounding behaviour, however. The way in which the row count step was flagged in error made it tricky to diagnose what was wrong, so I hope that this thread helps out someone else.

I was unable to determine why the script step was having trouble with the variable. I suspect that the engine was trying to run both count and script at the same time, and there was contention for the variable. The variable was read-only in the script step, however.

Anyway, now onto the next issue...

Persistant VariableName error in Row Count task

I am getting this error:

The variable "MyVariable" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

This is my scenario:

I had a Row Count task on a data flow that was writing to a locally scoped variable (called MyVariable for this example.)

I needed to access the value of this variable at the Control Flow (global scope), so I deleted it and recreated it at that level.

My new variable has the same name as the old variable, just different scope.

Now I get this error every time I run the package.

BUT WAIT THERE'S MORE!

I have another Row Count step in the same data flow that is presently writing to a globally scoped variable called "ErrorRows."

If I change this step to write to MyVariable it works fine. If I change my other step to use ErrorRows, it works fine. If I change them back I get the error again.

I have tried deleting and recreating the step, and the variable, and using different names for them. Something is very jiggy with this variable!!

Variables names are case sensitives; make sure you are using the proper casing and that the variable is defined in the right scope.

|||

Can you share the package?

|||

I have discovered that the problem lies not in the Row Count task, but somehow in the Script task that immediately follows it.

I still haven't been able to isolate the issue, but for some reason the error is showing as related to the Row Count task.

(That is why I had been unable to shake the error despite deleting and re-creating the task.)

The script is unexceptional. It is simply incrementing a counter to generate a new row id. For some reason it is using the row count, but since the row count in my present tests is 0 the script should not be executing at all, and the error is not an execution error.

In answer to the suggestion above, the yes the case of the variable name is correct. The steps are all passing validation (no little red circles with x's in them) but failing at runtime.

Dylan.

|||Do you have the variable you are referencing in the Row Count marked as read or read/write in the script component? If so, the issue may be that the variable is locked when the Row Count tries to access it.|||

I have removed the variable altogether from the script step, as I realised on reflection that it really wasn't necessary there.

This really was confounding behaviour, however. The way in which the row count step was flagged in error made it tricky to diagnose what was wrong, so I hope that this thread helps out someone else.

I was unable to determine why the script step was having trouble with the variable. I suspect that the engine was trying to run both count and script at the same time, and there was contention for the variable. The variable was read-only in the script step, however.

Anyway, now onto the next issue...

Permission at the row level associated with user/login

Hi,
I have the following scenario:
I have a table X with 1000 rows. I want to allow select access to only 300
particular rows to a user/login while another user/login can see the other
700 (for example).
Is this granularity level possible in SQL Server 2005?
Thanks in advance,
Juan Dent, M.Sc.Juan Dent (juanjr@.nospam.nospam) writes:
> I have the following scenario:
> I have a table X with 1000 rows. I want to allow select access to only 300
> particular rows to a user/login while another user/login can see the other
> 700 (for example).
> Is this granularity level possible in SQL Server 2005?
Yes and no. In theory it is simple. You add a table that specifies which
keys that a certain user may see:
CREATE VIEW rowlevelsec_view AS
SELECT ...
FROM tbl t
JOIN accesscontrol c ON t.keycol = c.keycol
WHERE c.userid = SYSTEM_USER
You grant users access on the view, but not on the table. Users can then
only see the rows they are entitled to.
However, it is possible for crafty users to wrestle out information from
the view that they are not permitted to see. It's not that they can read
the rows, but they can infer things from query plans and error messages.
It's not really trivial, but this could matter if the data is very
sensitive.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thnaks, but I was thinking something perhaps new to SQLServer 2005 and at th
e
Transact-SQL level or the like, you know something declarative and part of
the language - not a construction.
Anyone?
Thanks in advance,
Juan Dent, M.Sc.
"Erland Sommarskog" wrote:

> Juan Dent (juanjr@.nospam.nospam) writes:
> Yes and no. In theory it is simple. You add a table that specifies which
> keys that a certain user may see:
> CREATE VIEW rowlevelsec_view AS
> SELECT ...
> FROM tbl t
> JOIN accesscontrol c ON t.keycol = c.keycol
> WHERE c.userid = SYSTEM_USER
> You grant users access on the view, but not on the table. Users can then
> only see the rows they are entitled to.
> However, it is possible for crafty users to wrestle out information from
> the view that they are not permitted to see. It's not that they can read
> the rows, but they can infer things from query plans and error messages.
> It's not really trivial, but this could matter if the data is very
> sensitive.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Juan Dent (juanjr@.nospam.nospam) writes:
> Thnaks, but I was thinking something perhaps new to SQLServer 2005 and
> at the Transact-SQL level or the like, you know something declarative
> and part of the language - not a construction.
I'm afraid that what I presented is what SQL 2005 offers.
See also this white-paper on the topic:
http://www.microsoft.com/technet/pr.../multisec.mspx-
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Juan
http://vyaskn.tripod.com/ row_level...as
es.htm
"Juan Dent" <juanjr@.nospam.nospam> wrote in message
news:6D23828E-4AD9-4456-9E6E-B3836C1E0099@.microsoft.com...
> Hi,
> I have the following scenario:
> I have a table X with 1000 rows. I want to allow select access to only 300
> particular rows to a user/login while another user/login can see the other
> 700 (for example).
> Is this granularity level possible in SQL Server 2005?
> --
> Thanks in advance,
> Juan Dent, M.Sc.