SQL Server – Referential Integrity – Foreign Key Workings – Tracking Through Transaction Log

Background

Quick follow-up to our earlier discussion on SQL Server Referential Integrity – “How is it done” (https://danieladeniji.wordpress.com/2014/04/30/technical-microsoft-sql-server-referential-integrity-review-query-plan/)

Transaction Log Reading

We will dig through the Transaction Log and see if we can see how SQL Server does its work.

 

Successful Transaction

Here is what a successful transaction looks like.


set nocount on
set xact_abort off
go

use [DBLAB]
go

delete from dbo.[order]
delete from dbo.[customer]
go

backup database [DBLAB] to disk = 'NUL:' with init
go
backup log [DBLAB] to disk = 'NUL:' with init
go

begin tran trnDeleteGood

insert into [dbo].[customer]
(
[Customer_ID]
, [CustomerName]
, [inceptionDate]
, [expiryDate]
, [active]
, [dateAdded]
, [addedBy]
)
values
(
-100
, 'Kirkland'
, getdate()
, null
, 1
, getdate()
, SYSTEM_USER
)

insert into [dbo].[order]
(
[Customer_ID]
, [dateAdded]
, [addedBy]
)
select
-100
, getdate()
, SYSTEM_USER

commit tran trnDeleteGood

go


select
tblLog.[Current LSN]
, tblLog.SPID
, tblLog.[Database Name]
, tblLog.Operation
, tblLog.Command
, tblLog.AllocUnitName
, tblLog.[Transaction ID]
, tblLog.[Transaction Name]
, tblLog.[Number of Locks]

from sys.fn_dblog(null, null) tblLog

INNER JOIN sys.allocation_units tblAllocationUnit

ON tblLog.AllocUnitId = tblAllocationUnit.allocation_unit_id

INNER JOIN sys.partitions tblPartition

ON tblAllocationUnit.container_id = tblPartition.partition_id

INNER JOIN sys.objects tblObject

ON tblPartition.object_id = tblObject.object_id
order by tblLog.[Current LSN] desc

Output:

TransactionGood

 

Explanation:

  • Raw Transaction Log data is accessible through querying the sys.fn_dblog.   For our current purpose, the most reliable column is the AllocUnitID.We can join that column to the sys.allocation_units.  And, from sys.allocation_units get allocation_unit_id.Join sys.allocation_units to sys.partitions using container_id.And, join sys.partitions to sys.objects using object_id.
  • In the screen above, we ordered with most recent first
  • The 3rd entry is our insert into dbo.customer (LOP_INSERT_RECORDS)
  • The 2nd entry is our LOP_SET_BITS
  • The 1st entry is our insert into dbo.order (LOP_INSERT_RECORDS)

 

 

Failed Transaction

Here is what a failed transaction looks like.


set nocount on
set xact_abort off
go

use [DBLAB]
go

while (@@trancount > 0)
begin

print 'rollback'
rollback;

end
go
delete from dbo.[order]
delete from dbo.[customer]
go

backup database [DBLAB] to disk = 'NUL:' with init
go
backup log [DBLAB] to disk = 'NUL:' with init
go

begin tran trnDeleteFailed

insert into [dbo].[customer]
(
[Customer_ID]
, [CustomerName]
, [inceptionDate]
, [expiryDate]
, [active]
, [dateAdded]
, [addedBy]
)
values
(
-100
, 'Kirkland'
, getdate()
, null
, 1
, getdate()
, SYSTEM_USER
)

insert into [dbo].[order]
(
[Customer_ID]
, [dateAdded]
, [addedBy]
)
select
-1002 -- customer id does not exist
, getdate()
, SYSTEM_USER

commit tran trnDeleteFailed

go


select
tblLog.[Current LSN]
, tblLog.SPID
, tblLog.[Database Name]
, tblLog.Operation
, tblLog.Command
, tblLog.AllocUnitName
, tblLog.[Transaction ID]
, tblLog.[Transaction Name]
, tblLog.[Number of Locks]

from sys.fn_dblog(null, null) tblLog

INNER JOIN sys.allocation_units tblAllocationUnit

ON tblLog.AllocUnitId = tblAllocationUnit.allocation_unit_id

INNER JOIN sys.partitions tblPartition

ON tblAllocationUnit.container_id = tblPartition.partition_id

INNER JOIN sys.objects tblObject

ON tblPartition.object_id = tblObject.object_id
order by tblLog.[Current LSN] desc

Output:
FailedTransaction

 

Explanation:

  • In the screen above, we again ordered with most recent first
  • The 4th entry is our insert into dbo.customer (LOP_INSERT_RECORDS)
  • The 3rd entry is our LOP_SET_BITS
  • The 2nd entry is our insert into dbo.order (LOP_INSERT_RECORDS)
  • The 1st entry is our “forced” removal of the orphaned insert into dbo.order (LOP_DELETE_ROWS)

 

 

Listening

As previously hinted, like a bird digging through SQL Server’s Log looking for one man’s waste..

The Neville Brothers – Bird on a Wire

 

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s