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.

5 Responses to Deadlock scenario: ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS index options

  1. Haderer Pierre says:

    Very nice post !
    I was just missing one information : How do I get the error log ?
    In “Object Explorer”
    -> Expand “Management” Submenu
    -> Right click “current”
    -> Select “View SQL Server Log”

  2. mbourgon says:

    Robert, you just made my day. Was investigating a deadlock problem along with allow_row_locks = off, and this was on the first page. Explained exactly what I needed. Nice analysis! Thanks.

    Michael

  3. Pingback: When indexes go bad. The story of AllowRowLocks equals false

  4. Martin says:

    After hours of searching online on indexes, covering index and all that complicated stuff to find out why I had deadlocks on simple update & delete queries (on different primary keys), your article found my problem in 2 minutes !

    Thanks you !!!

  5. Alex Vera says:

    Hi Robert.
    Do you think is a bad practice use the WITH(ROWLOCK) in the update,
    And With(NoLock) in Select queries, daily we have this kind of problems and the database are +1 Million of records.

    Thanks and nice article!

    ***Comment(RobertVirag): Question was answered in e-mail.

Leave a Reply

Your email address will not be published. Required fields are marked *