Taking down production using nested transactions

Most DBAs will have processes they need to run over a series of databases, and do so either with sys.sp_msforeachdb (which is bad, bad, bad because it can skip databases) or their own stored procedure which uses a static cursor over sys.databases.

Within that cursor you might also use an explicit transaction so that if the process running on one database failed, it would rollback and continue with the next database on the list or exit with an error. So the intended order of execution will be:

Outer Database (Cursor)
First Database (Begin Tran, Commit/Rollback)
Second Database (Begin Tran, Commit/Rollback)

This starts out great. And then over time that procedure becomes a black box. And then one day a cautious DBA wraps the procedure call in their own transaction. And when that happens, what happens? This is the question I had but I wasn't going in completely blind:

I knew transactions nest in an odd-way, where inner transactions are meaningless, and nothing commits until the last outer transaction is committed.
I also knew code within an sp_executesql doesn't have automatic access to variables in the parent block, and so runs in its own kind of special context.
I didn't know if transactions would nest between outside and inside sp_executesql, and I wanted to be 100% sure what the results would be.

So I put together a sample script to demonstrate and prove the potential issue to myself (the best way I learn).

If Not Exists (Select Top 1 0 From sys.databases Where Name = 'Database_Outer')
	Create Database Database_Outer
Go

Use Database_Outer
Go

-- This is the procedure we will be using to iterate other databases.
If Exists (Select Top 1 0 From sys.procedures Where Name = 'Lock_Outer')
	Drop Procedure dbo.Lock_Outer
Go

Create Procedure dbo.Lock_Outer
As
Begin
	Declare @DatabaseName nvarchar(Max)
	Declare @Command nvarchar(Max)
	Declare @Message Nvarchar(Max)

	Set		Nocount On

	-- Notify us of the transaction situation
	If	Xact_State() = 0
	Begin
		-- In a long-running procedure it can take a while for Print
		-- statements to get back to SSMS. Raiserror is faster.
		Raiserror('Starting: I am not running inside a transaction', 10, 1)
	End
	Else
	Begin
		Raiserror('Starting: I am running inside a transaction', 10, 1)
	End

	-- Static is important here, without it, changes to the databases while
	-- running (i.e. like doing a backup) will cause records to skip or the
	-- cursor to fail
	Declare DBCursor Cursor Static For
		Select	Name
		From	sys.databases
		Where	Name Like ('Database_Inner%')
		Order By 1

	Open	DBCursor
	Fetch	Next From DBCursor Into @DatabaseName
	While	@@fetch_status = 0
	Begin
		-- This is the command we will run on each database, creating a
		-- transaction, inserting a value into a table, and waiting for
		-- 15 seconds.
		Set		@Command = '
				Use ' + QuoteName(@DatabaseName) + '
				Begin Tran
				Insert	Table_To_Lock (Something) Values (''Ok'')
				WaitFor Delay ''00:00:15''
				Commit'

		Set		@Message = 'Locking ' + @DatabaseName + '.dbo.Table_To_Lock for 15 seconds'
		Raiserror(@Message, 10, 1) With Nowait
		Exec	sys.sp_executesql @Command
		Set		@Message = 'Unlocked'
		Raiserror(@Message, 10, 1) With Nowait

		Fetch	Next From DBCursor Into @DatabaseName
	End

	Close	DBCursor
	Deallocate DBCursor
End
Go

-- These are the two databases that will be locked, and a table to store
-- data in.
If Not Exists (Select Top 1 0 From sys.databases Where Name = 'Database_Inner_1')
	Create Database Database_Inner_1
Go
Use Database_Inner_1
Go
If Not Exists (Select Top 1 0 From sys.tables Where Name = 'Table_To_Lock')
	Create Table Table_To_Lock (
		[Id] Int Identity(1, 1) Primary Key,
		[Something] nvarchar(max)
	)
Go

If Not Exists (Select Top 1 0 From sys.databases Where Name = 'Database_Inner_2')
	Create Database Database_Inner_2
Go
Use Database_Inner_2
Go
If Not Exists (Select Top 1 0 From sys.tables Where Name = 'Table_To_Lock')
	Create Table Table_To_Lock (
		[Id] Int Identity(1, 1) Primary Key,
		[Something] nvarchar(max)
	)
Go

-- Our first test is a normal run
Print	'*** Run 1.'
Exec	Database_Outer.dbo.Lock_Outer
Go

-- Our second test is from within a transaction, just like a DBA does
Print	'*** Run 2'
Begin Tran
Exec	Database_Outer.dbo.Lock_Outer
Commit
Go

The plan was to set that running, and have a second script to run interactively in another session at each point a table was locked. By doing this I could inspect the locking situation across all three databases that would be involved.

Use Database_Outer
Go
Print 'Database_Outer'
Dbcc Opentran
Go

Use Database_Inner_1
Go
Print 'Database_Inner_1'
Dbcc Opentran
Go

Use Database_Inner_2
Go
Print 'Database_Inner_2'
Dbcc Opentran
Go

I started the script running and the output is below, with the locking information script output interspersed at each stage in green.

*** Run 1.
Starting: I am not running inside a transaction
Locking Database_Inner_1.dbo.Table_To_Lock for 15 seconds

Database_Outer
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database_Inner_1
Transaction information for database 'Database_Inner_1'.

Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (31:416:2)
Start time : Jan 17 2014 7:48:21:030PM
SID : 0x010500000000000515000000138899b286a01f9d883c7911e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database_Inner_2
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unlocked
Locking Database_Inner_2.dbo.Table_To_Lock for 15 seconds

Database_Outer
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database_Inner_1
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database_Inner_2
Transaction information for database 'Database_Inner_2'.

Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (31:419:1)
Start time : Jan 17 2014 7:48:36:057PM
SID : 0x010500000000000515000000138899b286a01f9d883c7911e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unlocked
*** Run 2
Starting: I am running inside a transaction
Locking Database_Inner_1.dbo.Table_To_Lock for 15 seconds

Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (31:417:1)
Start time : Jan 17 2014 7:48:51:077PM
SID : 0x010500000000000515000000138899b286a01f9d883c7911e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database_Inner_2
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unlocked
Locking Database_Inner_2.dbo.Table_To_Lock for 15 seconds

Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (31:417:1)
Start time : Jan 17 2014 7:48:51:077PM
SID : 0x010500000000000515000000138899b286a01f9d883c7911e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database_Inner_2
Transaction information for database 'Database_Inner_2'.

Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (31:420:1)
Start time : Jan 17 2014 7:49:06:080PM
SID : 0x010500000000000515000000138899b286a01f9d883c7911e9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unlocked

As you can see no data is touched in Database_Outer and so it never gets locked, and while the inner transactions on their own are okay, when run in combination with an outer transaction this causes each database on the server to become progressively locked until the entire set completes (which could take a while!) In production this could be a catastrophe. So here's my takeaway:

Code within sp_executesql is considered to have a special context, but transactions still nest. This was not very explicit in Books Online.
If you use anything that iterates databases you should check for open transactions at the beginning and notify the DBA what the consequences are. They may have no idea.

Share on: