when/what locks are hold/released in READ COMMITTED isolation level

Question

I am trying to understand isolation/locks in SQL Server.

I have following scenario in READ COMMITTED isolation level(Default)

We have a table.

create table Transactions(Tid int,amt int)

with some records

insert into Transactions values(1, 100)
insert into Transactions values(2, -50)
insert into Transactions values(3, 100)
insert into Transactions values(4, -100)
insert into Transactions values(5, 200)

Now from msdn i understood

When a select is fired shared lock is taken so no other transaction can modify data(avoiding dirty read).. Documentation also talks about row level, page level, table level lock. I thought of following scenarion

Begin Transaction

select * from Transactions

/*
some buisness logic which takes 5 minutes

*/

Commit

What I want to understand is for what duration of time shared lock would be acquired and which (row, page, table).

Will lock will be acquire only when statement select * from Transactions is run or would it be acquire for whole 5+ minutes till we reach COMMIT.

Erdrich

Answer 1

lock will only acquire when select * from Transaction is run

You can check it with below code

open a sql session and run this query

Begin Transaction

select * from Transactions

 WAITFOR DELAY '00:05'
/*
some buisness logic which takes 5 minutes

*/

Commit

Open another sql session and run below query

Begin Transaction
Update Transactions
Set = ...
where ....
commit

Rives

I've tried, and Update statement was completed, while Read transaction was pending. Probably all is resolved by MVCC without any locking in read commited. BTW, my READ_COMMITTED_SNAPSHOT is set to OFF. Maybe shared lock is only for write locks, but still, it allows reading. – Ruelas

Answer 2

Your question is a good one. Understanding what kind of locks are acquired allows a deep understanding of DBMS's. In SQL Server, under all isolation levels (Read Uncommitted, Read Committed (default), Repeatable Reads, Serializable) Exclusive Locks are acquired for Write operations.

Exclusive locks are released when transaction ends, regardless of the isolation level.

The difference between the isolation levels refers to the way in which Shared (Read) Locks are acquired/released.

Under Read Uncommitted isolation level, no Shared locks are acquired. Under this isolation level the concurrency issue known as "Dirty Reads" (a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed, so it could be rolled back) can occur.

Under Read Committed isolation level, Shared Locks are acquired for the concerned records. The Shared Locks are released when the current instruction ends. This isolation level prevents "Dirty Reads" but, since the record can be updated by other concurrent transactions, "Non-Repeatable Reads" (transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data) or "Phantom Reads" (in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first) can occur.

Under Repeatable Reads isolation level, Shared Locks are acquired for the transaction duration. "Dirty Reads" and "Non-Repeatable Reads" are prevented but "Phantom Reads" can still occur.

Under Serializable isolation level, ranged Shared Locks are acquired for the transaction duration. None of the above mentioned concurrency issues occur but performance is drastically reduced and there is the risk of Deadlocks occurrence.

Dunton

Answer 3

You are asking the wrong question, you are concerned about the implementation details. What you should think of and be concerned with are the semantics of the isolation level. Kendra Little has a nice poster explaining them: Free Poster! Guide to SQL Server Isolation Levels.

Your question should be rephrased like:

select * from Items

Q: What Items will I see?
A: All committed Items

Q: What happens if there are uncommitted transactions that have inserted/deleted/update Items?
A: your SELECT will block until all uncommitted Items are committed (or rolled back).

Q: What happens if new Items are inserted/deleted/update while I run the query above?
A: The results are undetermined. You may see some of the modifications, won't see some other, and possible block until some of them commit.

READ COMMITTED makes no promise once your statement finished, irrelevant of the length of the transaction. If you run the statement again you will have again exactly the same semantics as state before, and the Items you've seen before may change, disappear and new one can appear. Obviously this implies that changes can be made to Items after your select.

Higher isolation levels give stronger guarantees: REPEATABLE READ guarantees that no item you've selected the first time can be modified or deleted until you commit. SERIALIZABLE adds the guarantee that no new Item can appear in your second select before you commit.

This is what you need to understand, no how the implementation mechanism works. After you master these concepts, you may ask the implementation details. They're all described in Transaction Processing: Concepts and Techniques.

Seeley

implementation details are important when you're getting table locks instead of row locks. – Wrongly

Answer 4

First, lock only acquire when statement run. Your statement seprate in two pieces, suppose to be simplfy:

select * from Transactions
update Transactions set amt = xxx where Tid = xxx

When/what locks are hold/released in READ COMMITTED isolation level? when select * from Transactions run, no lock acquired.

Following update Transactions set amt = xxx where Tid = xxx will add X lock for updating/updated keys, IX lock for page/tab

All lock will release only after committed/rollbacked. That means no lock will release in trans running.

Ora