Was the ikey column a primary key column or non-clustered index column?
It seemed that the UPDATE statement acquired an Exclusive lock on a page
which containing the rows ikey=1 and ikey=2. I would like to know if there
were many concurrent transactions executed on your table in your scenario.
I performed a test under Adventureworks database, however I could not
reproduce your issue.
To get more detailed information, I recommend that you use SQL Profiler to
monitor your SQL Server. Please add all Locks events for your SQL trace.
Due to huge number of records generated, I also recommended that you filter
the records by specifying LoginName or SPID. You may also mail me
(changliw_at_microsoft_dot_com) the trace file for further research.
You may try using one of the following methods to see if it helps:
a. READ COMMITTED isolation level when the READ_COMMITTED_SNAPSHOT database
option is ON.
b. SNAPSHOT isolation level.
c. READ UNCOMMITTED isolation level. This can only be used for systems that
can operate with dirty reads.
Take the option a for example, change your database to use row versioning
isolation levels with the following statement:
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON;
For more information, please refer to this article:
Lock Escalation (Database Engine)
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Microsoft Online Community Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
This posting is provided "AS IS" with no warranties, and confers no rights.