0

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?

M Sach
  • 33,416
  • 76
  • 221
  • 314

1 Answers1

0

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.

meriton
  • 68,356
  • 14
  • 108
  • 175
  • As you said "A write operation refers to an individual row, and its lock. " what do you mean by that? To update the individual row.you need where clause. So "update Employee set category = "permanent" where id =1" can be individuaql row or collection of rows. Right? – M Sach Jan 26 '14 at 12:12
  • A write operation is not the same as an update statement. To execute an update statement, the database must first find the rows that match the where clause (the "range operation" from the wikipedia table), compute the new values for these rows, and write them (the "write operation" from the wikipedia table). – meriton Jan 26 '14 at 14:12