Abstract
This article explains lock issues in SQL Server with DDL statements including SELECT INTO clause.
Use of explicit transaction is common in SQL Server development. Sometimes a developer might inadvertently specify a Data Definition Language clause within a long running explicit transaction, similar to the structure below, within a SQL Server object such as a stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE PROCEDURE ... AS BEGIN TRAN -- Many DML (UPDATE\INSERT\DELETE) SELECT columnlist, .. INTO #temp FROM dbo.LargeTable WHERE ... -- Long running DML (UPDATE\INSERT\DELETE) ... (COMMIT OR ROLLBACK) |
This article describes the side effect of having DDL within a long running transaction.
DDL and Transaction
Data Definition Language (DDL) statements such as CREATE TABLE honors transactions. So if a BEGIN TRAN is specified, followed by a CREATE TABLE and then a ROLLBACK, then the table will not be created.
SELECT INTO works similarly to CREATE TABLE because it creates a new table with the columns returned by the SELECT statement. Under the cover, it begins an implicit transaction and populates the related system tables with the necessary rows to define the table structure same as a CREATE TABLE DDL.
When a DDL statement is specified within an explicit transaction, the underlying row(s) in the affected system table will be exclusively locked during this duration until the transaction is explicitly committed.
An inadvertent effect due to locked row(s) in the affected system tables are blockings or timeout on monitoring queries, regular administrative tasks and queries which require to scan all rows in the affected system tables. This article will demonstrate the side effects on SQL Server 2016 Developer Edition Service Pack 1.
Test Environment
We will create a test database and interrogate the transaction log using the undocumented system function fn_dblog. The column [Transaction ID] contains the system degenerate transaction ID for all logged operations. The same value in the [Transaction ID] column means the operations logged belongs to the same transaction scope.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE DATABASE test GO USE test GO ALTER DATABASE test SET RECOVERY SIMPLE GO CHECKPOINT GO WAITFOR DELAY '00:00:10' -- Allow time for checkpoint GO CREATE TABLE dbo.test (col1 INT) GO DECLARE @transactionid nvarchar(28) SELECT @transactionid = [transaction id] FROM fn_dblog(null,null) WHERE [Transaction Name] = 'CREATE TABLE' SELECT Operation, Context, AllocUnitName, COUNT(*) OpCount FROM fn_dblog(null,null) WHERE [Transaction ID] = @transactionid GROUP BY Operation, Context, AllocUnitName |
A wealth of information can be obtained from the transaction log using system function fn_dblog. The screenshot above shows us the system tables involved when a CREATE TABLE is executed.
LOP_BEGIN_XACT is the (implicit) begin transaction.
LOP_COMMIT_XACT is the (implicit) commit transaction.
Explicit Transaction and SELECT INTO
If we specify an explicit begin transaction, the SQL Server transaction log still logs this as LOP_BEGIN_XACT. A manual COMMIT TRAN will generate LOP_COMMIT_XACT similarly to an implicit commit transaction.
We will use SELECT INTO in this contrived example, because this is the more commonly used in SQL Server development.
We will start an explicit transaction and use SELECT INTO to create a temp table.
When SELECT INTO is executed into a temporary table, the transaction will be logged in tempdb even though the context of the database is on a user database. So in this scenario, the fn_dblog function is executed in the context of tempdb to obtain details of the SELECT INTO transaction. When we interrogate the transaction log, we would see a LOP_BEGIN_XACT but without the corresponding LOP_COMMIT_XACT because the transaction is still uncommitted.
A lesser-known trick is that SQL Server allows you to label an explicit transaction with a name. This transaction name is recorded in the transaction log [Transaction Name] column. This would then allow fn_dblog to easily search and identify all rows which belongs to the same transaction scope using the transaction name.
In the code example below, we have an explicit transaction name “tmp” which gets stamps into the transaction log to allow us to filter the result by [Transaction ID].
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE test GO BEGIN TRAN tmp SELECT * INTO #temp FROM test GO -- COMMIT TRAN tmp USE tempdb GO CHECKPOINT GO DECLARE @transactionid nvarchar(28) SELECT @transactionid = [Transaction ID] FROM fn_dblog(null,null) WHERE [Transaction Name] = 'tmp' SELECT Operation, Context, AllocUnitName, COUNT(*) OpCount FROM fn_dblog(null, null) WHERE [Transaction ID] = @transactionid AND Operation IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT') GROUP BY Operation, Context, AllocUnitName |
As expected, we can see the SELECT INTO started a transaction, but do not have a corresponding commit transaction (LOP_COMMIT_XACT) operation yet. It just means that the transaction is not committed yet.
Locks in System Tables
If you check for locks, there will be a bunch of Intent Exclusive locks on system tables and Exclusive locks on index rows (KEYS), PAGE and EXTENT.
The locks are against system tables in tempdb since the SELECT INTO automatically goes into tempdb as a temp table. This query below needs to be executed within the same session that started the transaction, otherwise the query will be blocked if executing on a different session.
If you want to execute the query from another session, then you need to set the new session isolation level to read uncommitted (Syntax: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT t1.resource_type , t1.resource_database_id , t1.resource_associated_entity_id , t1.request_mode , t1.request_session_id , o1.name 'object name' , o1.type_desc 'object descr' FROM sys.dm_tran_locks as t1 LEFT OUTER JOIN sys.objects o1 ON o1.object_id = t1.resource_associated_entity_id LEFT OUTER JOIN sys.partitions p1 ON p1.hobt_id = t1.resource_associated_entity_id LEFT OUTER JOIN sys.allocation_units a1 ON a1.allocation_unit_id = t1.resource_associated_entity_id WHERE t1.request_mode IN ('IX', 'X') |
Locking and Blockings
We will now perform a simple operation in SQL Server Management Studio (SSMS), checking the database properties of tempdb.
Typically the Database Properties form would show immediately. But in our scenario, SSMS would be in a “busy” state and not responding.
So, we will launch a new SSMS and execute the query below to check for blockings.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT command , r.status , blocking_session_id , wait_type, wait_resource , [program_name], [text] FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s on r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id <> @@SPID |
The output reveals that we have a blocking session id 58 which is executing the SELECT INTO query. The waitresource 2:327680 is system table sysrowsets in tempdb.
The Database Properties form is unable to launch because the query which calculates the database size is blocked. We might not be interested in checking tempdb database size, but this is one of the side effect when row(s) in system table are locked exclusively.
If you wait long enough, the error message below will appear.
TempDB is Special
Let’s try with DBCC CHECKDB on tempdb.
|
1 2 3 4 5 |
USE master GO DBCC CHECKDB(tempdb) |
The database integrity checks command comes back with an error because it is unable to obtain a lock on system table sys.sysrscols. This does not indicate a database corruption, although it gives the perception of something seems wrong.
If the SELECT INTO statement is operated into a permanent table in the context of a user database, the database integrity check will execute successfully on the user database because user database would automatically create an internal hidden database snapshot by default when CHECKDB is performed. Integrity checks on tempdb behavior is different from a user database. Tempdb does not and cannot create a database snapshot for database integrity checks and hence it needs to acquire shared table locks on the system tables to perform table checks. When rows in system tables are exclusively locked, it produces an error and fails the CHECKDB.
Commit Transaction re-run CHECKDB on TempDB
We now commit the explicit transaction and execute database integrity checks again. The command will execute successfully and the output would indicate the system tables that CHECKDB requires to access in tempdb.
Common Scenario in Deployment Utilities
There are deployment utilities, which build objects by performing a series of DDL statements within a database. Some objects might take longer to be created and you might encounter a lock request timeout. For instance, if a CREATE PROCEDURE takes a long time to complete, will cause timeout when you try to expand the “Stored Procedures” nodes from SSMS.
Conclusion
DDL (including SELECT INTO) statements containing long-running explicit transactions, should be avoided if possible because it creates exclusive locks on row(s) in system tables for the duration of the transaction.
This might not cause detrimental effects, but often it misleads DBAs to think there was a network connectivity or SQL Server resource issue. If the code is not well written, you may encounter lock request timeouts when performing typical administrative tasks such as checking database properties from SSMS. In actuality, you are just experiencing typical locks and blocks on row(s) in system tables.
Simon has over 15+ years of database design, implementation, administration and development in SQL Server. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing. Achieving Microsoft masters-level certifications validate the deepest level of product expertise, as well as the ability to design and build the most innovative solutions for complex on-premises, off-premises, and hybrid enterprise environments using Microsoft technologies.
View all posts by Simon Liew