sql server locks more that one record when updating one record.

sql server locks more that one record when updating one record.

Post by Nalak » Sun, 26 Aug 2007 07:18:20


Hi,
It seems like sql server locks more that one row.. when I tried to update
one row... scenario below.

Step 1 --- updated one record in a seven row table
BEGIN TRANSACTION MyTransaction
go
update dbRequest.test set f1 = '5' where ikey = 1
go
Step 2 --- Tried to read another row from the same table but was waiting
till step 1 got commited ... (different connction)
select * from dbRequest.test where ikey = 2


Quesion is...
Is it possible to get sql server to only lock that on record (not the all
other records too)?
(Would really like if sql server can return the original value till commit)



Any direction is deeply appreciated
Nalaka
 
 
 

sql server locks more that one record when updating one record.

Post by Tibor Kara » Sun, 26 Aug 2007 16:45:06

Do you have an index on the "ikey" column?

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/

 
 
 

sql server locks more that one record when updating one record.

Post by changli » Tue, 28 Aug 2007 14:38:53

Hi Nalaka,
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)
http://www.yqcomputer.com/

Hope this helps. Please feel free to let us know if you have any other
questions or concerns.

Best regards,
Charles Wang
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.
======================================================