Why does the index lock my updates in SQL Server 2005?

Suggestion is that UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2' requires table scan to locate the record, so the scan stops every time it reaches the locked record with row_id = 100. Anyway, you can investigate it by sp_lock procedure.

Solution: try to create index on the name column.

P.S. What's is the reason the table have no clustered index at all?

gavrilenko_s at 2007-9-9 >

So your main problem's here:
-Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it.

The approach is to keep transactions as short as possible. And never to use standart lock engine to implement business logic - it's not flexible, its behavior may change from version to version ( as you have found it out yourself ). All the data must have been saved within one short transaction after the user pushed the 'Save' button. To implement pessimistic locks you may somehow mark you record as 'locked' (it's a tricky to maintain only live 'locks', but the problem solvable)

If i where to add an index for every possible WHERE statement then i would need to create thousands for my entire database. This is not a very logical solution since it is possible that new WHERE statements are introduced.

Since the record i want is not locked i should still be able to update it, right?
And if i use a different WHERE statement i can update it, that's just wrong!

There are not only logic while the MSSQL executes a query, there are also a physics presented. MSSQL should locate the data you want to update (select or delete, no matter). If MSSQL does't find apropriate index, it use table or clustered index scan access the data, no other options. While scanning the data, if MSSQL faces the exclusively locked record, it can't even read it (that's eclusive locks were invented for, weren't they?), and since the record couldn't be read, you can't identify does the record meet your conditions or not, can you? So the scan process stops untill the record remains locked (or the timeout occured).

So avoid the scan as a method of data locating. It's a slow method. It has other not so obvious disadvantages.

For example this query will work :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;

But this qeury will give me a "Lock timeout" :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';

Why does you use different key fields to locate the same record? And, if name field is a key a key field why does it have no index on it?

Since this problem did not occur with SQL Server 2000

There are many changes made to the MSSQL 2005 engine and query optimizer. Anyway, you should comare execution palns for the queries run on different versions of MSSQL.

gavrilenko_s at 2007-9-9 >

Pessimistic locking is not a really good idea in a server based application. Consider changing to use optimistic locks unless you have a really high amount of users editing the same row.

This statement:

UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id IN (SELECT row_id FROM gwseqnumber WHERE NAME = 'REC_2');

Shouldn't run any slower than:

UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';

But it should still be blocked by an UPDATE or EXCLUSIVE locks...Anyhow, I would suggest two steps to see what is going on:

First, use profiler to capture the exact statments that are taking place when the lock is being taken.

Next, try running this to look at the locks. Perhaps it will give us an idea of the locking issues you are having. You might want to trim the output to just a couple of session_ids...

(Ok, I am a bit new with the dynamic management views, so I might not have this 100% correct :)

use adventureworks
go
select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end
request_session_id, resource_type, resource_subtype, request_mode, request_type, request_status,
request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then (select object_name(object_id) from sys.partitions where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @@spid

Post the results and let's see what is causing the blocks. To me, if you didn't see blocking in 2000 it might have been a bug. Any time you modify a table and it requires a table scan, it should be blocked by any full pessimistic lock (if any other user can touch a row in the table or index of a table, they could fetch a copy of the row and later try to modify it.

LouisDavidson-SQLServerMVP at 2007-9-9 >

So what is "probably" happening is that it is doing a table scan, locking each row as it scans through each row. In ReadCommitted isolation level, locks are acquired through grabbing a row, checking it, releasing it, grab the next, and so on. (commonly referred to as "crabbing" through the rows, for the mental image of a crab walking sideways.) Updates should always be done using a unique index for maximum concurrency. If you have fetched the row for editing, why not fetch the primary key as well and use this as the key for the update?

The reason why you get the blocking here but not with the SELECT, is that since the UPDATE is crabbing using update locks, an Update lock (which signals that the process might update the row.) (If you look at the lock compatibility chart that I mentioned yesterday (searching for shared lock mode will get you there) it shows what locks are compatible) Update locks are compatible with Shared locks, but not update locks. Hence reader can read the row, but not update it.

You should note that what you are doing is not really an effective pessimistic locking mechanism, even if you get the locking worked out. All this does is force any other writer to wait. But if three people read the row, and then all three go to update the row, they will all wait and eventually succeed or lock timeout. And if they lock the row for editing and their fetch was before the other writer made the change, you can overwrite changes. (at which time you need to use optimistic locking)

LouisDavidson-SQLServerMVP at 2007-9-9 >

But this query will work :
UPDATE gwseqnumber SET findhighestnumber = 'Y' WHERE name = 'REC_2';<<

Very interesting. Check the plan, then then do the second one in a REPEATABLE READ isolation transaction and looks at the locks (so we can see all of the locks that it required to do the job without releasing them.)

Best guess is that it is using a different index to find the row somehow.

But when a lock is applied to one record, it should not block other records (unless using Page or Table locking), especially not inconsistently, allowing some queries to update but blocking others.

I doubt it really is inconsistent internally (I know it looks that way). I will bet that the reason the second update doesn't get blocked is due to an index on the nextseqnr column. The plan of the two queries will tell you, but I will bet that the second update uses a different index to get the row (for some reason, which hopefully a person with deep internal query processor knowledge can give us. If they don't answer here, I will try to find out for you.)

Can you post the complete table structure and the plans to the queries? As well as the locks from the queries?

I understand the whole concept of Shared-Locks, Update-Locks and Exclusive-Locks, i have been programming with databases for many years now and run into many weird database 'features'.

I hope I didn't sound condesending. I had to go to some stuff I had written about locks to remember exactly what they were (I have a memory like an elephant with a pre-frontal lobotomy.) I am keen to help you out in any way I can. Locking and blocking is very interesting because they do a lot of tricks to avoid deadlocks and blocks that don't seem straightforward sometimes.

We only implement the pessimistic lock for when user manually want to edit a record, if a lock already resides on the record they immediately get a warning and will not be able to go to the update screen.

This is fine as long as you refresh the data set before edit. All I was trying to say was that if you let the user edit the data as they have it in cache and you just lock it, it may have changed (I am not at all familiar with how pessimistic locking works using OLEDB, since it is really discouraged (actually because of these kind of locking issues :)

[

LouisDavidson-SQLServerMVP at 2007-9-9 >

I am really hoping that someone else will chime in as well. As for why 2000 used the index and 2005 doesn't is a good question, but it could just be due to cost (I wasn't able to see the plans, they don't seem to be there, though the image is) differences.

You can force the index by using a FROM clause:

create table test
(
testId int
)
go
create unique index testIndex on test (testId)
go
update test
set testid = 1

update test
set testId = 1
from test with (index = testIndex)

LouisDavidson-SQLServerMVP at 2007-9-9 >

Nope. As you probably know, SQL Server uses a cost based optimizer. Most likely what has occurred is that in 2005 it was cheaper to do the query without the index.

You could add more data until it is more costly to do the table scan....

You could try using the READ_COMMITTED_SNAPSHOT database setting. It would alleviate this locking. Try searching for it: http://search.msn.com/results.aspx?q=READ_COMMITTED_SNAPSHOT and there are some good articles about it.

I didn't suggest it before because I was confused by your pessimistic locking mechanism where you want them to be able to read the data. If you fetch the data again before the edit then the READ_COMMITTED_SNAPSHOT might work for you.

LouisDavidson-SQLServerMVP at 2007-9-9 >