My current client is using a software package that relies on SQL Server for its database. They’re having me write code that queries the database for reporting. Today, I ran across a lovely deadlock exception that occurred during a reporting run. Here’s what caused it.
By default, select statements in SQL Server lock the rows they select against for the length of the select statement to ensure transactional reliability. If the select has locked some records and is trying to lock some that are held by another query, and that other query is trying to lock records already held by the select, then SQL Server will declare a deadlock and will kill one of them. Deadlocks don’t have a timeout. As soon as SQL Server detects one, it will kill one of the queries. In my case, I’m guessing that an update in the software package was the cause of the deadlock.
There are a number of ways to address this. First, make sure all queries acquire resources in the same order. That way you don’t get queries that each acquire a resource the other needs. I don’t know what the software application queries look like, so that’s unreasonable.
Second, you can tell SQL Server not to lock the selects. You can tell it to ignore locks entirely by adding a hint to the query, e.g., ‘NOLOCK’. That means you can get inconsistent results. For the report query that triggered my deadlock message, that could mean:
- Reporting a value that was later rolled back at the end of a failed update transaction
- Reporting a value that was overwritten by an update transaction
- Missing a value completely due to how SQL Server handles lock rows
There’s a second hint, ‘READPAST’, that will ignore locked items - not ignore locks, ignore locked items. That also means you can get inconsistent results. For our query, that could mean:
- Missing a previous result completely because the record was updated but not yet committed
The READPAST hint will return more “accurate” data if you have a lot of rollbacks. The NOLOCK hint will miss less data.
So your options are:
- Do nothing. Re-run your query if you get a SQL deadlock and hope it goes through the second time.
- Use the NOLOCK hint. You will occasionally miss reporting a value and you will occasionally report a value before it is updated.
- Use the READPAST hint. You will occasionally miss reporting a value (more often then NOLOCK).
BTW, if you use Oracle, you might be wondering why you never see this with Oracle. You don’t see it because Oracle doesn’t lock rows for select statements. Readers are never blocked by writers and vice-versa.
How does Oracle accomplish this miraculous task that so eludes SQL Server? Oracle uses the rollback logs on selects. The moment the select starts, the select pulls data that was live at that moment in time. Rows that are deleted, updated, etc. during the life of the select statement aren’t visible. SQL Server can’t manage that, so the developers have to live with deadlocks, carefully structure their SQL for all users of the database, only allow access through stored procedures that are carefully tuned, or simply ignore transactional boundaries.
Update: Newer versions of SQL Server (2005+) can use row versioning to work like Oracle. You have to issue a set command to the database, ‘SET TRANSACTION ISOLATION LEVEL SNAPSHOT’ in order to turn on this mode. From then on, you’ll get transactionally consistent reads without select locks. Unfortunately, the database you’re working with has to allow this. It’s not the default since ‘it adds overhead to support the feature’.
If your database doesn’t support it, then when you issue the snapshot set command and try a select, you’ll get this response: “Snapshot isolation transaction failed accessing database ‘xyz’ because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.”
You turn on access to snapshot reads by issuing the command ‘ALTER DATABASE xyz SET ALLOW_SNAPSHOT_ISOLATION ON’. Better yet, you can make snapshot reads the default by issuing the additional command ‘ALTER DATABASE xyz SET READ_COMMITTED_SNAPSHOT ON’.
Categories: ComputingACID Database Transactions, Database Deadlocks, Database Transactions, NOLOCK, Oracle Corporation, READPAST, SQL, SQL Server