Transaction Concurrency in C#

Question

user 1 :

begin tran
select * from items with(nolock);
insert into orders (odate) values(getdate());

insert into OrderData values((select max(orderid) from Orders with(nolock)),1,1);
update Items set qih=qih-1 where item_id=1;
select * from OrderData where oid=(select max(orderid) from Orders with(nolock));

insert into OrderData values((select max(orderid) from Orders with(nolock)),2,1);
update Items set qih=qih-1 where item_id=2;
select * from OrderData where oid=(select max(orderid) from Orders with(nolock));

commit tran;

User 2 :

begin tran
select * from items with(nolock);
insert into orders (odate) values(getdate());

insert into OrderData values((select max(orderid) from Orders with(nolock)),1,1);//in here waiting this user

after commit user1. user 2 last statement is executeing.

But I want to execute this user 2 last statement not waiting. How do I do it.

Please Help me.

score 3 · Answer 1 · answered Dec 25 '11 at 17:41

Reading without observing locks is supportable, since the worst case is that you cause data integrity issues (phanton/non-repeatable reads) to the SPID that requested the nolock - which is fine: it was self-inflicted.

Writing without observing locks is not, AFAIK, supported. Since this would allow you to cause data integrity issues to other SPIDs. And that is most definitely not OK.

So basically; to the best of my knowledge: you can't. You'll have to wait to get the lock.

The best way of avoiding delays on locks is to ensure that transactions do the minimum work necessary to ensure a coherent change (and without external operations in the middle of a transaction).

Transaction Concurrency in C#

1 Answers1