Sql-server – Locking in “Read Committed Snapshot”

lockingsnapshot-isolationsql servertransaction

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending

eg:

update table1 set col1 = col1 + 1 where PKcol < 3
update table1 set col1 = col1 + 1 where PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3
update table1 set col1 = col1 + 1 where NonPKcol = 3
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

In above case PKcol is primary key in table and NonPKcol is a non-primary key.

Will updates be locked for only the rows satisfying the 'where' condition? Is it based on value or index or primary column?

Best Answer

The exact behaviour of concurrent modifications depends, in part, on the access path chosen by SQL Server to locate records to change.

If SQL Server uses the clustered index to locate data to change, locks will generally be taken on clustered index keys (and/or pages, etc.). If SQL Server locates rows to change using a nonclustered index, locks will be taken on the nonclustered index. In both cases, exclusive locks are also generally taken on the clustered index before the modification is actually performed.

Anticipating locking behaviour can be a fun and educational exercise, but it often the wrong question to ask. If you are experiencing blocking on locks where it does not seem necessary, then it might be the right question, but it is a very advanced one, requiring detailed internal knowledge to fully explain. I'll show an example based on your data later on.

Most often, the real question is "which isolation level should I use?". Locks are an implementation detail, used to provide the guarantees offered by the various isolation levels. The guarantees are the important thing. You should understand the different behaviours that are possible under each isolation level, and then make an informed choice. Please refer to that link for all the details.

When modifying data, RCSI behaves the same as standard READ COMMITTED. It will block if it needs to read something that is currently locked by another session's uncommitted changes. Once the blocking session commits or rolls back its changes, the blocked update continues, reading the committed values present at the time the blocking lock was released. This behaviour is required to prevent "lost updates", which are not allowed under any isolation level supported by SQL Server.

The following demo shows that the precise blocking behaviour depends on which locks are needed according to the query plan selected by the optimizer. In some cases, the update will block, in other cases, it will not. SQL Server always respects the guarantees provided by the user's isolation level, regardless of the implementation-defined locking behaviour.

Test Table and Data

CREATE TABLE dbo.Table1
(
    PKcol integer PRIMARY KEY, 
    NonPKCol integer NULL UNIQUE, 
    col1 integer NULL
);

INSERT dbo.Table1
    (PKcol, NonPKCol, col1)
VALUES
    (1,1,0),
    (2,2,0),
    (3,3,0),
    (4,4,0),
    (5,5,0);

Uncommitted Update

On a separate connection, run:

BEGIN TRANSACTION;
UPDATE dbo.Table1 SET NonPKCol = 997 WHERE PKcol = 3;
UPDATE dbo.Table1 SET NonPKCol = 998 WHERE NonPKCol = 3;
UPDATE dbo.Table1 SET NonPKCol = 999 WHERE NonPKCol = 5;

Note the lack of a COMMIT or ROLLBACK TRANSACTION.

Test Results

-- (1) Succeeds (no conflicting locks encountered)
update table1 set col1 = col1 + 1 where PKcol < 3

-- (2) Waits for an X lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where PKcol = 3

-- (3) Waits on U lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3

-- (3) Succeeds when read access is by NONCLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(2)) where NonPKcol < 3

-- (4) Blocks on U lock for NONCLUSTERED index key NonPKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol = 3

-- (5) Blocks on U lock for nonclustered index key NonPKcol = 5
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

-- (5) Succeeds when access is by CLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(1)) where PKcol < 3 and NonPKcol = 5

Related Solutions

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Sql-server – SQL Server’s READ COMMITTED SNAPSHOT vs SNAPSHOT

READ COMMITTED SNAPSHOT uses a new snapshot after each statement. That means that less row versions are being kept alive. (The statement you quoted from the docs is slightly misleading because it suggest that this is always true - it is only true in case of long-running SNAPSHOT transactions.) Snapshot row versions are created on writes. The reads do not influence what gets put into tempdb. Writers cannot possibly foresee what reads will be carried out in the future. Readers only influence only what can be purged.
When a SNAPSHOT transaction T1 writes to a row that was modified by another transaction T2 in the time between T1 started and T1 attempted the write, the statement fails with an update conflict error. This is an optimistic concurrency model. With READ COMMITTED SNAPSHOT T1 would wait for T2 to release the X-lock on the row and continue normally.