CODEWITHSUNDEEP
javasql-servertransactionsisolation-level
M Sach
M Sach

Reputation: 34424

Update under Read committed Isolation level?

As per read committed isolation level on wiki

Read committed

In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed

As per above statement it looks like , whenever any update statement is exceute whether its on whole table (codesnippet 1) or selected range(codesnippet 2), read committed isolation level will keep the lock till commit

codesnippet 1
begin tran
update Employee set category = "permanent" 
...
end tran // lock will be released here

codesnippet 2
begin tran
update Employee set category = "permanent" where id =1 
...
end tran // lock will be released here

Section 2:-

But as per Isolation Levels vs Lock Duration section on the same link looks like in case of update on selected range will cause the lock to be relaesed once update is executed . It won't wait till transaction ened

begin tran
update Employee set category = "permanent" where id =1 
// lock will be released here
........
end tran

My question is section 2 correct or section 1?

Upvotes: 0

Views: 759

Answers (1)

meriton
meriton

Reputation: 70564

Both, they aren't talking about the same lock.

A write operation refers to an individual row, and its lock. The range operation refers to a collection of rows, and its lock (which is typically a lock on the entire table).

That is, the statement

update Employee set category = "permanent" where id =1

would acquire a table lock for the duration of the statement, and lock the updated row(s) for the duration of the transaction.

Upvotes: 0

Related Questions