Sql-server – problem on understanding locking and covering index

blockingsql server

As per my study, I found that a lot of people mention that the covering index would help to reduce the "key lookup" blocking. I was keen to test and understand it, but my testing did not show the expected result.

First I run the code below, I trying to update the table on the first transaction without commit and then on another session, I run a query to select the col2 and col3 from the tableA. According to the book, the second session will be block by the first session.

-- Create the table for testing
CREATE TABLE [TableA] 
(   
    [col1] INT,
    [col2] INT, 
    [col3] INT, 
    [col4] CHAR(100) DEFAULT('abc') NOT NULL
);
GO

DECLARE @int INT;
SET @int = 1;

-- Load data into the table
WHILE (@int <= 1000) 
BEGIN
    INSERT INTO [TableA] 
        ([col1], [col2], [col3], [col4])
    VALUES (@int*2, @int*2, @int*2, @int*2);
    SET @int = @int + 1;
END
GO

CREATE CLUSTERED INDEX [cidx_TableA] 
ON [TableA] ([col1]);

-- Create a non-clustered index
CREATE NONCLUSTERED INDEX [idx_TableA_col2] 
ON [TableA] ([col2]);
GO

BEGIN TRANSACTION
    UPDATE [TableA] SET col3=999 WHERE Col2=4
--Rollback

--ON another session, I query this.
SELECT [col2],col3 FROM [TableA] 
WHERE [col2]=66
option (recompile)

The second session did using the key lookup to the Cluster index cidx_TableA and return the result, I expect it would no return any result because suppose it would lock by the update on first session. Why?

I am not yet create the covering index yet, because it did not make any blocking at all. So, did covering index really work?

enter image description here

Best Answer

There's a lot to unpack here, but I will say probably the best decision would be to start looking at Read Committed Snapshot Isolation (RCSI) this means you no longer require locks to read data (and as a bonus, you get meaningful results).

With the indexes as they currently are (assuming access via idx_TableA_col2), your update statement is going to lock

  • The entries in the idx_TableA_col2 index for the rows that get updated
  • The rows in the table that get updated

Your select statement does not need to read any of those locked bits because you can seek to WHERE [col2]=66 in the idx_TableA_col2 index (avoiding the locked index entries) and use that to access just the rows in the table that match this condition (again, these rows are not locked).

The locking problem a covering index is trying to avoid is for where a row is updated but the query does not need to worry about this column. Your update statement is updating col3, which is involved in your select statement so your covering index on ([col2],[col3]) would get locked for these rows too as the update statement will need to update the entries in this index.

In order to demo the problem a covering index would solve, you would need to change the update statement to not be updating col3 but have the where filter return the same rows:

BEGIN TRANSACTION
    UPDATE [TableA] SET col4=999 WHERE Col2=4
--rollback

--ON another session
SELECT [col2],col3 FROM [TableA] 
WHERE [col2]=4
option (recompile)

But as I mentioned in the first paragraph, using RCSI will avoid this problem completely.

Related Question