CODEWITHSUNDEEP
sqlsql-serverdeadlock
Vicky Thakor
Vicky Thakor

Reputation: 3916

Can deadlock on database tables allows other queries to execute

I'm working around Microsoft SQL Server. I know that it uses parallel processing. But I want to know that, Say two update query on same table X turns into deadlock situation and 3rd query comes for another table Y, so SQL server will allow to run 3rd query or it'll block until SQL server recovers from deadlock? I tried to put scenario in image for better explanation.

enter image description here

Upvotes: 2

Views: 534

Answers (1)

md4
md4

Reputation: 1669

In most normal cases, Q3 will be unaffected by the deadlock between Q1 and Q2, because the locks taken will most likely be page or row locks against table X.

In fact, I can't imagine a scenario where a deadlock could occur if either of the first two queries obtains a table lock. But that's still table X, not Y, which has no existing locks held.

One possible case of Q3 being blocked could occur if either of the others had previously accessed table Y within a transaction with the holdlock option. For example:

Q1

begin transaction;

select * from Y where some_col = 'goats' with (updlock, holdlock);

update X set other_col = 'alpacas' where animal = 'best'; -- deadlock occurs here

commit transaction;

This is pretty contrived, but Q1 has obtained an update lock on table Y that is held until the end of the transaction, blocking any selects from Q3 until the transaction has complete (and the deadlock resolved).

Upvotes: 1

Related Questions