Two different transactions updating different rows of same table at same time

Question

Environment: SQL SERVER 2008 R2, Windows.

CONNECTION-1: executing following

BEGIN TRANSACTION               

    UPDATE Check_lock with (rowlock)
    set LayoutType = 98
    where USERID between 1 and 7;

    WAITFOR DELAY '000:10:00';

COMMIT TRANSACTION;

CONNECTION-2: executing following

BEGIN TRANSACTION               

    UPDATE Check_lock with (rowlock)
    set LayoutType = 98
    where USERID between 15 and 20;

COMMIT TRANSACTION;

Problem Statement: I am executing above transactions through SQL Server Management Studio by making two connections with the same server/database on same machine at same time. Though the table is same & transactions are executing at same time (not 100% same time, because executing manually) but updating different rows (row-level locking) then why the "Conneciton-2" transaction did not get committed immediately and goes in wait until the first transaction is not committed/completed. ??

Please let me know if I didn't describe my scenario clearly.

Welcome to SO, you should add the SQL tag to your question and avoid putting your email address in the question body (you can add it to your profile instead!) — Awalias, Jul 09 '13 at 09:52
Is `USERID` indexed? Is the index used in the execution plan? If not the scan to find the rows matching `USERID between 15 and 20` will be blocked waiting to read the rows exclusively locked by the first transaction. — Martin Smith, Jul 09 '13 at 10:02

miguelbgouveia · Answer 1 · 2013-07-09T13:10:56.460

I think this is rthe reason:

"WITH (ROWLOCK) provides a query hint to the optimizer. If SQL so deems, it can still escalate a rowlock into a page lock or a table lock. It will normally only do so if you're hitting a very large number of rows though - it's basically trying to save you from consuming extra resources via scores of row locks."

This quote was taken from this MSDN forum: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/60238304-04e8-4f98-84d1-3ddf1ed786a9/why-the-entire-table-is-locked-while-with-rowlock-is-used-in-a-update-statement

Creating an index for the column USERID (like Martin Smith said) could help because the SQL optimizer can use the index and conclude that is best the row lock that a full scan.

score 0 · Answer 2 · answered Mar 05 '19 at 07:19

To solve this, you need to apply "WITH (ROWLOCK)" as you have already done and the other thing is that you also need to add a "Non-Clustered Unique Key" index on USERID.

I was able to fix deadlock issues with this and hopefully, will resolve your problem as well.

Two different transactions updating different rows of same table at same time

2 Answers2