update with rowlock in MSSQL server

Question

I was trying to understand ROWLOCK in SQL server to update a record after locking it. Here is my observation and would like to get a confirm if ROWLOCK is like a table or page lock sort of thing or I have not tried it correctly. ROWLOCK should be a lock to row only not to the table or page.

Here is what I tried:

I created a simple table:row_lock_temp_test with two columns ID and Name with no PK or index. Now I open SQL Server, two different clients but same credentials and tried executed a set of queries as follow:

Client 1:

1: BEGIN TRANSACTION;
2: update row_lock_temp_test set name = 'CC' where id = 2
3: COMMIT

Client 2:

1: BEGIN TRANSACTION;
2: update row_lock_temp_test set name= 'CC' where id = 2
3: COMMIT

I executed Query 1, 2 on C-1 and went to C-2 and executed the same queries, both clients executed the queries and then I committed the transaction, all good.

Then I added RowLock to update query,

C-1

  1: BEGIN TRANSACTION;
  2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 2
  3: COMMIT

C-2

1: BEGIN TRANSACTION;
2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 2
3: COMMIT

Now, I executed query 1 and 2 on C-1 and then went to C-2 and tried to execute the same 2 queries, but query got Stuck as expected because the row is locked by C-1 so it should be in queue until the transaction is committed on C-1. as soon as I committed transaction on C-1 query on C-2 got executed and then I committed the transaction on C-2 as well. All good.

here I tried another scenario to execute the same set of queries with row id = 3

C-2

 1: BEGIN TRANSACTION;
 2: update row_lock_temp_test WITH(rowlock) set name = 'CC' where id = 3
 3: COMMIT

I executed 1st two queries in C-1 and then went to executed 1st two queries of C-2, row id is different in both clients, but still, the query in C-2 got stuck. This means while updating query with id = 2 it has locked the page or table, I was expecting a row lock, but it seems a page or table lock.

I also tried using xlock, HOLDLOCK, and UPDLOCK with different combinations but it is always locking the table. is there any possibility to lock a row only.

Select and insert is working as expected.

Thanks in advance.

Answer 1

Lock hints are only hints. You can't "force" SQL to take a particular kind of lock.

You can see the locks being taken with the following query:

select      tl.request_session_id,
            tl.resource_type,
            tl.request_mode,
            tl.resource_description,
            tl.request_status
from        sys.dm_tran_locks   tl
join        sys.partitions      pt  on  pt.hobt_id = tl.resource_associated_entity_id
join        sys.objects         ob  on  ob.object_id = pt.object_id
where       tl.resource_database_id = db_id()
order by    tl.request_session_id

OK, let's run some code in an SSMS query window:

create table t(i int, j int);
insert t values (1, 1), (2, 2);

begin tran;
update t with(rowlock) set j = 2 where i = 1;

Open a second SSMS window, and run this:

begin tran;
update t with(rowlock) set j = 2 where i = 2;

The second execution will be blocked. Why?

Run the locking query in a third window, and note that there are two rows with a resource_type of RID, one with a status of "grant", the other with a status of "wait". We'll get to the RID bit in a second. Also, look at the resource_description column for those rows. It's the same value.

OK, so what's a resource_description? It depends on theresource_type. But for our RID it represents: the file id, then the page id, then the row id (also known as the slot). But why are both executions taking a lock on row slot 0? Shouldn't they be trying to lock different rows? After all, we are updating different rows.

David Browne has given the answer: In order to find the correct row to update, SQL has to scan the entire table, because there is no index telling it how many rows there are where i = 1. It will take an update lock on each row as it scans through. Why does it take an update lock on each row? Well, it's not to "do" the update, to so speak. It will take an exclusive lock for that. Update locks are pretty much always taken to prevent deadlocks.

So, the first query has scanned through the rows, taking a U lock on each row. Of course, it found the row it wanted to update right away, in slot 0, and took an X lock. And it still has that X lock, because we haven't committed.

Then we started the second query, which also has to scan all of the rows to find the one it wants. It started off by trying to take the U lock on the first row, and was blocked. The X lock of our first query is blocking it.

So, you see, even with row locking, your second query is still blocked.

OK, let's rollback the queries, and see what happens if we have the first query update the second row, and the second query update the first row? Does that work? Nope! Because SQL still has no way of knowing how many rows match the predicate. So the first query takes its update lock on slot 0, sees that it doesn't have to update it, takes its update lock on slot 1, sees the correct value for i, takes its exclusive lock, and waits for us to commit.

The query 2 comes along, takes the update lock on slot 0, sees the value it wants, takes its exclusive lock, updates the value, and then tries to take an update lock on slot 1, because that might also have the value it wants.

You'll also see "intent locks" on the next "level" up, i.e., the page. The operation is letting the rest of the engine know that it might want to escalate the lock to the page level at some point in the future. But that's not a factor here. Page locking is not causing the issue.

Solution in this case? Add an index on column i. In this case, that's probably the primary key. You can then do the updates in either order. Asking for row locking in this case makes no difference, because SQL doesn't know how many rows match the predicate. But even if you try to force a row lock in some situation, and even with a primary key or appropriate index, SQL can still choose to escalate the lock type, because it can be way more efficient to lock a whole page, or a whole table, than to lock and unlock individual rows.