February 22, 2019 at 1:59 am
Hello there,
I generated an example to demonstrate some colleagues the behaviour of nolock within concurrent transactions.
While I thought I would understand what I'm doing, it shows that I actually do not understand what is going on. My experience in analysing lockings in this granularity is quite low. Maybe someone can help me understanding.
Please note that I am not looking for a way to solve the example or alternate architectural things, but rather have explanations of what is happening.
I am not using read committed snapshot.
Set up the example, Part 1
(please read the comments for understanding and don't miss the ATTENTION part)
-----------------------------------------------------------
-- create a test table
-----------------------------------------------------------
create table dbo.TestNolock
(
Id bigint not null identity(1,1)
, SomeValue varchar(5000 ) null
, UpdateDate datetime null
);
go
-----------------------------------------------------------
-- generate some records...
-- (and don't create locks on system tables)
-----------------------------------------------------------
set nocount on;
insert into dbo.TestNolock( SomeValue, UpdateDate )
select
replicate( 'x', 500 ), sysdatetime()
from
master.sys.columns as c1 with ( readuncommitted )
go 10
alter table dbo.TestNolock add constraint TestNolock_PK
primary key clustered ( Id asc )
go
create nonclustered index ix_TestNolock_UpdateDate
on dbo.TestNolock ( UpdateDate asc )
go
-- do some "beatification"...
-----------------------------------------------------------
alter index all on dbo.TestNolock rebuild;
update statistics dbo.TestNolock with fullscan;
go
-----------------------------------------------------------
-- A T T E N T I O N ! ! ! HERE COMES AN INFINITE LOOP ! ! !
-----------------------------------------------------------
-- Do some "workload" on the table.
-- While doing so, execute the second script just as a whole.
-----------------------------------------------------------
set transaction isolation level read committed;
set nocount on;
WHILE 1=1
BEGIN;
update dbo.TestNolock
set UpdateDate = getdate()
where Id = ( select top(1) Id from dbo.TestNolock order by UpdateDate asc )
;
END;
-- at the end, cleanup the test table
-----------------------------------------------------------
drop table dbo.TestNolock;
go
Set up the example, Part 2
While the workload of the upper script is running, we do a second workload.
Maybe some ETL process, that wants to get all changed data since the last run, so it makes a Select on the UpdateDate of the base table.if object_id( 'tempdb..#tmpInsert' ) is not null drop table #tmpInsert;
go
create table #tmpInsert
(
Id bigint not null primary key clustered
);
create table #tmpCount
(
Id bigint not null primary key clustered
, RecordCount bigint not null check( RecordCount=1 )
);
set transaction isolation level read committed;
set nocount on;
declare
@LastDate datetime = cast( getdate() as date )
, @Counter bigint = 0
;
WHILE 1=1
BEGIN;
BEGIN TRY;
set @Counter += 1;
truncate table #tmpInsert;
-- ===============================================================
-----------------------------------------
-- Variation 1
-----------------------------------------
-- As we insert the primary key value of the base table, there should not
-- ever happen something like a duplicate key on the temp table!
-- The temp table is defenitely empty at this point as we just truncated in in the previous statement.
-----------------------------------------
insert into #tmpInsert ( Id )
select t.Id
from dbo.TestNolock as t with ( readuncommitted ) --, index = ix_TestNolock_UpdateDate )
where t.UpdateDate > @LastDate;
-----------------------------------------
-- End Variation 1
-----------------------------------------
-- ===============================================================
if @Counter = 500 break; -- the expected error happens quite soon, so let's just break "early"...
END TRY
BEGIN CATCH --kill 225; -- we could kill the other process here to not produce unneccessary transactions on the DB
print 'LoopCount: ' + cast( @Counter as varchar(10) );
drop table #tmpInsert;
drop table #tmpCount;
throw;
break; END CATCH;
END; -- WHILE
drop table #tmpInsert;
drop table #tmpCount;
go
What happens and my understanding of it
(if not done already, please stop the first process with the infinite loop 😉 )
We should never get a PK Violation in the second script, as we just select another PK-Value.
But: The second process reads the index on the UpdateDate. While reading, the first process updates the read record, and within the index this record moves to the end. The second process, still reading this index, finally reaches the end, and reads this record a second time.
But?
When, in the second process, I raise the Isolation to read committed (I just replace the table hint), the error still occurs. I have to use repeatable read to not get the error.
But why?
I thought: Second process uses read committed, thus uses an S-Lock on all the read records, and releases it when having finished the statement. If so, the first process could not update any record that is read by the second process.
So why do I need repeatable read?
Variation 2
Now, in the second script, let's REPLACE the area marked as "Variation 1" with the following script, at the same position.
Then again, do the workload of process one and then execute the second script as a whole. -----------------------------------------
-- Variation 2
-----------------------------------------
-- uncomment this part and use it instead of the code of "Variation 1"
-- As we got a PK violation before, let's find out if there are more than one duplicate keys
-- If we get any duplicate value, we will get a violation of the check constraint on column "RecordCount"
--
-- Why does it not happen???
-----------------------------------------
insert into #tmpCount ( Id, RecordCount )
select t.Id, count(*)
from dbo.TestNolock as t with ( readuncommitted )-- ( readuncommitted )--, index = ix_TestNolock_UpdateDate )
where t.UpdateDate > @LastDate
group by t.Id
having count(*) > 1;
-----------------------------------------
-- End Variation 2
-----------------------------------------
What's going on?
The comment in the Variation-2-Script already asks the question: With my assumption so far I would have bet that I get duplicate key values. But why is that not happening? Has the behaviour really to do somehting with the insert in the temp table?
Thank you for anyone dealing with this example, I know it's long one, but locking and concurrency never has been easy, right?;)
Best greetings and have a nice weekend,
Wolfgang
February 22, 2019 at 8:22 am
Second process uses read committed, thus uses an S-Lock on all the read records, and releases it when having finished the statement.
No. Actually, in RC level, the shared lock is released as soon as SQL is done with that row, it does not wait until the end of the transaction. That's why non-repeatable reads and phantom reads are still possible with RC. The only real advantage of RC is preventing dirty reads and consistent IAM read results (very technical issue concerning getting dups in RU mode depending on how SQL at the lowest level physically reads the rows).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2019 at 9:57 am
Hello Scott, thank you for the answer.
That makes sense and the whole thing more clear. I thought non repeteable reads only occur when having at least 2 statements within 1 transaction. That’s wrong.
Now I even found the appropriate passage in the SQL documentation, regarding read committed and it’s lock behavior:
The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.
But does it also explain the Version 2 script, where I should 5he duplicate rows again ?
Thank you so far.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply