SQL Server Dead Lock Log

1 . 模拟Dead Lock

Session1:

begintran

insertintoT1(name)values('test1')

UpdateT2setname='test1'

commit

Session2:

begintran

insertintoT2(name)values('test2')

UpdateT1setname='test2'

commit

交叉每个session执行一句，当session2执行update T1语句时候，会提示deadlocked错误,并且选择这个session作为牺牲者，该transaction已经rollback

2.查看Dead Lock Log
EXEC master.dbo.sp_readerrorlog

发现没有记录以上dead lock情况，使用下面命令

DBCC TRACEON(1222,-1)

DBCC TRACEOFF(1204,-1)

此后再次试验，里面就会有类似记录，好吧，其实看不太懂

3.查看当前没有commit的transaction

select *fromsys.dm_tran_active_transactions
或者

SELECT
    trans.session_id AS [SESSION ID],
    ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
    trans.transaction_id AS [TRANSACTION ID],
    tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION BEGIN TIME],
    tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

还有

SELECT dt.transaction_id,
st.session_id,
database_transaction_begin_time,
CASE database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END database_transaction_type,
CASE database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log recorst.'
WHEN 4 THEN 'The transaction has generated log recorst.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
END database_transaction_state,
database_transaction_log_bytes_used,
database_transaction_log_bytes_reserved
FROM   sys.dm_tran_database_transactions dt
INNER JOIN sys.dm_tran_session_transactions st
ON st.transaction_id = dt.transaction_id

查看Lock的信息，不过里面的SqlStatementText不是当时执行的语句，而只是那个session的最后一条执行语句

  SELECT  L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS DatabaseName,L.resource_database_id,
    O.Name AS LockedObjectName, 
   P.object_id AS LockedObjectId, 
    L.resource_type AS LockedResource, 
    L.request_mode AS LockType ,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
LEFT JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
LEFT JOIN sys.objects O ON O.object_id = P.object_id
 LEFT JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
 LEFT JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
 LEFT JOIN  sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
 LEFT JOIN  sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE -- DB_NAME(db_id())='TestDb'
resource_database_id = db_id()
ORDER BY L.request_session_id

当前

总体说来，这些Sql语句提供的信息似乎并不准确，是我打开的方式不对么。。。有种白费功夫的感觉，无语啊

当前session id

select @@spid

当前db id

selectdb_id()

当前db name

select DB_NAME(db_id())
sp_who2

will tell you which processes are blocking which

怎么捕获和记录SQL Server中发生的死锁?
http://blog.csdn.net/coleling/article/details/6086680

Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204
http://www.mytechmantra.com/LearnSQLServer/Identify-Deadlocks-Using-Graphical-Deadlock-Chain-Event-in-SQL-Server-Profiler/