Is there a deterministic way to know whether a T-SQL query on SQL Server will use row locks or page locks?
In the chapter Lock granularity and hierarchies of the documentation it reads:
To minimize the cost of locking, the SQL Server Database Engine locks
resources automatically at a level appropriate to the task.
I have seen a few deadlocks occuring because two delete statements want to delete from a table where foreign key is X and Y. Because rows with X and Y reside in the same page and SQL server deems page locks appropriate the two deletes lock one page and wait for another and this way deadlock each other.
To avoid this I know two options:
- Use an index which enables the queries to aquire key locks (which lock only the rows with either X or Y)
- Kindly ask the query to use row locks with the corresponding query hint
But, how do I know when to start using these options? Do I wait for the deadlock to occur and make changes afterwards or am I able to predict that they will occur and do it preemptively?
1
No, there is no way to determine in advance what locks will be taken. From further on in your linked document
The SQL Server Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query.
I infer it may depend on table statistics, available memory, concurrent load et al. It is deterministic (in that SQL Server is likely not generating random numbers) but is is not well documented, so there’s no way for developers to know in advance.
For multi-statement transactions you can see problems coming in advance. Say one process reads table A, then B then inserts to C. A second reads C then B and updates A. This is a recipe for deadlocks.
For your case it is just bad luck where the rows fall in a page. Tomorrow a new insert may cause a BTree page split and the problem goes away. Next week the index is rebuilt and the problem comes back.
If you know your data well enough you may be able to engineer the referenced key values sufficiently well to offer some hope that they’ll not cohabit. This seems like it will cause more problems than it would solve, though.
Hints aren’t that bad a thing per se. But make sure you include comments explaining what problem the hint is there to solve, and review at some future time.
If transactions are light and deadlocks intermittent (rather than crippling) maybe just adding retry logic to the application is the least painful way forward.
Are you able to provide the deadlock graph as part of your answer?
According to the documentation https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver16#locking-behavior
By default, a DELETE statement always acquires an intent exclusive
(IX) lock on the table object and pages it modifies, an exclusive (X)
lock on the rows it modifies, and holds those locks until the
transaction completes.
The first thing I check when investigating a deadlock is the query used. If it’s not using an index to find the row(s) to delete, then add one appropriate to the query. Making the individual query faster is one of the cheaper ways to solve deadlocks, minimize the chances of conflict.
If the delete is part of a larger set of statements then that may also be part of the issue, with the locks held until everything commits. If your logic allows for it, try committing the transaction after the delete then proceed with the rest of your statements.
1