SQL Server: Transaction Management

Managing SQL Server transactions is an important step toward ensuring smooth operations and avoiding locking and blocking errors.

Excerpted from “SQL Server DMV Starter Pack,” published by Red Gate Books (2010).

Glenn Berry, Louis Davidson and Tim Ford

Looking into granular levels of managing SQL Server operations, you have to carefully consider how you manage transaction-related Dynamic Management Objects (DMOs). All of the Dynamic Management Views (DMVs) in the “transaction related” category of DMOs begin with “sys.dm_tran_” as an opening statement.

Ultimately, every statement executed against SQL Server is transactional. If you issue a single SQL statement, an implicit transaction is started under the covers. This statement will start and auto-complete. If you use explicit BEGIN TRAN / COMMIT TRAN commands, you can group these together as an explicit transaction—a set of statements that must fail or succeed together.

SQL Server implements various transaction isolation levels, to ensure the Atomicity, Consistency, Isolation and Durability (ACID) properties of these transactions. In practical terms, this means that it uses locks and latches to mediate transactional access to shared database resources and prevent “interference” between the transactions.

Generally speaking, your strategy and processes for investigating and managing SQL Server transactions will be limited to a few key questions:

  • What transactions are active, and what sessions are running them? (session_transactions, active_transactions)
  • What transactions are doing the most work? (database_transactions)
  • Which transactions are causing locking/blocking problems? (locks)

Of these reasons, investigating locking and blocking is by far the most common use of these DMVs. An area of investigation that will continue to become increasingly common is into the activity generated when using the SNAPSHOT isolation level. The snapshot isolation level was introduced in SQL Server 2005. Snapshot isolation eliminates blocking and deadlocking by using a version store in the tempdb database to maintain concurrency, rather than establishing locks on database objects. There are a number of DMVs provided to investigate this isolation level.

Monitor Long-running Transactions

Let’s now move on to the scripts. Unless stated otherwise, all of these queries work with SQL Server 2005, 2008 and 2008 R2, and all require VIEW SERVER STATE permission. This script uses two DMVs. The first is sys.dm_tran_database_transactions, which is described in SQL Server books online (BOL) as follows: “Returns information about transactions at the database level.”

The second is sys.dm_tran_session_transactions, which simply: “Returns correlation information for associated transactions and sessions.”

The terse description given for database_transactions rather belies its potential usefulness. The following script provides a query that shows, per session, which databases are in use by a transaction open by that session, whether the transaction has upgraded to read-write in any of the databases (by default most transactions are read-only), when the transaction upgraded to read-write for that database, how many log records written, and how many bytes were used on behalf of those log records:

SELECT st.session_id , DB_NAME(dt.database_id) AS database_name , CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'

ELSE 'read-write' END AS transaction_state , dt.database_transaction_begin_time AS read_write_start_time , dt.database_transaction_log_record_count , dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions AS st INNER JOIN sys.dm_tran_database_transactions AS dt

ON st.transaction_id = dt.transaction_idORDER BY st.session_id , database_name

These sorts of queries against database_transactions are very useful when monitoring things such as:

  • Sessions with open read-write transactions (especially important for sleeping sessions)
  • Sessions causing the transaction log to grow/bloat
  • The progress of long-running transactions (for non-bulk logged operations, each affected index row will produce approximately one transaction log record)

Locking and Blocking

Our example script for the transaction-related category of DMVs uses the sys.dm_tran_locks DMV, which is described by BOL as follows:

“Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.”

This DMV is useful in helping to identify locking and blocking issues on your database instances:

-- Look at active Lock Manager resources for current database

SELECT request_session_id ,

DB_NAME(resource_database_id) AS [Database] , resource_type , resource_subtype , request_type , request_mode , resource_description , request_mode , request_owner_type

FROM sys.dm_tran_locksWHERE request_session_id > 50 AND resource_database_id = DB_ID() AND request_session_id <> @@SPIDORDER BY request_session_id ;

-- Look for blocking

SELECT tl.resource_type , tl.resource_database_id , tl.resource_associated_entity_id , tl.request_mode , tl.request_session_id , wt.blocking_session_id , wt.wait_type , wt.wait_duration_msFROM sys.dm_tran_locks AS tl INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC ;

The first query shows lock types and their status by SPID, filtered by the current database, and eliminating the current connection and the system SPIDs. The second query provides information regarding any blocking that might be occurring, instance-wide. Notice that this second query joins to the sys.dm_os_waiting_tasks DMV to obtain data on the length of time a process has been waiting, due to blocking, and on which resource.

Unless you have severe blocking issues, you'll typically need to run each of these queries multiple times to catch blocking. If you do identify two data modification statements, or a query and a data modification, which are embracing in severe blocking, or even deadlocks, then you’ll need to extract the SQL text for the queries, examine them, run them on a test system—with Profiler tracing running—and work out a way to tune the queries, or add indexes, to alleviate the problem.

Glenn Berry

Glenn Berry works as a database architect at NewsGator Technologies in Denver, Colo. He’s a SQL Server MVP, and has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD and MCTS, which proves that he likes to take tests.

Louis Davidson

Louis Davidson has been in the IT industry for 16 years as a corporate database developer and architect. He has been a SQL Server Microsoft MVP for six years and has written four books on database design. Currently he’s the data architect and sometimes DBA for the Christian Broadcasting Network, supporting offices in Virginia Beach, Va., and Nashville, Tenn.

Timothy Ford

Timothy Ford is a SQL Server MVP and has been working with SQL Server for more than 10 years. He’s the primary DBA and subject-matter expert for the SQL Server platform for Spectrum Health. He’s been writing about technology since 2007 for a variety of Web sites and maintains his own blog at thesqlagentman.com, covering SQL as well as telecommuting and professional development topics.

Learn more about “SQL Server DMV Starter Pack” at red-gate.com/our-company/about/book-store.