Handling locks, blocking, and deadlocks (1)--Determining long-running transactions

Preface:

Transactions are a major part of an OLTP system. It manages data consistency and data concurrency issues, and when multiple resources are read or modified with the same data, SQL Server uses a locking mechanism to ensure that the data in the database is always in a valid state. In SQL Server, the lock manager is responsible for implementing these locking mechanisms. SQL Server provides different types of locks for different resource types, such as databases, files, objects, tables, extents, pages, and keys.

When you use transactions, you still encounter problems caused by transactions, which are usually caused by locks, blocking, and deadlocks.

This series will explain the three parts of the concept.

to determine long-running transactions:

Long-running transactions can block other transactions and trigger a new round of long-running transactions. This will severely affect the performance of the database server.

As a DBA, you need to constantly monitor the server's transactions, and when you find a long-running transaction, you need to use the necessary steps to correct it. This article will explain how to monitor these transactions through the duration of a transaction, and if you frequently find transactions that last a long time, you may want to find out if you are blocked by other transactions, or if there is a problem with the statement that delves into the transaction.

preparatory work:

This article uses the SQLSERVER2012 sample database AdventureWorks2012 database.

steps:

1, open SQL Server, connect to the ADVENTUREWORKS2012 database.

2, enter the following script to enable it to open a simple transaction:

[SQL] view plain copy print? Use AdventureWorks2012 go BEGIN TRANSACTION SELECT * from Sales.salesorder Header

Use AdventureWorks2012
go
BEGIN TRANSACTION
SELECT  *
from    Sales.SalesOrderHeader

3. Do not close the window, enter the following code in the new window to monitor the currently running transaction:[SQL]View Plain copy print?SELECTst.transaction_id asTransactionID, db_name (dt.database_id) asDatabaseName, at. transaction_begin_time asTransactionstarttime, DATEDIFF (SECOND, at. Transaction_begin_time, GETDATE ()) asTransactionduration, case. at. transaction_type when1THEN' Read/write Transaction ' when2THEN' Read-only Transaction ' when3THEN' System Transaction ' when4THEN' Distributed Transaction ' End asTransactiontype, case. at. transaction_state when0THEN' Transaction not initialized ' when1THEN' Transaction initialized & not started ' when2THEN' Active Transaction ' when3THEN' Transaction Ended ' when4THEN' Distributed Transaction initiated Commit Process ' when5THEN' Transaction in Prepared State & waiting Resolution ' when6THEN' Transaction committed ' when7THEN' Transaction rolling back ' when8THEN' Transaction rolled back ' End asTransactionstate fromSys.dm_tran_session_transactions asStINNERJOIN sys.dm_tran_active_transactions as at onst.transaction_id = at. transaction_idINNERJOIN sys.dm_tran_database_transactions asDt onst.transaction_id = dt.transaction_id Order byTransactionstarttime Go

SELECT st.transaction_id as TransactionID, Db_name (dt.database_id) as DatabaseName, At.transaction_begi
        N_time as Transactionstarttime, DATEDIFF (SECOND, At.transaction_begin_time, GETDATE ()) as Transactionduration, Case At.transaction_type then 1 THEN ' read/write transaction ' when 2 THEN ' Read-only transactio N ' when 3 THEN ' System Transaction ' if 4 THEN ' distributed Transaction ' end as Transactionty PE, Case at.transaction_state if 0 THEN ' transaction not initialized ' when 1 THEN ' Transact
          Ion initialized & not started ' When 2 THEN ' Active Transaction ' when 3 THEN ' Transaction Ended ' When 4 THEN ' distributed Transaction initiated Commit Process ' when 5 THEN ' Transaction in Prepared Stat 
          E & Waiting Resolution ' When 6 THEN ' Transaction committed ' if 7 THEN ' Transaction rolling back ' When 8 THEN 'Transaction rolled back ' end as transactionstate from Sys.dm_tran_session_transactions as ST INNER JOIN Sys.dm_tran_active_transactions as on st.transaction_id = at.transaction_id INNER JOIN sys.dm_tran_database_tr
 Ansactions as DT on st.transaction_id = dt.transaction_id from Transactionstarttime go

4, the following is a screenshot of the results:

5. Now to close the transaction, enter in the first window: [SQL] view plain copy print? ROLLBACK   TRANSACTION Go

ROLLBACK TRANSACTION Go

Analysis:

In the example above, you open a window and then create a new query. In another window, you are querying for a transaction that is currently running.

The following 3 DMV are used in this example:

1. Sys.dm_tran_session_transactions: Provides view-related information and contains information about a particular session.

2, Sys.dm_tran_active_transactions: Returns the transaction information that is active at the instance level.

3. Sys.dm_tran_database_transactions: Returns transaction information at the database level.

The example uses Db_name () to return to the current database as a filter for transaction information on a particular database.