Skip to main content

Return to Answer

Misread the case, adjusted answer for the correct case
Source Link
Henk Kok
Henk Kok
  • 383
  • 6
  • 10

In both cases the selects use a shared lock and the updates an exclusive lock.

In READ COMMITTED mode, the shared lock is released immediately after the select finishes.

In REPEATABLE READS mode, the shared locks for the selects are held untill the end of the transaction, to ensure that no other sessions can change the data that was read. A new read within the same transaction is garantueed to yield the same results, unless the data was changed in the current session/transaction

SoOriginally I thought, that you executed "First" in both sessions. Then the REPEATABLE READ scenario,explanation would be trivial: both session 1 and 2sessions acquire and get a shared lock successfully and the selects do not block each other. Next, which then blocks the sessions want to acquire an exlusiveexclusive lock required for the updates, but can't because the other.

The situation with a second session doing only an update is holding a shared locklittle more complex. AAn update staement will first acquire an update lock (UPDLOCK) for selecting the rows that must be updated, which is probably similar to a shared lock does, but at least not block otherblocked by a shared lockslock. Next, but preventswhen the data is actually updated, it tries to convert the update lock to an exclusive locks. Thislock, which fails, because the first session is still holding the deadlock situation: both sessions hold a shared lock that blocks the. Now both sessions block each other session and neither session can unblock, because it can never finish its transaction.

In both cases the selects use a shared lock and the updates an exclusive lock.

In READ COMMITTED mode, the shared lock is released immediately after the select finishes.

In REPEATABLE READS mode, the shared locks for the selects are held untill the end of the transaction, to ensure that no other sessions can change the data that was read. A new read within the same transaction is garantueed to yield the same results, unless the data was changed in the current session/transaction

So in the REPEATABLE READ scenario, both session 1 and 2 acquire a shared lock successfully and the selects do not block each other. Next, the sessions want to acquire an exlusive lock for the updates, but can't because the other session is holding a shared lock. A shared lock does not block other shared locks, but prevents exclusive locks. This is the deadlock situation: both sessions hold a shared lock that blocks the other session and neither session can unblock, because it can never finish its transaction.

In both cases the selects use a shared lock and the updates an exclusive lock.

In READ COMMITTED mode, the shared lock is released immediately after the select finishes.

In REPEATABLE READS mode, the shared locks for the selects are held untill the end of the transaction, to ensure that no other sessions can change the data that was read. A new read within the same transaction is garantueed to yield the same results, unless the data was changed in the current session/transaction

Originally I thought, that you executed "First" in both sessions. Then the explanation would be trivial: both sessions acquire and get a shared lock, which then blocks the exclusive lock required for the updates.

The situation with a second session doing only an update is a little more complex. An update staement will first acquire an update lock (UPDLOCK) for selecting the rows that must be updated, which is probably similar to a shared lock, but at least not blocked by a shared lock. Next, when the data is actually updated, it tries to convert the update lock to an exclusive lock, which fails, because the first session is still holding the shared lock. Now both sessions block each other.

Source Link
Henk Kok
Henk Kok
  • 383
  • 6
  • 10

In both cases the selects use a shared lock and the updates an exclusive lock.

In READ COMMITTED mode, the shared lock is released immediately after the select finishes.

In REPEATABLE READS mode, the shared locks for the selects are held untill the end of the transaction, to ensure that no other sessions can change the data that was read. A new read within the same transaction is garantueed to yield the same results, unless the data was changed in the current session/transaction

So in the REPEATABLE READ scenario, both session 1 and 2 acquire a shared lock successfully and the selects do not block each other. Next, the sessions want to acquire an exlusive lock for the updates, but can't because the other session is holding a shared lock. A shared lock does not block other shared locks, but prevents exclusive locks. This is the deadlock situation: both sessions hold a shared lock that blocks the other session and neither session can unblock, because it can never finish its transaction.