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)
