1

Could anyone explain to me why in SQL Server SELECT can be blocked by UPDATE statement? I thought, that MVCC (implemented in SQL Server by copying modified data to the tempdb) prevent readers to be blocked in all cases. Am I wrong? Why do I need to change the ISOLATION LEVEL to SNAPSHOT to release the lock?

That's how it works in Oracle:

A writer never blocks a reader. When a row is being changed by a writer, the database uses undo data to provide readers with a consistent view of the row.

and PostgreSQL

The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

1
  • 2
    READ COMMITTED is the default isolation level in SQL Server, and how it behaves depends on the READ_COMMITTED_SNAPSHOT database setting, per the documentation
    – Thom A
    Jan 19 at 10:25

1 Answer 1

4

By default SQL Server uses the READ COMMITTED isolation level. How this behaves depends on if you have the database setting READ_COMMITTED_SNAPSHOT enabled or not. From the documentation:

READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

  • If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

  • If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

Most likely what you want is for READ_COMMITTED_SNAPSHOT to be enabled on your database, so that you can query rows that are being updated, and access their prior value. This would be enable using the following:

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

We can demonstrate these different behaviours for when READ_COMMITTED_SNAPSHOT is on or not with the following. Firstly, let's create a couple of databases:

CREATE DATABASE SnapShotDB;
GO
CREATE DATABASE NoSnapShotDB;
GO

ALTER DATABASE SnapShotDB SET READ_COMMITTED_SNAPSHOT ON;
GO

Now I'll CREATE a table, and then attempt to UPDATE one of the rows inside a tranasction but not COMMIT it:

USE NoSnapShotDB;
GO
CREATE TABLE dbo.MyTable (ID int PRIMARY KEY, C char(1));
GO
INSERT INTO dbo.MyTable
VALUES(1,'a'),
      (2,'b');
GO

BEGIN TRANSACTION;

UPDATE dbo.MyTable 
SET C = 'c'
WHERE ID = 1;

Now, in a new query window (without closing your other), run the following query:

USE NoSnapShotDB;
GO

SELECT *
FROM dbo.MyTable
WHERE ID = 1;

This query will not complete, it'll "hang". If, however, you return to your other query window and run COMMIT in in, this query will then complete with the following results:

ID C
1 c

So the table contains the new value in the column C.

NOw we can do the same but for SnapShotDB:

USE SnapShotDB;
GO
CREATE TABLE dbo.MyTable (ID int PRIMARY KEY, C char(1));
GO
INSERT INTO dbo.MyTable
VALUES(1,'a'),
      (2,'b');
GO

BEGIN TRANSACTION;

UPDATE dbo.MyTable 
SET C = 'c'
WHERE ID = 1;
USE SnapShotDB;
GO

SELECT *
FROM dbo.MyTable
WHERE ID = 1;

This returns a result set immediately, with the old value:

ID C
1 a

If you then COMMIT and reran the latter query, then you would get 'c' for the column C.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.