Sql-server – UPDATE Blocking SELECT Of Unrelated Rows

sql server

I have TableA with Col1 as the primary key. I am running the following transaction without committing it (for test purposes).

BEGIN TRANSACTION
UPDATE TableA
SET Col3 = 0
WHERE Col2 = 'AAA'

In the meanwhile, I run the following query and see that it waits on the first transaction to complete.

SELECT *
FROM TableA
WHERE Col2 = 'BBB'

But the following query returns the results immediately:

SELECT *
FROM TableA
WHERE Col1 = '1'

So I thought that the second query might need to read rows that have exclusive locks put by the first transaction in order to select rows with Col2 = 'BBB'. That's why then I tried to index Col2 so that a table seek will not be necessary but that did not work either. Second query still waits on the first transaction.

What should be done to prevent SELECT from blocking (except the use of NOLOCK).

P.S: Transaction isolation level is "Read Committed".

Best Answer

I might have to modify this answer once you provide more information based on my comment.

For now follow this example. I am using SQL 2016, SP1.

--Creating table
    DROP TABLE IF EXISTS TableA
    CREATE TABLE [dbo].[TableA](
      [col1] [int] NULL,
      [col2] [nchar](10) NULL,
      [col3] [int] NULL
    ) ON [PRIMARY]

   GO

Inserting data:

INSERT INTO [dbo].[TableA]
           VALUES (10, 'AAA', 11),
                  (12, 'BBB', 13),
                  (14, 'CCC', 15)

Session ID 65 running this.

BEGIN TRANSACTION
UPDATE TableA
SET Col3 = 0
WHERE Col2 = N'AAA'

Session ID 66 running this:

SELECT *
FROM TableA
WHERE Col2 = N'BBB'

Looking at the locks held by both session. Session 65 is holding an exclusive lock on file id:1, page id:488 and slot id=0, shared lock is requested on the same resource by session 66 and is waiting.

Running the 2nd query and looking at the actual execution plan it is doing a table scan and because of your isolation level reading requires a shared lock to prevent any update or delete on the same row.

Creating an index on col2 does not change the behavior in my case because of number of rows.

CREATE NONCLUSTERED INDEX [NCI_TableA_col2] ON [dbo].[TableA]
(
    [col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, 
DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

After I inserted 3000+ rows I can get a index seek and session 66 will not wait for the session 65 anymore.

Why did your 3rd query ran without waiting: I can answer that once you provide more information.

Related Solutions

Sql-server – How to create implicit select statements

Not sure that this is simpler than a cursor solution, but here is what I would do:

Before we begin we need a few tables in a SQL Fiddle:

MS SQL Server 2012 Schema Setup

CREATE TABLE dbo.tbl1(c1 INT, c2 INT, c3 INT);
CREATE TABLE dbo.tbl2(c4 INT, c5 INT, c6 INT);
CREATE TABLE dbo.tbl3(c7 INT, c8 INT, c9 INT);

First we need the list of all user tables. For that we can use the sys.tables catalog view. The OBJECT_SCHEMA_NAME() function gets us the schema name, the OBJECT_NAME() function the table name. The QUOTENAME() function quotes the names correctly, in case some of those contain special characters, key words or spaces. (In this example the use of OBJECT_NAME() is not strictly necessary as sys.tables has a name column, but I left it in as you can use this pattern with any catalog view that has an object_id column.)

Query 1:

SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id))+'.'+
  QUOTENAME(OBJECT_NAME(T.object_id)) AS quoted_table_name,
  T.object_id
FROM sys.tables AS T;

Results:

| QUOTED_TABLE_NAME | OBJECT_ID |
|-------------------|-----------|
|      [dbo].[tbl1] | 245575913 |
|      [dbo].[tbl2] | 261575970 |
|      [dbo].[tbl3] | 277576027 |

The next step is to get the list of column names, again quoted. We can use the sys.columns catalog view for that.

Query 2:

SELECT C.name,C.column_id 
  FROM sys.columns AS C
 WHERE C.object_id = OBJECT_ID('dbo.tbl1');

Results:

| NAME | COLUMN_ID |
|------|-----------|
|   c1 |         1 |
|   c2 |         2 |
|   c3 |         3 |

The next hurdle is to get those columns in a comma separated list. There is no string concatenation aggregate function build in so we have to use a trick:

Query 3:

SELECT STUFF((
  SELECT ','+QUOTENAME(name)
    FROM sys.columns AS C
   WHERE C.object_id = OBJECT_ID('dbo.tbl1')
   ORDER BY C.column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'') AS clomun_list;

Results:

|    CLOMUN_LIST |
|----------------|
| [c1],[c2],[c3] |

With that all pieces are in place and we just have to put them all together:

Query 4:

SELECT 'SELECT ' +
       CL.column_list +
       ' FROM ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id)) + '.' +
       QUOTENAME(OBJECT_NAME(T.object_id)) +
       ';' AS select_statement
FROM sys.tables AS T
CROSS APPLY (
  SELECT STUFF((
    SELECT ','+QUOTENAME(name)
      FROM sys.columns AS C
     WHERE C.object_id = T.object_id
     ORDER BY C.column_id
       FOR XML PATH(''),TYPE
    ).value('.','NVARCHAR(MAX)'),1,1,'') AS column_list
  )CL;

Results:

|                         SELECT_STATEMENT |
|------------------------------------------|
| SELECT [c1],[c2],[c3] FROM [dbo].[tbl1]; |
| SELECT [c4],[c5],[c6] FROM [dbo].[tbl2]; |
| SELECT [c7],[c8],[c9] FROM [dbo].[tbl3]; |

This example will work in SQL 2005 and later, assuming you are on the latest service pack. There is no clean solution to achieve this with SQL Server 2000. In that case you need to go back to your cursor solution.

SQL Server Optimization – New Query Slower Despite Fewer Logical Reads

The percentage costs on an execution plan are from the optimizer estimates, even when an actual execution plan produced. The actual execution plan does use the exact plan and include both the estimated rows and the actual row counts. Discrepancies between the row counts can be useful to determine how accurate the estimate was.

Somewhere between the subquery and comparing it to the column in derived, the optimizer wasn't able to correctly estimate how many rows would match. It guessed that there would be 18 rows from derived when there were actually over 220,000. An additional clue is the warning message Cardnality Estimate: CONVERT(nvarchar(35),[mssqlsystemresource].[sys].[spt_values].[name],0) on the SELECT node.

If you were to check the query run length with something else, such as STATISTICS TIME, I would expect them to be much closer, and likely the second query running faster.

Here's another plan analysis with a somewhat similar situation. (SQL Server Plan Explorer) (with hat tip to Kendra Little on how to fool the optimizer)

The estimate shows a 93%/7% cost split, but by looking at the actual CPU, time, or IO, the difference is not that extreme. IO is about 75%/25% and CPU is roughly 60%/40%. (I tried to come up with something more even, but wasn't able to.)

Best Answer

Related Solutions

Sql-server – How to create implicit select statements

SQL Server Optimization – New Query Slower Despite Fewer Logical Reads

Related Question