SQL Server查询事务

2018年07月08日 19:25:00 weixin_33989058 阅读数 108

无论是有意无意，如果事务在数据库中保持打开，则它会阻塞其他进程对修改后的数据进行操作（ACID特性）。同样，长时间运行的事务也可能导致日志填满。长时间运行的事务可使事务日志从包含事务的第一条日志记录的虚拟日志文件开始，便一直保持活动状态，以打开的事务会导致日志变多（甚至达到物理限制），直到事务被提交或回滚。

（1）通过DBCC OPENTRAN查询事务

1.1 DBCC OPENTRAN简介

通过DBCC OPENTRAN语句，您可以标识该事务所有者的用户 ID，因此可以隐性地跟踪该事务的源以得到更加有序的终止（将其提交而非回滚）。该命令也返回在指定数据库内存在最早的活动事务和最早的分布式和非分布式复制事务。如果没有活动事务，则显示信息性消息，而不返回会话级数据。
DBCC OPENTRAN对于孤立连接（在数据库中是打开的，但与应用程序或客户端已经断开的连接）是非常有用的，并能帮助我们找出遗漏了COMMIT或ROLLBACK的事务。

1.2 DBCC OPENTRAN使用示例

DBCC OPENTRAN语法

DBCC OPENTRAN
[
( [ database_name | database_id | 0 ] ) ]
{ [ WITH TABLERESULTS ]
[ , [ NO_INFOMSGS ] ]
}
]

database_name | database_id| 0：Is the name or ID of the database for which to display the oldest transaction information. If not specified, or if 0 is specified, the current database is used.
TABLERESULTS：Specifies the results in a tabular format that can be loaded into a table. Use this option to create a table of results that can be inserted into a table for comparisons.
NO_INFOMSGS：Suppresses all informational messages.

使用示例

A. Returning the oldest active transaction

CREATE TABLE T1(Col1 int, Col2 char(3));
GO
BEGIN TRAN
INSERT INTO T1 VALUES (101, 'abc');
GO
DBCC OPENTRAN;
ROLLBACK TRAN;
GO
DROP TABLE T1;
GO

【执行结果】
结果显示了最早活动日志的相关信息，包括服务器进程ID（SPID）、用户ID（UID）、和事务的开始时间，关键是SPID和Start Time。

B. Specifying the WITH TABLERESULTS option
The following example loads the results of the DBCC OPENTRAN command into a temporary table.

-- Create the temporary table to accept the results.
CREATE TABLE #OpenTranStatus (
ActiveTransaction varchar(25),
Details sql_variant
);
-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');
-- Display the results.
SELECT * FROM #OpenTranStatus;
GO

（2）通过DMV（动态管理视图）查询事务

1、 sys.dm_tran_session_transactions：提供视图相关的信息，并包含了特定会话的信息。

2、 sys.dm_tran_active_transactions：返回实例级别上，所以正在活动的事务信息。
3、 sys.dm_tran_database_transactions：返回数据库级别上的事务信息。

对于长时间运行的事务，最需要注意的列包括：第一条日志记录的时间、事务的当前状态 (database_transaction_state) 和事务日志中开始记录的日志序列号 (LSN) 。

实例、数据库和会话

数据库是属于某个实例的，就是说一个实例包含多个数据库。
会话：用户通过用户进程与SQL Server实例建立的连接[此处连接主要指用户和数据库间的联系 ]。例如，当用户启动SQL Server时必须提供有效的用户名和密码，之后SQL Server为此用户建立一个会话。从用户开始连接到用户断开连接（或退出数据库应用程序）期间，会话一直持续。

示例

SET Transaction isolation level serializable
BEGIN TRAN
select * from T_Product
Insert into T_Product select 'OATest' union all select 'OAPlay'

这是一个未提交的事务,在另一个查询窗口执行如下：

select session_id,transaction_id,is_user_transaction,is_local
from sys.dm_tran_session_transactions
where is_user_transaction=1 --

执行结果：

session_id transaction_id is_user_transaction is_local
54 489743 1 1

返回会话ID（session_id）后，可以通过sys.dm_exec_connections和sys.dm_exec_sql_text来挖掘最近执行的查询的详细信息：

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

这个查询返回最后执行的语句。
因为也从sys.dm_tran_session_transactions的第一个查询中得知事务ID，所以可以使用sys.dm_tran_active_transactions来了解更多事务本身的内容。

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 back'
when 0 then 'been rolled back'
end transaction_state
from
sys.dm_tran_active_transactions
where transaction_ID=455520

执行结果：

transaction_begin_time tran_Type transaction_state
2010-12-24 14:05:29.170 Read/Write transaction active