Does UPDATE lock the table or the row

Question

Does UPDATE lock the table or the row

Chakravarthy-397088

SSC Rookie

Points: 28
More actions
April 1, 2007 at 10:01 am

#98188

I have a basic question.
Within a READ COMMITTED transaction, will an UPDATE statement lock the table, or the row being updated ?
And what is the granularity of the lock if the transaction is READ UNCOMMITTED ?

Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It depends on the size of the table, the number of rows been updated, the indexes on the table, the amount of locks already held, and probably several other factors I've forgotten.

Read uncommitted has no effect on locks taken by updates, inserts or deletes. The transaction isolation levels define the type of locks acquired on read operations only.

From books online:

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 2

All changes (insert, update, delete) acquire an exclusive lock. Granularity depends on a number of factors, but they all have an exclusive lock. Now, read operations can ignore the lock if configured, which may be good or bad.

Chakravarthy-397088 SSC Rookie Points: 28 More actions · Answer 3

Yes, but i do think an UPDATE locks the table itself, not just the row being updated. That is why if there are 2 seperate transactions, with the first query in one Transaction 1 doing an UPDATE Table A set (...) WHERE (...) , and the second query in Transaction 2 trying to UPDATE Table A set (...) WHERE (some other condition) , query 2 is in a loop waiting for Transaction 1 to get over.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Yes, but i do think an UPDATE locks the table itself, not just the row being updated.

It depends. If you're updating the entire table, a table lock is very likely to be aken. If you're updating a single row, and there's an appropriate index, then SQL's likely to take a row lock.

It depends on a large number of factors and is impossible to state categorically what locks will be taken, especially with no info on table structure, indexing and table size.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 5

use DBCC TraceFalgs 1211,1224 for disabling lock escalations to higher levels. For more information http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx

Feature is not there in SQL 2000.

Regards
Shrikant Kulkarni

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 6

Be careful of disabling lock escalations. May not help your system.

If these are locks with different WHERE clauses, are they updating large numbers of rows? Or single rows?

Usually locks will be page or row locks, but if you can't update in place, maybe the data size changes, maybe you are causing page splits or something that escalates the locks. Is this happening constantly?