3

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)
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.