16th-handling locks, blockages, and deadlocks (1)-Determining long-running transactions

Original: 16th-handling locks, blockages, and deadlocks (1)-Determining long-running transactions

Objective:

Transactions are a major part of an OLTP system. It manages data consistency and data concurrency issues, and SQL Server uses a locking mechanism to ensure that the data in the database is always in a valid state when multiple resources are read or modified at the same time. 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, blockages, and deadlocks.

This series will explain the concepts of these three parts.

Determine long-running transactions:

Long-running transactions 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 monitor the server's transactions frequently, and when you find a long-running transaction, you need to use the necessary steps to correct it. This article will talk about monitoring these transactions through the duration of the transaction, and if it is often found that the transaction lasts for a long time, you may need to find out if there is a problem with other transactions, or if you are investigating the statement of the transaction.

Preparatory work:

This article uses the SQLSERVER2012 sample database AdventureWorks2012 database.

Steps:

1. Open SQL Server and connect to the ADVENTUREWORKS2012 database.

2. Enter the following script to enable it to open a simple transaction:

Use Adventureworks2012gobegin transactionselect  *from    Sales.SalesOrderHeader

3. Do not close the window, enter the following code in a new window to monitor the currently running transaction:

SELECT st.transaction_id as TransactionID, Db_name (dt.database_id) as DatabaseName, At.transaction_begin_        Time as Transactionstarttime, DATEDIFF (SECOND, At.transaction_begin_time, GETDATE ()) as Transactionduration,          Case At.transaction_type If 1 Then ' Read/write transaction ' when 2 Then ' read-only transaction '        When the 3 then ' System Transaction ' was 4 Then ' distributed Transaction ' END as Transactiontype, Case At.transaction_state If 0 Then ' transaction not Initialized ' when 1 Then ' transaction Initiali Zed & Not Started ' When 2 Then ' Active Transaction ' when 3 Then ' Transaction Ended ' when 4 Then ' distributed Transaction initiated Commit Process ' if 5 Then ' Transaction in Prepared State & Waiting Resolution ' when 6 and ' Transaction Committed ' when 7 then ' Transaction rolling back ' when 8 T HEN ' Transaction rolledBack ' END as Transactionstatefrom sys.dm_tran_session_transactions as ST INNER JOIN sys.dm_tran_active_tr Ansactions as at the st.transaction_id = at.transaction_id INNER JOIN sys.dm_tran_database_transactions as DT on ST. transaction_id = Dt.transaction_idorder by Transactionstarttimego

4, the following is the result:

5. Now to close the transaction, enter in the first window:

ROLLBACK Transactiongo

Analysis:

The above example opens a window and creates a new query. In another window, the currently running transaction is queried.

The following 3 DMV are used in this example:

1. Sys.dm_tran_session_transactions: Provides view-related information and contains information for a specific 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 the current database as a filter for transaction information on a particular database.

16th-handling locks, blockages, and deadlocks (1)-Determining long-running transactions