Pessimistic locking

Question

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.

Answer 1

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'

 

 

Answer 2

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?