April 8, 2009 at 9:40 am
If I run this on a table with 100,000 records, that updates 100 records:
Update tableA set type='A' where type='B'
And I have an index on "type", is there a way to avoid locking the entire table so other process that have type='C' can continue to run ?
April 8, 2009 at 10:23 am
That query should be selective enough to use an index lock, which in theory, should allow you to do operations on other types ('c'). What is the query plan for this update?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2009 at 10:34 am
I think it might depend on how selective the index is. Also, because you're updating the index values themselves, you might be seeing a lock on the index, not the table, that would prevent Type='c' from operating.
Like Jack said, post the execution plan. Also, can you tell me what the data distribution for the different values in the index are?
"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
April 8, 2009 at 10:56 am
It also depends on isolation level - assuming that you are using read committed and that the index is selective enough to identify just the records needing an update, then then only row locks should be taken.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply