I Have an Exclusive Lock, My Row Is Safe, Right?

Comments 0

Share to social media

Note: This is an update of a blog I posted in 2006 with a lot of additional information (I was less wordy in my 30s apparently). It is just as relevant today.

I have a presentation I do occasionally on concurrency entitled “Let Me Finish” that covers many of the different types of concurrency behaviors that SQL Server uses, both in the on-disk and in-memory tables (you can get that from the latest link here). One of the demos that gets the most attention is probably the least typical scenario, having a reader not blocked by an exclusive lock.

The in-memory engine doesn’t use locks, so this will not pertain with any in-memory tables. But for on-disk tables, it seems obvious that anything exclusively locked will not be touchable by any other user, since an exclusive look (XLOCK) is incompatible with all other lock types, right?

Note: READ UNCOMMITED and the NOLOCK hint are also generally not blocked by an exclusive lock, but let’s try to believe that doesn’t exist.

Well, not exactly, and the reason for this lies in the actual name of the default isolation level READ COMMITTED. Data that is on a page that has not been marked as dirty will (generally, for reasons to come) be available because SQL Server knows that the page has not been changed as that point, so it reads through the exclusive lock (which are most often used by the query processor to mark when a page is being written to).

Note, too that SQL Server has a setting to allow READ COMMITTED isolation level to read the last committed version of a row using statement level snapshot isolation level with the database setting READ COMMITTED SNAPSHOT, in which case even a dirty page would be skipped by a reader. I will discuss that further later in the blog.

For example. say you have the following database:

Then we create the following schema and table with a single row to give us something to lock:

Now, in one connection to SQL Server, run this following command:

By using the SERIALIZABLE isolation level, we have applied the most strict isolation level such that no other user can make any changes to this table. No new (phantom) rows, and no changed (non-repeatable read) rows either. Without the XLOCK hint, our row would have shared range locks on all existing rows, as well as any preventing any new rows in the table, but all users would have access to the row. Adding in the XLOCK hint to the query, and now all locks will be exclusive locks.

For more reading on this subject, go check Rodney Landrum’s blog here on the topic, which has techniques to see the locks, including using sp_lock and sys.dm_tran_locks.

Now, if you execute either of the following statements on a different connection:

You will be blocked from running your query. The first because the exclusive lock is not compatible with the already exclusively locked row. The second for the same reason, except we are actually trying to change the row.

However, if a typical reader were to come in and run a typical query:

You will find that the SELECT statement executes, ignoring the exclusive lock, because it is not a write lock, and the data on the page has not been changed.

The main reason people try to do this is to force access to a row in a single threaded manner. For example, building their own sequence number, either in a row they update, or by trying to do MAX() on all of the data in a table to make sure only one reader gets the same value.

This is generally a bad idea, since locking an entire table is a generally bad idea, but if you needed to block readers, you can couple the XLOCK with a PAGLOCK. So, change the first reader to:

And retry the experiment. Now you will find the reader is blocked (Again thanks to Tibor Karazi for pointing this one out to me oh so many years ago, and it has been a lesson I still remember).

Just bear in mind that in a normal usage of a statement like this, you are probably trying to lock a single row. But by adding the PAGLOCK hint, you have now blocked 8060 byte exclusively. Maybe not an issue, but certainly could be in a highly contentious database with smaller row size.

In the start of this blog, I noted READ COMMITTED SNAPSHOT had an effect on being able to read exclusively locked rows. If we turn this setting on for the database, no user in READ COMMITTED isolation level will be blocked when reading this row.

Now, even if the user executes, making the page the row sits on marked as dirty, and the row exclusively locked:

No matter if this user leaves the transaction open for all day, any user in the default isolation level of READ COMMITTED will not be blocked and will see the original value of 1 until the transaction is committed. (Ideally, your transactions are kept short so the version store can be flushed of needless versions, but long running transactions happen):

I include the BEGIN TRANSACTION for the second query to make a finer point about READ COMMITTED SNAPSHOT. If you use the full SNAPSHOT isolation level, readers will get the same result back every time once their transaction has started (meaning they have read anything from the database.) So:

No matter how many times you run that SELECT statement in that same transaction, the results will be the same, or your transaction will be cancelled. The extends to other tables as well, your view of all of the tables in the database container (all of the on-disk tables in a database) will look like they did when your first statement accesses data (not when the transaction occurs).

However in READ COMMITTED SNAPSHOT readers are only guaranteed consistent results at the statement level. Once the rows are returned, the results of the UPDATE will be visible to the reader.

So in the case of:

Once your query starts, it will see the table as it was when the statement started, but the second SELECT statement may return a completely different set of data if between the start of the first SELECT the data has changed.

Summary

The main thing to take away here is that locking is complicated, and the relational engine creators will do anything they can to improve performance. If you need certain guarantees of how your data is protected from change to your readers, you need to understand the isolation levels well enough to protect yourself.

Exclusively locked rows are not really exclusively locked from all other users. Just more exclusively locked than shared locks. (And READ UNCOMMITTED isn’t always allowed to see data in any scenario, such as seeing a table and data that is being created using SELECT INTO.) Be sure and test your code for how it will behave in concurrent processing scenarios, and when testing in a query tool, use WAITFOR statements to slow down time. For most situations you can devise a manner to slow down time and see what two statements will do to one another.

 

Load comments

About the author

Louis Davidson

Simple Talk Editor

See Profile

Louis is the editor of this Simple-Talk website. Prior to that, has was a corporate database developer and data architect for a non-profit organization for 25 years! Louis has been a Microsoft MVP since 2004, and is the author of a series of SQL Server Database Design books, most recently Pro SQL Server Relational Database Design and Implementation.