Sql-server – UPDATE-SELECT Consistency Issue In Read Committed

isolation-levelsql server

In Read Committed isolation level, I would expect the following to deadlock since UPDATE statement will mark the row dirty and SELECT should not be able to read the row before transaction is committed or rolled back. But it seems to work without a deadlock. How does this happen?

BEGIN TRAN

UPDATE TableA
SET Col2 = Col2 + 3
WHERE ID = 50

SELECT Col2 from TableA WHERE ID = 50

COMMIT TRAN

Best Answer

Your SELECT is running in the same process (spid) as your UPDATE, so you can freely select the updated rows - as long as you're in the same process.

Here is a classic deadlock example that you can play with:

--Classic deadlock example:

Create table dbo.Test1 (col1 int);
Create table dbo.Test2 (col1 int);

Insert into dbo.test1 values(1)
Insert into dbo.test1 values(2) 
Insert into dbo.test1 values(3) 


Insert into dbo.test2 values(1)
Insert into dbo.test2 values(2)
Insert Into dbo.test2 values(3)

--Open a SSMS query session 1 and run these commands
begin tran 
update  dbo.test1 set col1 = 2


--Open another SSMS query session 2 and run these commands
begin tran
update dbo.test2 set col1 = 1 
update  dbo.test1 set col1 = 3


--Go to SSMS query session 1 and run these commands
update dbo.test2 set col1 = 4

rollback

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.

This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.

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.

Best Answer

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

Postgresql – Read Committed Isolation Level

Related Question