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

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:

USE master
GO
CREATE DATABASE TestReadCommitted
GO
ALTER DATABASE TestReadCommitted
  SET READ_COMMITTED_SNAPSHOT OFF; --To make 100% sure
GO
USE TestReadCommitted;

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

CREATE SCHEMA Demo;
GO
CREATE TABLE Demo.Test
(
        TestId int PRIMARY KEY
)
GO
INSERT INTO Demo.Test(TestId)
VALUES(1);

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test WITH (XLOCK);

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:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT *
FROM   Demo.Test WITH (XLOCK);
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE Demo.Test
SET    TestId = TestId;

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:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT *
FROM   Demo.Test;

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:

BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test WITH (XLOCK,PAGLOCK);

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.

ALTER DATABASE TestReadCommitted
  SET READ_COMMITTED_SNAPSHOT ON;

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

BEGIN TRANSACTION;
UPDATE Demo.Test
SET TestId = TestId * 100;

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):

BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test;

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:

SET ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test;
SELECT *
FROM   Demo.Test;

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:

SET ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test;
SELECT *
FROM   Demo.Test;

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.

 

The post I Have an Exclusive Lock, My Row Is Safe, Right? appeared first on Simple Talk.

from Simple Talk https://ift.tt/2PjKQTP
via