Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Read Committed Isolation level in SQL server for a single statement

Say, I have a person table and it has only 1 row -

id = 1, name = 'foo'

On one connection

select p1.id, p1.name, p2.name
from person p1 
join person p2 on p1.id = p2.id

On another connection at the same time:

update person set name = 'bar' where person.id = 1

Q1: Is it ever possible, for my select to return a result like this based on the timing of the update statement:

id = 1, p1.name = 'foo', p2.name = 'bar'

Neither connection uses an explicit transaction and both use default transaction isolation level READ COMMITTED.

The question is really is to help me understand, whether the locks acquired at the beginning of a sql statement continue to exist until the statement completes, or if it is possible for a statement to release the lock and re-acquire the lock for the same row if it is used twice in the same statement?

Q2: Would the answer to the question change if the set read_committed_snapshot on is set on the db?

like image 488
Raghu Dodda Avatar asked Jan 23 '11 05:01

Raghu Dodda


People also ask

How the Read Committed isolation level works with an example?

Cursor stability at RC isolation level prevents cursor lost updates that could occur if locks are released immediately after data is read. For example: Transaction T1 running with RC isolation level reads a data item. Transaction T2 updates the data item and commits.

What is read committed isolation level in SQL Server?

READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions.

How do I find uncommitted transactions in SQL Server?

Two things to check. One is that you might have the "implicit transaction" setting turned on, which means EVERYTHING it wrapped in a transaction. Check the properties of the server/database. Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....


1 Answers

Q1: Yes this is perfectly possible at least in theory. read committed just guarantees you do not read dirty data it makes no promises about consistency. At read committed level shared locks are released as soon as the data is read (not at the end of the transaction or even the end of the statement)

Q2: Yes the answer to this question would change if read_committed_snapshot is on. You would then be guaranteed statement level consistency. I'm finding it hard finding an online source that unambiguously states this but quoting from p.648 of "Microsoft SQL Server 2008 Internals"

A statement in RCSI sees everything committed before the start of the statement. Each new statement in the transaction picks up the most recent committed changes.

Also see this MSDN blog post

Setup Script

CREATE TABLE person 
(
id int primary key,
name varchar(50)
)

INSERT INTO person
values(1, 'foo');

Connection 1

while 1=1
update person SET name = CASE WHEN name='foo' then 'bar' ELSE 'foo' END

Connection 2

DECLARE @Results TABLE (
  id    int primary key,
  name1 varchar(50),
  name2 varchar(50))

while( NOT EXISTS(SELECT *
                  FROM   @Results) )
  BEGIN
      INSERT INTO @Results
      Select p1.id,
             p1.name,
             p2.name
      from   person p1
             INNER HASH join person p2
               on p1.id = p2.id
      WHERE  p1.name <> p2.name
  END

SELECT *
FROM   @Results  

Results

id          name1 name2
----------- ----- -----
1           bar   foo

Looking at the other join types in Profiler it appears that this issue could not arise under either the merge join or nested loops plan for this particular query (no locks get released until all are acquired) but the point remains that read committed just guarantees you do not read dirty data it makes no promises about consistency. In practice you may well not get this issue for the exact query you have posted as SQL Server would not choose this join type by default. However you are then just left relying on implementation details to produce the behaviour that you want .

Trace

NB: If you were wondering why some row level S locks appear to be missing this is an optimisation explained here.

like image 107
Martin Smith Avatar answered Sep 22 '22 00:09

Martin Smith