SQL Server – Begin Transaction Doesn’t Lock Table

sql serversql-server-2016

I have a table with this structure:

CREATE TABLE [dbo].[a](
    [id] [INT] IDENTITY(1,1) NOT NULL,
    [aa] [INT] NULL,
 CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Then create new transaction and then update all records, but don't commit it.
So I have somethings like this:

BEGIN TRANSACTION

UPDATE dbo.a
SET aa=4

In new session I execute select and get result! and see old value before the update.

SELECT * FROM a

When I want to execute update, it waits for another transaction commit.

My question is:

When we update a table in a transaction we create a lock on it until commit transaction and no one can select from it, but why can I?

I have 57 records and when I check lock state on sql with this:

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

I get result like this:

resource_type         request_mode          resource_description
------------------- ------------------- ------------------- 
OBJECT                IX                                          
OBJECT                IX                                          
OBJECT                IX                                          
KEY                   X                     (d8295d9f49e9)        
PAGE                  X                     4:17377               
PAGE                  X                     4:17376               
PAGE                  X                     4:17379               
PAGE                  X                     4:17378               
PAGE                  X                     4:17381               
PAGE                  X                     4:17380               
PAGE                  X                     4:17383               
PAGE                  X                     4:17382               
EXTENT                X                     4:17376               
OBJECT                Sch-S                                       
PAGE                  X                     4:17598               
PAGE                  X                     4:17599               
PAGE                  X                     4:17596               
PAGE                  X                     4:17597               
PAGE                  X                     4:17594               
PAGE                  X                     4:17595               
PAGE                  X                     4:17592               
PAGE                  X                     4:17593               
PAGE                  X                     4:17590               
EXTENT                X                     4:17592               
PAGE                  X                     4:17591               
PAGE                  X                     4:17588               
PAGE                  X                     4:17589               
PAGE                  X                     4:17586               
PAGE                  X                     4:17587               
PAGE                  X                     4:17614               
EXTENT                X                     4:17600               
PAGE                  X                     4:17615               
PAGE                  X                     4:17612               
PAGE                  X                     4:17613               
PAGE                  X                     4:17610               
PAGE                  X                     4:17611               
PAGE                  X                     4:17608               
PAGE                  X                     4:17609               
EXTENT                X                     4:17608               
PAGE                  X                     4:17606               
PAGE                  X                     4:17607               
PAGE                  X                     4:17604               
PAGE                  X                     4:17605               
PAGE                  X                     4:17602               
PAGE                  X                     4:17603               
PAGE                  X                     4:17600               
PAGE                  X                     4:17601               
PAGE                  X                     1:1412                
PAGE                  X                     1:1413                
PAGE                  X                     1:1414                
PAGE                  X                     1:1415                
PAGE                  X                     1:1409                
PAGE                  X                     1:1410                
PAGE                  X                     1:1411                
METADATA              Sch-S                 object_id = -1277451255, index_id or stats_id = 4            
METADATA              Sch-M                 object_id = -1277451255, stats_id = 4                        
METADATA              Sch-M                 object_id = -1277451255, stats_id = 3                        
METADATA              Sch-S                 object_id = -1277451255, index_id or stats_id = 2            
METADATA              Sch-M                 object_id = -1277451255, stats_id = 2                        
METADATA              Sch-S                 object_id = -1277451255, index_id or stats_id = 3            
PAGE                  X                     1:1647                
PAGE                  X                     1:1646                
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
OBJECT                X                                           
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
KEY                   X                     (508b5231be99)        
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
OBJECT                Sch-S                                       
KEY                   X                     (e54dfecee7ff)        
KEY                   X                     (bc73762086ca)        
ALLOCATION_UNIT       S                                           
OBJECT                Sch-S                                       
KEY                   X                     (8da779ef3812)        
KEY                   X                     (162836d95642)        
EXTENT                X                     3:29752               
PAGE                  X                     3:29755               
PAGE                  X                     3:29754               
PAGE                  X                     3:29753               
PAGE                  X                     3:29752               
PAGE                  X                     3:29759               
PAGE                  X                     3:29758               
PAGE                  X                     3:29757               
PAGE                  X                     3:29756               
EXTENT                X                     5:13736               
PAGE                  X                     5:13736               
PAGE                  X                     5:13737               
PAGE                  X                     5:13738               
PAGE                  X                     5:13739               
PAGE                  X                     5:13740               
PAGE                  X                     5:13741               
PAGE                  X                     5:13742               
PAGE                  X                     5:13743               
PAGE                  X                     3:29985               
PAGE                  X                     3:29984               
PAGE                  X                     3:29987               
PAGE                  X                     3:29986               
PAGE                  X                     3:29989               
PAGE                  X                     3:29988               
PAGE                  X                     3:29991               
PAGE                  X                     3:29990               
EXTENT                X                     3:29984               
PAGE                  X                     3:29969               
PAGE                  X                     3:29968               
PAGE                  X                     3:29971               
PAGE                  X                     3:29970               
PAGE                  X                     3:29973               
PAGE                  X                     3:29972               
PAGE                  X                     3:29975               
EXTENT                X                     3:29976               
PAGE                  X                     3:29974               
PAGE                  X                     3:29977               
PAGE                  X                     3:29976               
PAGE                  X                     3:29979               
PAGE                  X                     3:29978               
PAGE                  X                     3:29981               
PAGE                  X                     3:29980               
PAGE                  X                     3:29983               
PAGE                  X                     3:29982               
EXTENT                X                     3:29968               
OBJECT                Sch-S                                       
EXTENT                X                     3:29960               
PAGE                  X                     3:29961               
PAGE                  X                     3:29960               
PAGE                  X                     3:29963               
PAGE                  X                     3:29962               
PAGE                  X                     3:29965               
PAGE                  X                     3:29964               
PAGE                  X                     3:29967               
PAGE                  X                     3:29966               
KEY                   X                     (7688db35ca94)        
PAGE                  X                     5:13966               
PAGE                  X                     5:13967               
OBJECT                Sch-S                                       
EXTENT                X                     5:13976               
PAGE                  X                     5:13974               
PAGE                  X                     5:13975               
PAGE                  X                     5:13972               
PAGE                  X                     5:13973               
PAGE                  X                     5:13982               
PAGE                  X                     5:13983               
PAGE                  X                     5:13980               
PAGE                  X                     5:13981               
PAGE                  X                     5:13978               
PAGE                  X                     5:13979               
PAGE                  X                     5:13976               
PAGE                  X                     5:13977               
OBJECT                Sch-M                                       
KEY                   X                     (8c6bc9fc53ee)

Best Answer

If you can run a SELECT query against a table that another session has X locks on in Read Committed isolation, you must be in a database with READ_COMMITTED_SNAPSHOT set.

With READ_COMMITTED_SNAPSHOT Read Committed queries don't use Shared (S) locks. Instead if the query encounters a row that is locked or has been modified since the query started, it will read the "last known good" version of the row from the Version Store.

That's why you see the "old value before the update." If you need the other session's SELECT query to block until the transaction is complete, you can use a lock hint like UPDLOCK or READCOMMITTEDLOCK.

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Best Answer

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Sql-server – deteriorating stored procedure running times

Related Question