Pessimistic locking
I am attempting to try a pesimitic 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 but it is not locking.
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 windows 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 first 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.
__________________
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================
|
