July 29, 2015 at 9:33 am
Playing with repeatable read and I understand that it takes locks and holds them until the transaction completes, but when looking at the locks with DBCC it looks like the locks are acquired and released as the table is accessed.
I know that isn't the case, because I can see a second process being blocked if I try certain updates but I'm trying to understand what DBCC is showing me.
DBCC TRACEON( 3604 )
DBCC TRACEON( 1200, -1 )
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT [ID],
[Test_Value]
FROM dbo.Test2
ORDER BY ID ASC;
DBCC TRACEOFF( 3604 )
DBCC TRACEOFF( 1200, -1 )
Table Test2 is nothing exciting, 2 million row with 'ID' incrementing from 1 to 2 million, and 'Test_Value' containing the same value.
A snippet from the display from DBCC:
Process 72 acquiring S lock on PAGE: 5:1:128012 (class bit2000000 ref1) result: OK
Process 72 releasing lock reference on PAGE: 5:1:128011
Process 72 acquiring S lock on PAGE: 5:1:128013 (class bit2000000 ref1) result: OK
Process 72 releasing lock reference on PAGE: 5:1:128012
Process 72 acquiring S lock on PAGE: 5:1:128014 (class bit2000000 ref1) result: OK
Process 72 releasing lock reference on PAGE: 5:1:128013
Process 72 acquiring S lock on PAGE: 5:1:128015 (class bit2000000 ref1) result: OK
Process 72 releasing lock reference on PAGE: 5:1:128014
A lock is aquired on 128012 and released three lines later, as with 128013, 128014 and so on throughout the entire display.
What is this really showing me?
July 29, 2015 at 10:04 am
Well, I can't say why with any certainty, but I know I've seen different results from enabling 1200 than I have from tracing Lock:Acquired and Lock:Released events, with the results from the tracing making more sense.
Having said that, I'm not sure why it would hold the locks here anyway, since I don't see a transaction started. I guess perhaps you did that outside the snippet shown?
I also haven't been able to reproduce those DBCC results on my machine either. What's the setup for this (exact version, DDL for table and any indexes, etc.)?
At any rate, I'm guessing the output from enabling 1200 is perhaps not the most reliable, which fits my past experience. I prefer to trace if I want to dig into that (or if I just want to see what locks persist, query sys.dm_tran_locks).
Cheers!
July 30, 2015 at 1:37 am
Hi Jacob,
Thanks for taking the time.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test2](
[ID] [int] NOT NULL,
[Test_Value] [int] NOT NULL,
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]
GO
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
There isn't a transaction around that, in this example. The SELECT takes about 7 seconds to run against the two million rows and will be within its own transaction. Place a BEGIN TRANSACTION and COMMIT around those and it does the same.
I'm getting the behaviour I'm expecting, but it isn't being confirmed by the 1200 setting, so I wanted to make sure I wasn't misunderstanding something.
July 30, 2015 at 10:01 am
No problem, and thanks for the additional info!
The index was the reason I wasn't seeing the same results. Once I added that I saw the same results as you did.
In this case those released locks show up both in the 1200 output and in a trace on Lock:Released events.
Interestingly, those releases that occur during query execution show as having a mode of Null. The release events for lock mode S don't occur until the end of the query, or at COMMIT/ROLLBACK if the select is within an explicit transaction.
I haven't really found any good documentation on exactly what a Lock:Released event with a mode of Null actually means; at any rate, those aren't releasing the shared locks. Those release events occur later. It's easier to see if you trace Lock:Released and Lock:Acquired events for the session you're running the query in, output the results to a table, and analyze it there.
Then you'll see that the query takes out a bunch of shared page locks, as we'd expect. Interspersed with those are Lock:Released events on those same pages, but with a mode of Null, not Shared.
So, the shared locks behave as expected. I would like to know what the Lock:Released with a NULL mode mean, but I'm not optimistic about that given my investigation so far.
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply