Lookup in SQL Server for a long time uncommitted transaction

Whether intentionally or unintentionally, if a transaction remains open in the database, it blocks other processes from manipulating the modified data. Similarly, backing up the transaction log only truncates the part of the transaction log for inactive transactions, so open transactions can cause more logs (or even physical limitations) until the transaction is committed or rolled back.

To find the oldest active transaction, you can use the DBCC OPENTRAN command.

Give an example:

The code is as follows:

CREATE TABLE t_product (pkid int, pname Nvarchar (50));

Go

BEGIN TRAN

INSERT into T_product VALUES (101, ' Chang ' e Fourth ');

Go

DBCC Opentran;

ROLLBACK TRAN;

Go

DROP TABLE t_product;

Go

Execution results:

The code is as follows:

/*

(1 row (s) affected)

Transaction information for the database ' TestDB '.

The oldest active transaction:

SPID (server process ID): 54

UID (User ID):-1

Name: user_transaction

LSN: (295:6687:1)

Start time: 2:50:15:607pm

sid:0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

*/

The results show information about the oldest activity log, including the server process ID, the user ID, and the start time of the transaction. The key is the SPID and start time.

Once you have this information, you can use the dynamic management view (DMV) to verify the T-SQL that is being executed, and to close the procedure if necessary

DBCC Opentran are useful for orphaned connections (which are open in the database but are disconnected from applications or clients) and can help us to identify transactions that have been omitted from commit or rollback. This command also returns the oldest active transaction and the oldest distributed and distributed replication transaction in the specified database. If there is no active transaction, an informational message is displayed, and no session-level data is returned.

Let's look at an example:

The code is as follows:

SET Transaction Isolation Level serializable

BEGIN TRAN

SELECT * FROM T_product

Insert into T_product

Select ' Oatest ' UNION ALL

Select ' Oaplay '

This is an uncommitted transaction and is executed in another query window as follows:

The code is as follows:

Select Session_id,transaction_id,is_user_transaction,is_local

From Sys.dm_tran_session_transactions

where is_user_transaction=1

Execution results:

The code is as follows:

/* Return results

session_id transaction_id is_user_transaction is_local

54 489743 1 1

*/

After the session ID is returned, the details of the most recently executed query can be mined through sys.dm_exec_connections and sys.dm_exec_sql_text.

The code is as follows:

Select S.text from sys.dm_exec_connections C

Cross apply Sys.dm_exec_sql_text (C.most_recent_sql_handle) s

where session_id=54

This query returns the last executed statement. You can also use sys.dm_exec_requests.

Because the transaction ID is also known from the first query of sys.dm_tran_session_transactions, you can use Sys.dm_tran_active_transactions to learn more about the transaction itself

The code is as follows:

Select Transaction_begin_time,

Case Transaction_type

When 1 Then ' Read/write transaction '

When 2 Then ' read-only transaction '

When 3 Then ' System transaction '

When 4 Then ' Distributed transaction '

End Tran_type,

Case Transaction_state

When 0 Then ' not been comoletely initaialiaed yet '

When 1 Then ' initaialiaed but Ha notstarted '

When 2 Then ' active '

When 3 Then ' ended (read-only transaction) '

When 4 Then ' commit initiated for distributed transaction '

When 5 Then ' transaction prepared and waiting resolution '

When 6 then ' commited '

When 7 then ' being rolled '

When 0 Then ' been rolled '

End Transaction_state

From

Sys.dm_tran_active_transactions

where transaction_id=455520

The code is as follows:

/* Results:

Transaction_begin_time Tran_type Transaction_state

2010-12-24 14:05:29.170 read/write Transaction Active

*/

Summary: Here is a demonstration of general techniques for using the DMV to troubleshoot and investigate Long-running activity transactions. The basic steps are as follows:

1, query sys.dm_tran_session_transactions get the mapping between the session ID and the transaction ID.

2, Query sys.dm_exec_connections and Sys.dm_exec_sql_text find session the most recently executed command (most_recent_sql_handle column)

3. Finally, the query sys.dm_tran_active_transactions determines how much time the transaction was opened, the type of transaction, and the state of the transaction.

Use this technique to go back to the application to find out about the discarded transactions that were invoked (open but never committed) and those that were running too long or unnecessary for the application.