CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Mr. Smith
Mr. Smith

Reputation: 4506

Does TABLOCKX order matter?

Let's say I have three stored procedures: ProcA, ProcB, and ProcC. They all use cursors, have branching (via IF statements), and all perform SELECT / UPDATE / INSERT statements.

In addition, on each table that they reference, they apply the table-hint 'TABLOCKX'. Furthermore, these stored procedures are always called from a transaction.

My concern though is that, since these procedures use cursors and branching, it seems like the order in which they lock tables could matter. For example, the order in which tables are used in the stored procedures vary:

+--------+--------+--------------------+--------------------+
| ProcA  | ProcB  | ProcC (scenario 1) | ProcC (scenario 2) |
+--------+--------+--------------------+--------------------+
| TableA | TableA | TableB             | TableD             |
| TableB | TableC | TableC             | TableA             |
| TableD | TableE | TableD             | TableC             |
+--------+--------+--------------------+--------------------+

So, if ProcA and ProcC both started at the same time, would SQL Server know to block ProcC until ProcA finished, since ProcC might need TableA (depending on the conditions of the branching)?

If SQL Server permitted both to run at the same time, then if (scenario 2) was reached for ProcC, wouldn't it deadlock? Since ProcA was given a lock of TableA and ProcC was given a lock to TableD, and now ProcA needs a lock on TableD and ProcC needs a lock on TableA?

Upvotes: 0

Views: 97

Answers (1)

Ben Thul
Ben Thul

Reputation: 32707

You've presented two scenarios; I'll answer each in turn.

First, locks are obtained when the resources they reference are referenced, not in a preemptive fashion. So, to answer your question, the call to ProcC won't lock tableA until and unless the logic reaches a point where the call to tableA is executed.

Secondly, you're correct about the deadlock possibility. Generally speaking, if you lock the resources in the same order, deadlocks don't happen. Conversely, if you don't lock them in the same order, you've violated the Geneva Convention by putting a landmine in your code. In this case, if you take out the red herrings, tableA and tableD are locked in opposite order between the two procedures. So, there's a chance that they'll deadlock if they're executed simultaneously.

Upvotes: 1

Related Questions