Can you force a SQL Stored Procedure to take locks in a given order

Question

I have two stored procedures, one of which is called a lot and is thus very simple and executes well. Additionally, these actually work very nicely in paralell even though they are serializable.

However I have a second query that is only called in certain circumstances which needs to do many more checks before touching the same tables. Problem is when you run these in parallel you end up with a deadlock as each stored proc locks in different orders.

My question is can you force a given sproc to take it's locks in a specific order to match your important query(s)? That way they will not deadlock and execute nicely :).

EDIT: As a clarification, what I want my second procedure to do is this.

--Lock table1
--Lock table2

--Perform query.

Answer 1

You can provide Table Hints for this.

From MSDN:

Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query processing operation such as a table scan or index seek, or other options.

Just be very very careful, since its very easy to muck up...

For example:

SELECT Id FROM MyTable WITH UPDLOCK

will take an update lock on MyTable until the transaction is complete.

There are a variety hints available for different types of locks. Use whatever is appropriate.

Answer 2

If your 2nd proc needs locks on the 2 tables before performing the query, I don't think it's the order of the locking thats causing the problem, it's just a straightforward deadlock between 2 procs.

Have you tried snapshot isolation? It might not suit your situation, but it can alleviate locking problems.

Also maybe investigate application locks (look in BOL for sp_getapplock/sp_releaseapplock).

Controlling the locks taken, with table hints, I don't think will solve your problem.

IMHO, 99% of deadlocks can be solved with indexes rather than messing with isolation levels, escalated locks or application locks - again might not help you, but without any further detail it's difficult to say.