1

When we issue a select statement , it puts a shared lock and during shared lock other transactions can not do an update. So i am trying to do the following as shown in the below code.

I have started a transaction and then put a delay for 50 seconds. I open other query window and try to do a update to the same record. I was expecting the update should be get blocked for 50 seconds , but it just executes. Is that after select shared lock is released , any way i put in wait shared mode.

begin tran
select * from tblCustomer where CustomerId=1
WAITFOR DELAY '00:00:50'
commit tran

When i do this for update it works. I mean when i put update inside begin tran and committ tran and execute select in committed mode , select blocks.

Shivprasad Koirala
  • 27,644
  • 7
  • 84
  • 73

2 Answers2

2

Your shared lock is immediately released after the select gets executed even it is inside the transaction.

If you have an intention to update the record after select, in that case you need to acquire a UPDLOCK lock like following.

DECLARE @IdToUpdate INT
SELECT @IdToUpdate = ID from tblCustomer WITH (UPDLOCK) where CustomerId=1  
UPDATE [tblCustomer]
SET X=Y
WHERE ID=@IdToUpdate

This will take the necessary Update lock on the record in advance and will stop other sessions to acquire any lock (shared/exclusive) on the record

PSK
  • 17,547
  • 5
  • 32
  • 43
  • I thinks Shared lock will work for update lock ,please correct me if i am wrong. Must you would like to modify the last sentence "will stop other sessions to acquire any lock which is exclusive on the record" I know i am nit picking but just in case if some is reading it he can think other wise. – Shivprasad Koirala Feb 12 '19 at 06:02
  • I still feel shared locks also not allowed otherwise it may lead to a deadlock. I’ll still try and update the answer if required – PSK Feb 12 '19 at 06:16
0

This behavior follows from your SELECTs transaction isolation level. SQL Server uses by default READ COMMITTED as transaction isolation level, in which shared locks are acquired and released immediately. Should you want to use an exclusive lock for your SELECT then you need to use transaction level SERIALIZABLE.

Thailo
  • 1,314
  • 7
  • 13