May 4, 2010 at 5:43 pm
I am running an update statement on a single column within a large table (over a million records large number of columns). Locks are a concern with this update.
I just want to do some fact checking on update locks, I recognize that the locks for my particular situation could be different from teh general.
1) If a table lock does not occur, do update locks happen one row at a time? (as they are getting updated). Or are all rows updated by the update statement locked at the same time?
2) I have heard of "index locks" that can occur. Can someone explain what this means, how debilitating is it, and how I can avoid them if needed?
May 4, 2010 at 6:14 pm
huston.dunlap (5/4/2010)
I am running an update statement on a single column within a large table (over a million records large number of columns). Locks are a concern with this update.I just want to do some fact checking on update locks, I recognize that the locks for my particular situation could be different from teh general.
1) If a table lock does not occur, do update locks happen one row at a time? (as they are getting updated). Or are all rows updated by the update statement locked at the same time?
2) I have heard of "index locks" that can occur. Can someone explain what this means, how debilitating is it, and how I can avoid them if needed?
2 - Not as bad as a table lock per se. Indexes have row and page level locks on by default. You can alter that behavior in the create or alter index statement. Avoid them by better tuned queries and better indexes for the queries at hand.
1 - With a mass update like that, you will encounter a table lock. Here is some reading on Locks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2010 at 7:28 pm
huston.dunlap (5/4/2010)
I am running an update statement on a single column within a large table (over a million records large number of columns). Locks are a concern with this update.I just want to do some fact checking on update locks, I recognize that the locks for my particular situation could be different from teh general.
1) If a table lock does not occur, do update locks happen one row at a time? (as they are getting updated). Or are all rows updated by the update statement locked at the same time?
Whether SQL Server determines that it needs to do a row, a page or a table lock, it will perform the lock and update all the data in one fell swoop, so even when it's doing row locking on a large set of rows, it's not actually updating them one at a time, unless of course you're running everything through a cursor.
2) I have heard of "index locks" that can occur. Can someone explain what this means, how debilitating is it, and how I can avoid them if needed?
If you're manipulating data this also part of an index, then the index will get locked. As long as your system is indexed well, usually this cost is not honorous, but it's one of the main reasons why you'll read in all sorts of tuning literature that you need to limit the number & size of indexes you create on a system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply