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'
No comments:
Post a Comment