Deadlock scenario: ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS index options

Last month I helped one of my customer investigate a deadlock situation.

After we got the deadlock report it turned out that we were dealing with a ‘conversion deadlock’ caused by inappropriate index options.

Let’s reproduce and check the situation. I create a database ‘deadlock’, a table ‘t1’ and put some data into it. After that I create a user stored procedure which is used for updating the content of this table.

Notice that ALLOW_ROW_LOCKS=OFF and ALLOW_PAGE_LOCKS=OFF options have been used! Configuring both options to OFF means that SQL Server put every lock on the table immediately. This cause serious problems in case of high concurrency, especially for writers like in our case. Let’s go on.

Ok, we have everything to reproduce the issue. Only one thing left. Configure the profiler to catch every deadlock and set the T1222 trace flag on to get the deadlock information in the error log.

Done. Open two query window and run the following queries in it. It is important that the queries run totally the same time!

If everything go well (hm… I mean wrong in our case) you can see the following results:

Deadlock graph:

 

Deadlock report from error log:

So what happened here? Both transaction acquire an intent exclusive lock on the table (IX) because of the index options. Two IX is compatible each other. The real problem happen when both transaction want to convert its IX lock to X. Both transaction try to update totally different rows which rows could be on totally different pages, it doesn’t matter, because locks go to the top of table.

If  ALLOW_PAGE_LOCK was configured to ON the situation would be better a bit. In this case deadlock would occur if two concurrent transaction tried to update rows on the same page at totally the same time.

Conclusion: In case of high concurrency (especially writers) set ALLOW_PAGE_LOCK and ALLOW_ROW_LOCK to ON!

If you have any remark or question feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

This entry was posted in Deadlock, Index, SQL. Bookmark the permalink.