With Snapshot (read committed snapshot and snapshot isolation level) enabled, when data reading on a table takes place, if any records are being modified at the same time, the data will be read from the rows in version store rather than wait until data modification complete. Such behavior lets prgrammers think that reader process will NEVER ever be blocked by writer process. In most of the scenario, this is correct, however, when reader process is indirectly issued and running against a table, the isolation level for that reader process is always read committed lock isolation regardless the transacitoin isolation level of the parent statement.
When will the reader be indirectly issued? This happens when FK data verification are taken place. Let’s say there is a child table referencing to a parent table. When the FK column in child table is modified, the data modification process will have to lookup the parent table to ensure the existance of the parent record. In this case, when the parent record in the state of being modified, what would happen? Will the values in the version store of the parent record being used? Another way around, when the referenced key in the parent record is being modified, SQL Server will have to ensure there is no record in the child table referencing the key value. Such verification process will use read committed lock transaction isolation level.
Let’s look at the example here
if object_id('c') is not null--Child
drop table c
create table c(cid int primary key)
if object_id('p') is not null-- Parent
drop table p
create table p(pid int primary key)
go
alter table c add pid int references p(pid)
go
insert into p values(10),(20),(30)
insert into c values(1, 10)
insert into c values(2, 20)
goI inserted 3 values into the parent table and 2 record into child table. Then I update parent id 20 (see below) and keep the transaction open. In this case, the row with pid=20 is locked by X lock.
begin transaction update p set pid = 20 where pid =20
Now let’s open another session and run
update c set pid = 20 where cid = 2
This process will wait. This sometime is very confusing — 2 sessions are operating on 2 different tables, why blocks are generated?
Let rollback the transaction in the first session. The update statement will be completed.
Let’s do another test here, in session one, please enter
begin transaction update c set pid = 20 where cid = 2
and in another session enter
update p set pid = 40 where pid =30
Aha, the second session is blocked. This sympton is as confusing as previous example — both statements are operating at different objects but the second one is always blocked.
The reason behind is the Foreign Key between 2 tables. In the first example, when the second query runs, the foreign key pid in table c is changed, SQL Server will have to go to the parent table to check the existance of the pid=20. Record pid = 20 in the parent is locked by the first session in which it prevents the session from accessing the record, then the second session will have to wait until the lock held by the first session releases.
For the second demo above, the concept is similar to the first demo, we update the child table and leave the transaction open, which means that the record(s) in the second table is locked. When the second session modifies the primary key of the parent table, SQL Server has to check whether the key is referenced in a child table. Since there is no index on the referencing key in the child table, SQL Server has to perform a table scan to ensure that. One or few records are locked by the first session in which it prevents the second session from accessing the records in the child table. (resolving this in this demo is as simple as creating a none clustered index on the referencing key in the child table. This is another reason why build index on referencing columns in the child table is the best practise.)
You could use read committed snapshot and snapshot isolation level to re-run those 2 test. The results will be the same. Well, that’s true that SQL Server has to guarantee the data consistancy between parent and child tables. Any parent records not in committed state can not be referenced and vise versa any child records not in committed state can not prove that refereced keys are used.
From 2 examples above, you can see that there are indirect data reading processes involved for data integrity check. You may be curious why I say the indirect reading processes are under read committed lock transaction isolation level. In order to prove it, I am going to use Extended Event to collect the locking behavior of such situations.
CREATE ASSEMBLY [RunSQLStr]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030046936B4F0000000000000000E00002210B010800000E000000060000000000004E2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000F82C000053000000004000006003000000000000000000000000000000000000006000000C000000742C00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000540D000000200000000E000000020000000000000000000000000000200000602E7273726300000060030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000302D000000000000480000000200050034220000400A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3002002E00000001000011007E01000004250B280F00000A00002010270000281000000A0017731100000A0ADE0807281200000A00DC00062A00000110000002000E0015230008000000001B300200320000000200001100007E01000004280F00000A002010270000281000000A0017731100000A0ADE0E007E01000004281200000A0000DC00062A000001100000020001002021000E000000001B3004000B0100000300001100026F1300000A16FE01130711072D0C281400000A130638EC000000140C731500000A0D00096F1600000A13040972010000706F1700000A00096F1800000A00110472310000706F1900000A0011041F206F1A00000A130511056F1B00000A261105166F1C00000A6F1D00000A0A1105176F1C00000A6F1D00000A0B11056F1E00000A000972710000700607281F00000A6F1700000A00096F1800000A001104026F2000000A13081208FE161D0000016F1D00000A6F1900000A000011046F2100000A0C00DE05260000DE0000096F2200000A0000DE120914FE01130711072D07096F2300000A00DC00082C12086F1D00000A732400000A732500000A2B05281400000A13062B0011062A00011C00000000BB000CC700050100000102002400B3D70012000000001E02282600000A2A2E732600000A80010000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000004030000237E0000700300002004000023537472696E67730000000090070000080100002355530098080000100000002347554944000000A80800009801000023426C6F620000000000000002000001571502000902000000FA253300160000010000001E00000003000000010000000500000001000000260000000E0000000300000001000000020000000100000000000A0001000000000006003D0036000A00650050000A00840050000600BB00A9000600D200A9000600EF00A90006000E01A90006002701A90006004001A90006005B01A90006007601A9000600AE018F010600C201A9000600EE01DB013B00020200000600310211020600510211020A00900275020600B602A5020600C402A5020A000003EA020A000E03EA020A003A0327030A00610327030A007B03EA020A00890344000A00A70327030600D10336000A00DF03500006000304360000000000010000000000010001000100100014000000050001000100830110001D00000005000100050016009D001A0050200000000096006E000A0001009C2000000000960078000A000100EC200000000096008D000F0001002022000000008618970016000200282200000000911817042201020000000100A500210097001D00290097001D00310097001D00390097001D00410097001D00490097001D00510097001D00590097001D00610097002200690097001D00710097002700810097002D008900970016009100970016009900BE023700A100CB023C00110097002D009900D10237001900D602DD001900E102E100A90097001600A9001903E600B90047031D00B9005C031600C1006B031D00B1009903EB00D900B403DD00D900B903F2000900C203F700D900CB031600E100D803FB001900E9030201C100F5030701B900CB031600F1000F041600E90097001D00190097000B010900970016002000730032002E00230037012E002B0026012E000B0026012E00130031012E001B0031012E003B0031012E004B0031012E006B0079012E00330046012E005B0067012E0063007001400073003200600073004C0041004700110104800000010000007011A35B0000000000006F02000002000000000000000000000001002D00000000000200000000000000000000000100440000000000030002000000003C4D6F64756C653E0044656C61792E646C6C0044656C6179434C52005374617469635661726961626C6573006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C496E74333200576169743130536563005761697431305365635F310053716C43686172730052756E53514C537472002E63746F720053796E634F626A0053514C0053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650044656C6179004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E546872656164696E67004D6F6E69746F7200456E7465720054687265616400536C6565700045786974006765745F49734E756C6C006765745F4E756C6C0053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053716C436F6D6D616E6400437265617465436F6D6D616E640053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E007365745F436F6E6E656374696F6E537472696E67004F70656E004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C4461746152656164657200436F6D6D616E644265686176696F720045786563757465526561646572004462446174615265616465720052656164006765745F4974656D00546F537472696E6700436C6F736500537472696E6700466F726D61740053716C537472696E6700546F53716C537472696E6700457865637574655363616C61720049446973706F7361626C6500446973706F7365002E6363746F72000000002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D007400720075006500003F730065006C006500630074002000400040007300650072007600650072004E0061006D0065002C002000640062005F006E0061006D006500280029003B000080934400610074006100200053006F0075007200630065003D007B0030007D003B0049006E0069007400690061006C00200043006100740061006C006F0067003D007B0031007D003B0049006E00740065006700720061007400650064002000530065006300750072006900740079003D0053005300500049003B0050006F006F006C0069006E0067003D0074007200750065000000000AE5C10EA3615B419087D4DCD795B12D0008B77A5C561934E0890400001109060001120D120D0320000102061C042001010E042001010205200101113D04200101080401000000040001011C040001010805070211091C0407011109808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000003200002040000120D0420001259062001126511690420011C080320000E0600030E0E1C1C04200011750320001C0520010111751007090E0E1C125512591265120D021175030000010A01000544656C617900000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000046936B4F000000000200000068000000902C0000900E000052534453F083B510FC28794A8752341C26DE46C007000000433A5C55736572735C4A6F686E20485C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C44656C61795C6F626A5C44656275675C44656C61792E70646200202D000000000000000000003E2D0000002000000000000000000000000000000000000000000000302D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000080300000000000000000000080334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100A35B701100000100A35B70113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00468020000010053007400720069006E006700460069006C00650049006E0066006F00000044020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000340006000100460069006C0065004400650073006300720069007000740069006F006E0000000000440065006C0061007900000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003400360034002E00320033003400350039000000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000440065006C00610079002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003000000000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000440065006C00610079002E0064006C006C0000002C0006000100500072006F0064007500630074004E0061006D00650000000000440065006C0061007900000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003400360034002E00320033003400350039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003400360034002E00320033003400350039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000503D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE
go
create function dbo.RunSQLStr(@SQL [nvarchar](max))
returns [nvarchar](max) with execute as caller
as
external name [RunSQLStr].DelayCLR.[RunSQLStr]
go
create function dbo.ConvertedLockResource(@ResourceType sysname, @res0 bigint, @res1 bigint, @res2 bigint)
returns varchar(max)
as
begin
if @ResourceType = 'OBJECT'
return object_schema_name(@res0) + '.' + object_name(@res0) + ' - ' + cast(@res0 as varchar(20));
else if @ResourceType in ('PAGE', 'EXTENT')
begin
return cast(@res1 as varchar(10)) + ':' + cast(@res0 as varchar(20))
end
else if @ResourceType = 'RID'
begin
returncast(cast(cast(right(cast(@res1 as binary(8)),2) as binary(2)) as smallint) as varchar(10))+ ':'
+ cast(@res0 as varchar(20))+':'
+ cast(cast(cast(left(right(cast(@res1 as binary(8)),4), 2) as binary(2)) as smallint) as varchar(10))
end
else if @ResourceType = 'HOBT'
begin
return cast(cast(
cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
+cast(0x0000 as binary(2))
+ cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
+ cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
as bigint)
as varchar(20))
end
else if @ResourceType = 'KEY'
begin
declare @Hash varchar(20), @PStrID varchar(200), @SQL nvarchar(max)
select @Hash ='('
+ lower(convert( varchar(20),
cast(substring(cast(@res1 as binary(8)), 6, 1) as binary(1))
+ cast(substring(cast(@res1 as binary(8)), 5, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),8, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),7, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),6, 1) as binary(1))
+ cast(substring(cast(@res2 as binary(8)),5, 1) as binary(1))
,2))
+')',
@PStrID = cast(cast(
cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
+cast(0x0000 as binary(2))
+ cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
+ cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
as bigint)
as varchar(20))
select @SQL = '
declare @ParitionID varchar(50), @HashStr varchar(50)
select @ParitionID = ' +@PStrID+ ', @HashStr = '''+@Hash+'''
declare @IndexName sysname, @Output nvarchar(max), @SQL nvarchar(max), @TableName sysname
select
@Output = object_schema_name(p.object_id) + ''.'' + object_name(p.object_id) + ''.'' + i.name + ''(''+ stuff((select '',''+col_name(ic.object_id, ic.column_id) from sys.index_columns ic where ic.object_id = i.object_id and ic.index_id = i.index_id order by ic.key_ordinal for xml path('''')), 1, 1, '''') +'')'',
@IndexName = i.name, @TableName = quotename(object_schema_name(p.object_id)) + ''.'' + quotename(object_name(p.object_id)),
@SQL = ''select @Output =''''('''' + isnull((select ''+ stuff((select '','''''' +col_name(ic.object_id, ic.column_id) +''=''''+''+ ''cast(''+col_name(ic.object_id, ic.column_id) + '' as varchar(max)) '' from sys.index_columns ic where ic.object_id = i.object_id and ic.index_id = i.index_id order by ic.key_ordinal for xml path('''')), 1, 1, '''') +'' from '' + @TableName + '' with(index='' + quotename(@IndexName) +'') where %%lockres%% = @HashStr
for xml path('''''''')),'''''''') + '''')'''' + @Output ''
from sys.partitions p
inner join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
where p.partition_id = CAST(@ParitionID as bigint)
exec sp_executesql @SQL, N''@HashStr varchar(50), @Output nvarchar(max) output'', @HashStr, @Output output
select @Output '
select @SQL = dbo.RunSQLStr(@SQL)
return isnull(@SQL,'') + '-' + @Hash + '/' + @PStrID
end
return null
end
go
create procedure ShowEvents
as
begin
declare @Temp table (ID int identity(1,1), EventData xml);
insert into @Temp(EventData)
select cast(event_data as xml) event_data
from sys.fn_xe_file_target_read_file ('c:\temp\locktest00*', 'c:\temp\locktest00*.xem', null, null) a;
with x as (
select
a.ID, x.value('../@name', 'varchar(128)') EventName,
x.value('../@timestamp', 'datetime') EventTime,
x.value('@name','varchar(128)') [Col],
case when isnull(rtrim(x.value('(./text)[1]', 'varchar(128)')),'') = '' then x.value('(.)[1]', 'varchar(128)') else x.value('(./text)[1]', 'varchar(128)') end [Value]
from @Temp a
cross apply EventData.nodes('//data') ed(x)
)
select ID, EventName, DBID,
ResourceType, Mode, dbo.ConvertedLockResource(ResourceType, Res0, Res1, Res2) Resource,
OwnerType, TransID, Res0,
Res1, Res2, lockspace_nest_id,
lockspace_workspace_id
from(
select
ID, EventName, resource_type ResourceType,
mode Mode, resource_0 Res0,
resource_1 Res1, resource_2 Res2, lockspace_workspace_id,
lockspace_sub_id, lockspace_nest_id, owner_type OwnerType,
transaction_id TransID, database_id DBID, EventTime
from x
pivot( MAX([Value]) for Col in ([resource_type], [mode], [owner_type], [transaction_id], [database_id], [lockspace_workspace_id], [lockspace_sub_id], [lockspace_nest_id], [resource_0], [resource_1], [resource_2])) pvt
) y
where ResourceType not in ('METADATA') and Mode not in ('NL')
end
goI create a function, dbo.ConvertedLockResource, which converts the lock resource exposed in the Extended Event into a more human readable format, and a procedure, ShowEvents, which display the result of Extended Event in tabular format. The CLR function I created is just for executing dynamic SQL within a function. Now let’s create the event
CREATE EVENT SESSION locks
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target(set filename = 'c:\temp\locktest00')
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = NO_EVENT_LOSS ,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON,
STARTUP_STATE = OFF
);
goNow let’s do another test below
set transaction isolation level snapshot exec xp_cmdshell N'del c:\temp\locktest00*.*' ,no_output ALTER EVENT SESSION locks ON SERVER STATE = START go begin transaction update c set pid = 10 where cid = 2 rollback transaction go ALTER EVENT SESSION locks ON SERVER STATE = STOP go exec ShowEvents go
Let me interpret the result. When update on child happen
- Sch_S lock is granted to parent object first (ID = 2 in the result)
- IX lock is granted to child object (ID = 3 in the result)
- Lock the page where cid=2 in the child table with IX lock, because SQL Server is going to update the record in that page (ID = 4 in the result)
- X lock is place on record cid = 2 in the child table (ID = 5 in the result). This step guarantees that no one can access the child record while it’s being modified. (ID = 5 in the result)
- IS lock is granted to the parent object (ID = 6 in the result)
- IS lock is granted to the page which includes record pid = 10 in the parent object (ID = 7 in the result)
- S lock is granted to row pid = 10 in the parent object, this means that SQL Server place Shared lock to the record and read the value from it and while reading, no process can modify the record. (ID = 8 in the result)
- Look at the rest of the rows in the result, the Event Names are lock_release. Those means transaction is completed. All the locks are released.
Well, this still not be able to explain why the in-direct reader process is under read committed lock transaction isolation level. It’s not conclusive because there is only one record in the parent table needed to be verified. If we update multiple records in the child table with more keys to be varified in the parent table, it would become more conclusive.
set transaction isolation level snapshot exec xp_cmdshell N'del c:\temp\locktest00*.*' ,no_output ALTER EVENT SESSION locks ON SERVER STATE = START go begin transaction update c set pid = pid rollback transaction go ALTER EVENT SESSION locks ON SERVER STATE = STOP go exec ShowEvents go
You can see that within the transaction, while the parent records are being checked, pid = 10 in the parent is locked with Shared Lock and released afterward, then pid = 20 in the parent table is locked again and released. This is the typical locking behavior under read committed locked transaction isolation level.
