CODEWITHSUNDEEP
sql-serverdatabaset-sqlsql-server-2005
bouncingHippo
bouncingHippo

Reputation: 6040

database unconfirmed deadlock t-sql 2005

For instance, there exists table A and table B, and i need to process an update in A and then B and I decide to table lock both them during use (as demanded by my architect). Simultaneously, another procedure is called which table locks B, then locks A.

Will this transaction complete? I have a feeling its a deadlock, quite sure of it as it's not releasing any resources...

Upvotes: 0

Views: 90

Answers (2)

Roji P Thomas
Roji P Thomas

Reputation: 540

Yes. This approach will end in a classic cyclic deadlock as mentioned here

Using TABLE level lock for an update is an Overkill. What is the rationale behind doing this ? If you have the correct indexes, locks will be acquired at the key level, which helps multiple processes concurrently access the tables in question.

Still it is a best practice to access the tables in the same order when possible.

Upvotes: 1

mmmmmm
mmmmmm

Reputation: 32651

Yes it is a possible deadlock.

The deadlock scenario is

Your task locks A
Other task locks B

then Your task tries to lock B but it can't as you have the lock and other task tries to lock A but it can't as you have it.

So one of these tasks has to fail/rollback so the other can complete. Depending on RDBMS used the db will choose one of these to terminate.

Often the solution is for a guideline that you must lock resources in the same order in all processes usually this has to be manualy enforced.

Upvotes: 1

Related Questions