Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

Locking Behavior – Foreign Keys

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)
go

I 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
		return	cast(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
go

I 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
);
go

Now 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

  1. Sch_S lock is granted to parent object first (ID = 2 in the result)
  2. IX lock is granted to child object (ID = 3 in the result)
  3. 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)
  4. 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)
  5. IS lock is granted to the parent object (ID = 6 in the result)
  6. IS lock is granted to the page which includes record pid = 10 in the parent object  (ID = 7 in the result)
  7. 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)
  8. 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.

LinkedInEmailBlogger PostShare/Bookmark

Comments

Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...