SqlServer数据库常用死锁问题定位语句
作者:快盘下载 人气:SqlServer数据库常用问题定位语句
--1.查找未提交事务
SELECT
session_id,
transaction_id,
is_user_transaction,
is_local
FROM
sys.dm_tran_session_transactions
WHERE
is_user_transaction = 1;
--2.根据返回的ID查询详细信息
SELECT
*
FROM
sys.dm_exec_connections c CROSS apply sys.dm_exec_sql_text ( c.most_recent_sql_Handle ) s
WHERE
session_id = 63;
SELECT
p.session_id,
p.request_id,
p.start_time,--查找阻塞的语句;正在运行;
p.status,
p.command,
p.blocking_session_id,
p.wait_type,
p.wait_time,
p.wait_resource,
p.total_elapsed_time,
p.open_transaction_count,
p.transaction_isolation_level,
SUBSTRING (
qt.text,
p.statement_start_offset/ 2,
(
CASE
WHEN p.statement_end_offset=- 1 THEN
len( CONVERT ( nvarchar ( MAX ), qt.text ) ) * 2 ELSE p.statement_end_offset
END - statement_start_offset
) / 2
) AS ;SQL statement;,
p.statement_start_offset,
p.statement_end_offset,
batch = qt.text
FROM
master.sys.dm_exec_requests p CROSS apply sys.dm_exec_sql_text ( p.sql_handle ) AS qt
WHERE
p.session_id> 50
--查看产生死锁的表
SELECT
request_session_id spid,
OBJECT_NAME( resource_associated_entity_id ) tableName
FROM
sys.dm_tran_locks
WHERE
resource_type = ;OBJECT;;
--“杀死”死锁进程
kill spid;
加载全部内容