Dealing with Locking, Blocking, and Deadlocking - Microsoft SQL Server 2012 Performance Tuning - page 378

5. Execute the following statement in the first query window(Connection-1), to roll back
the transaction initiated previously:
--Rolling Back sample transaction
ROLLBACK TRANSACTION
GO
How it works...
We first connected to the SQL Server instance that is hosting the AdventureWorks2012
database. In a query window (Connection-1), we started a new transaction in which we
queried data from the table Sales.SalesOrderHeader .
In another query window (Connection-2), we executed a query to see all currently
running transactions. In this query we used the following transaction-related dynamic
management views:
F sys.dm_tran_session_transactions, which provides transaction-related information
along with some session specific information
F sys.dm_tran_active_transactions, which provides information on all transactions
currently active at instance level
F sys.dm_tran_database_transactions,which provides information on transactions
that are database-specific
All these DMVs are joined to the transaction_id column. To know with which database
a particular transaction is associated, we used the DB_NAME() function by passing the
database_id column.
The transaction_begin_time column specifies the time when the transaction was
started. By using DATEDIFF() function on this column, we calculated the number of
seconds for which a particular transaction has been running.
The other columns denote the types and states of the transaction.
Finally, we sorted the output of the query based on TransactionStartTime , so that we get
the oldest and longest-running transactions at the top of the list.
Detecting blocked and blocking queries
If a transaction is waiting for some resources because the same resources are locked by
other transactions, that transaction is considered a blocked transaction. On the contrary, a
transaction that has locked the resources and caused other transactions to wait is considered
a blocking transaction.