0

I read on google that deadlock can be managed by using UPDLOCK table hint. But the below scenario does not avoid deadlock:-

              -----session 1------------
set transaction isolation level repeatable read

begin transaction
select * from c with (updlock)

                -----session 2------------

set transaction isolation level repeatable read
declare @var int = 1

begin transaction
select * from c

Now when we go back to session 1 and issue a update query we will get deadlock in session 2. How did it avoid deadlock then and there is no use of updlock. We should use tablockX hint instead which does not allow any type of locks.

              -----session 1------------
update c
set name = 'qwert'
where id = 1

I found another strange observation. If i change the isolation level in the first session to serializable then updlock works and avoids deadlock.So my understanding is -

  1. Updlock will avoid deadlocks only when serializable isolation level is selected.
  2. When isolation levels other than serializable is used then shared locks from other sessions are allowed which results in deadlock.We should use tablockx in such cases.
  3. When serializable isolation is used then shared locks from other sessions are not allowed which helps in avoiding deadlocks.

Can someone help me here I am confused badly

sam
  • 1,242
  • 3
  • 12
  • 31
  • The post that you have suggested says - Other sessions can still see the data. They just cannot obtain locks that are incompatiable with the UPDLOCK and/or HOLDLOCK. This means shared locks can not be imposed by other sessions. But if you look at my statements, session 2 is reading the data and also applying shared locks. – sam Aug 16 '15 at 18:42
  • U-locks *are* compatible with S-locks, which is stated in the accepted answer and in the linked Lock Compatibility Matrix. – GSerg Aug 16 '15 at 18:48
  • ok so my question how does it reduces deadlocks then?Another question why UPDLOCK is not allowing shared locks when serializable isolation is used instead.? – sam Aug 16 '15 at 18:52
  • 1
    It may reduce deadlocks between two instances of the same query that *both* use updlock, but even then, you use an explicit updlock to avoid race conditions when selecting a value and only later updating it, rather than to avoid a deadlock. To quote the duplicate answer, *Playing with lock hints w/o an absolute complete 110% understanding of the locking semantics is begging for trouble*. Serializable isolation places an X-lock instead of the U-lock, which is not compatible with S-locks (see the answer and the matrix again). – GSerg Aug 16 '15 at 18:54
  • Thanks a Billion..I got it – sam Aug 16 '15 at 19:02

0 Answers0