February 6, 2003 at 7:34 am
Hi
MS SQL Server 2000 SP3
Question about holding lock on the record untill transaction ends.
Let's say we have such situation.
A table:
CREATE TABLE [S_Atask] (
[atask_id] [int] IDENTITY (1, 1) NOT NULL ,
[im] [int] NOT NULL ,
[forma] [smallint] NOT NULL ,
[pateikta] [datetime] NOT NULL ,
[metai] [smallint] NOT NULL ,
[kvarta] [tinyint] NOT NULL CONSTRAINT [DF_S_Atask_kvarta_1] DEFAULT
(0),
[menuo] [tinyint] NOT NULL CONSTRAINT [DF_S_Atask_kvarta] DEFAULT
(0),
[atsak_asm] [nvarchar] (80) COLLATE Lithuanian_CI_AS NULL ,
[pareigos] [nvarchar] (80) COLLATE Lithuanian_CI_AS NULL ,
[varchar] (20) COLLATE Lithuanian_CI_AS NULL ,
[mail] [nvarchar] (100) COLLATE Lithuanian_CI_AS NULL ,
[pastabos] [nvarchar] (4000) COLLATE Lithuanian_CI_AS NULL ,
[ts] [timestamp] NOT NULL ,
CONSTRAINT [PK_S_Atask] PRIMARY KEY CLUSTERED
(
[atask_id]
) ON [PRIMARY] ,
CONSTRAINT [U_S_Atask] UNIQUE NONCLUSTERED
(
[im],
[forma],
[metai],
[kvarta]
) ON [PRIMARY] ,
CONSTRAINT [FK_S_Atask_Imones] FOREIGN KEY
(
[im]
) REFERENCES [Imones] (
[IM_ID]
) ON UPDATE CASCADE ,
CONSTRAINT [FK_S_Atask_S_Formos] FOREIGN KEY
(
[forma]
) REFERENCES [S_Formos] (
[formos_id]
) ON UPDATE CASCADE
) ON [PRIMARY]
As you can see there is PK_S_Atask PRIMARY KEY CLUSTERED and UNIQUE NONCLUSTERED CONSTRAINT'as U_S_Atask. They are the "heroes" of this situation.
The thing is that I want to hold the lock on a record from table S_Atask untill a transaction ends. So, assume we have 2 transactions
T1
---------------
BEGIN TRAN
SELECT
atask_id
FROM dbo.S_Atask
WHERE atask_id = 381;
WAITFOR DELAY '000:00:10'
COMMIT TRAN
---------------
and transaction T2
---------------
BEGIN TRAN
UPDATE s_atask SET
pareigos = 'lock test'
WHERE atask_id = 381;
WAITFOR DELAY '000:00:05'
COMMIT TRAN
---------------
i.e. T1 reads the record and should hold lock until T1 ends and T2 tries to update the same record simultaneously.
Let's say TRANSACTION ISOLATION LEVEL IS SET TO REPEATABLE READ.
Here's the result of
sp_lock (T1 - spid = 53; T2 - spid = 55)
spid IndId Type Resource Mode Status
53 0 DB S GRANT
53 1 KEY (7d0095322c46) S GRANT
53 0 TAB IS GRANT
53 1 PAG 1:635 IS GRANT
55 0 DB S GRANT
55 1 PAG 1:635 IX GRANT
55 1 KEY (7d0095322c46) X CNVT
55 0 TAB IX GRANT
55 1 KEY (7d0095322c46) U GRANT
Everything works fine, but when I try to change WHERE condition in T1 from
WHERE atask_id = 381
to
WHERE im = 23
AND forma = 110
AND metai = 2002
AND kvarta = 2
i.e. when I change the criteria according to unique constraint U_S_Atask (both conditions return the same record), T2 doesn't wait for T1 to end, because T1 locks different index page. Result of
sp_lock is
spid IndId Type Resource ModeStatus
53 0 DB S GRANT
53 0 TAB IS GRANT
53 2 PAG 1:1858 IS GRANT
53 2 KEY (60012766dd50) S GRANT
55 0 DB S GRANT
55 1 PAG 1:635 IX GRANT
55 1 KEY (7d0095322c46) X GRANT
55 0 TAB IX GRANT
As you can see T1 is holding lock on page 1:1858 and key 60012766dd50 (part of UNIQUE CONSTRAINT U_S_Atask) and T2 - on page 1:635 and key 7d0095322c46 (part of CLUSTERED PRIMARY KEY PK_S_Atask).
I assume one way to solve it is to "force" the query optimizer to use CLUSTERED index (in this case PK_S_Atask) instead of UNIQUE CONSTRAINT (U_S_Atask):
SELECT
atask_id
FROM dbo.S_Atask WITH (INDEX (0))
WHERE im = 23
AND forma = 110
AND metai = 2002
AND kvarta = 2;
but in this case logical-reads increase 7 times, because optimizer initiates PK_S_Atask scan instead of U_S_Atask seek and it's not good for performance. Besides T1 holds locks not only on page 1:635, but on 5 more pages.
I hope I made myself clear...;)
I wonder if there is a better solution of this problem?
Thank You
February 10, 2003 at 8:00 am
This was removed by the editor as SPAM
February 11, 2003 at 8:47 am
Have you tried the locking hints, such as WITH (HOLDLOCK)
February 12, 2003 at 2:42 am
Thanks for reply.
Well, the problem is not that T1 releases locks before it ends, actually it holds them till the end (SET TRANSACTION ISOLATION LEVEL REPEATABLE READ insures it). The problem is T1 places the lock not on the data page (clustered index page) but on the nonclustered index page. DBCC EXTENTINFO shows page 1858 is one of the nonclustered index pages, wich just holds the value of clustered index for the particular record (atask_id = 381 in this case). In other words T1 places the lock on the bookmark, not on the record. Thats why T2 can place X lock on the same record, because it places the lock on page 635 - the data page (clustered index page) and nothing tells it this record is locked in other transaction.
I tried to reproduce this behaviour with new table and here's an interesting thing I found out. Here's what I did.
First we need a table
CREATE TABLE [tblTest] (
[rec_id] [int] IDENTITY (1, 1) NOT NULL ,
[cond_1] [int] NOT NULL ,
[cond_2] [int] NOT NULL ,
[text_1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[text_2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[rec_id]
) ON [PRIMARY] ,
CONSTRAINT [U_tblTest] UNIQUE NONCLUSTERED
(
[cond_1],
[cond_2]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Now we'll insert some data
DECLARE
@i int
SET @i = 1
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.tblTest (cond_1, cond_2, text_1, text_2)
VALUES (@i, @i, 'cond_1 = ' + CAST (@i AS nvarchar), 'cond_2 = ' + CAST (@i AS nvarchar))
SET @i = @i +1
END
Make some order
DBCC DBREINDEX ('dbo.tblTest', PK_tblTest, 80)
DBCC DBREINDEX ('dbo.tblTest', U_tblTest)
Here's what we have now
DBCC EXTENTINFO (9, 'dbo.tblTest')
file_id page_id pg_alloc ext_size obj_id index_id
1 152 8 8 469576711 1
1 160 5 8 469576711 1
1 90 1 1 469576711 2
1 92 1 1 469576711 2
1 95 1 1 469576711 2
So we have 13 filled data pages (clustered index PK_tblTest (index_id = 1) pages) and 3 nonclustered index U_tblTest (index_id = 2) pages.
Let's get back to transactions.
Transaction T1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT
rec_id
FROM dbo.tblTest
WHERE cond_1 = 5
AND cond_2 = 5;
-- it will return rec_id = 5
WAITFOR DELAY '000:00:10'
COMMIT TRAN
and transaction T2
BEGIN TRAN
UPDATE dbo.tblTest SET
text_1 = 'updated'
WHERE rec_id = 5;
-- trying to update record T1 should lock
WAITFOR DELAY '000:00:05'
COMMIT TRAN
Now let's run them simultaniously and see what sp_lock shows while transactions are running
Results of sp_lock (T1 spid = 54 and T2 spid = 56)
spid dbid ObjId IndId Type Resource Mode Status
54 9 469576711 2 PAG 1:90 IS GRANT
54 9 0 0 DB S GRANT
54 9 469576711 0 TAB IS GRANT
54 9 469576711 2 KEY (0a00c0dee3e5) S GRANT
56 9 469576711 0 TAB IX GRANT
56 9 0 0 DB S GRANT
56 9 469576711 1 PAG 1:152 IX GRANT
56 9 469576711 1 KEY (0500d1d065e9) X GRANT
As we can see there is no locking conflict. Page 90 is one of the U_tblTest index pages and page 152 is one of the PK_tblTest pages (or data pages). Let's
take a closer look at these pages.
DBCC PAGE (9, 1, 90, 3)
FileId PageId Row Level cond_1 cond_2 rec_id
1 90 0 0 NULL NULL NULL
1 90 1 0 2 2 2
1 90 2 0 3 3 3
1 90 3 0 4 4 4
1 90 4 0 5 5 5
1 90 5 0 6 6 6
T1 places S lock on Row 4 (WHERE cond_1 = 5 AND cond_2 = 5) of the index page.
DBCC TRACEON (3604)
DBCC PAGE (9, 1, 152, 3)
PAGE: (1:152)
-------------
BUFFER:
-------
.......
PAGE HEADER:
------------
.......
Slot 0 Offset 0x60
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
.......
Slot 4 Offset 0x164
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
19ECA164: 00100030 00000005 00000005 00000005 0...............
19ECA174: 02000005 3b002700 70007500 61006400 .....'.;.u.p.d.a
19ECA184: 65007400 63006400 6e006f00 5f006400 .t.e.d.c.o.n.d._
19ECA194: 20003200 20003d00 003500 .2. .=. .5.
rec_id = 5
cond_1 = 5
cond_2 = 5
text_1 = updated
text_2 = cond_2 = 5
Slot 5 Offset 0x1a5
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
.......
T2 places X lock on the Slot 4 (WHERE rec_id) of the data page. That's why no locking conflict occurs and T2 can easily update columns not belonging to clustered index PK_tblTest nor unique nonclustered index U_tblTest.
Now if we check execution plan of T1 this is what happens
|--Index Seek(OBJECT:([Tests].[dbo].[tblTest].[U_tblTest]), SEEK:([tblTest].[cond_1]=Convert([@1]) AND [tblTest].[cond_2]=Convert([@2])) ORDERED FORWARD)
A simple and effective nonclustered index seek. Now let's change the SELECT statement in T1 to
SELECT
rec_id,
text_1
FROM dbo.tblTest
WHERE cond_1 = 5
AND cond_2 = 5;
and rerun T1 (spid = 54) and T2 (spid = 56). Results of sp_lock now are
spid dbid ObjId IndId Type Resource Mode Status
54 9 0 0DB S GRANT
54 9 469576711 2PAG 1:90 IS GRANT
54 9 469576711 1PAG 1:152 IS GRANT
54 9 469576711 0TAB IS GRANT
54 9 469576711 2KEY (0a00c0dee3e5) S GRANT
54 9 469576711 1KEY (0500d1d065e9) S GRANT
56 9 469576711 1KEY (0500d1d065e9) X WAIT
56 9 0 0DB S GRANT
56 9 469576711 0TAB IX GRANT
56 9 469576711 1PAG 1:152 IX GRANT
Now T2 has to wait for T1 to release the lock, because T1 is holding S locks on Row 4 of the index page and Slot 4 of the data page. If we check execution plan of T1 again we'll see bookmark lookup comes up
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Tests].[dbo].[tblTest]))
|--Index Seek(OBJECT:([Tests].[dbo].[tblTest].[U_tblTest]), SEEK:([tblTest].[cond_1]=Convert([@1]) AND [tblTest].[cond_2]=Convert([@2])) ORDERED FORWARD)
This bookmark lookup forses T1 to place S lock on the Slot 4 of the data page.
I don't know how to classify such behaviuor of SQL Server - is it a bug or it's done on purpose, but I definintely don't like it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply