16th chapter-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:

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:

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, in the first window, enter:

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.