To avoid the blocking of other users, rowlocks are sometimes used to create locks on the most granular locking level in a table. In theory, when user A makes changes to a row, user B can read, change or delete any other row than that. But in practice, user B can still become blocked by user A altough they are not accessing the same row. It all depends on how data is accessed.
Open a new connection i management studio and run the following script:
CREATE TABLE ROWLOCKTEST ( PK INT PRIMARY KEY NOT NULL, SOME_VALUE VARCHAR(10) NOT NULL ); INSERT ROWLOCKTEST VALUES (1, 'aaaaa'),(2, 'bbbbb'),(3, 'ccccc'); BEGIN TRAN UPDATE ROWLOCKTEST WITH (ROWLOCK) SET SOME_VALUE = 'xxxxx' where PK = 2;
Now we have a transaction running, and a rowlock on table ROWLOCKTEST.
Open a new connection and run the following statement:
SELECT * FROM ROWLOCKTEST WHERE PK = 1;
Since the rowlock is on row 2, we can read from row 1 without getting blocked. Now try to read that same row, but this time by using a filter on the non key column SOME_VALUE:
SELECT * FROM ROWLOCKTEST WHERE SOME_VALUE = 'aaaaa'
The statement gets blocked and will wait until the blocking transaction ends, or until a connection time out occurs.
The reason for the blocking is that the second query need to scan all rows (including the blocked row) in the table to know which rows satisfies the condition SOME_VALUE = ‘aaaaa’. The first query accesses the row by the unique index on the primary key column (PK).
The first query uses an index seek, reading only one row:
And the other a index scan, reading all rows:
So in effect, a rowlock can block a whole table if another query tries to do a scan. Even if the scan do not need to touch the blocked row, the engine doesn’t know that.
