November 3, 2009 at 4:54 pm
Hey guys, I'm trying to get a better understanding of locks...especially revolving around update statements. Here are a few questions:
By default, does an update statement does an update lock, or an exclusive lock?
Are locks placed on an entire record, or only the fields of a record that the update is being performed on?
When doing a transaction that involves updates on many rows, is the lock maintained on each row for the course of the transaction, or are the locks applied and removed immediately as each row is updated?
November 3, 2009 at 4:59 pm
All data modifications take an exclusive lock.
Locks can be at the row, page, extent or table. Never part of a row.
Exclusive locks are held until the end of a transaction. In default isolation level, shared locks are released after the statement completes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply