sqlserver修改事务隔离级别、死锁查询

1.查看当前事务隔离级别

-- 查看当前事务隔离级别
DBCC Useroptions;

2.设置当前事务隔离级别为xxx

 -- 设置事务隔离级别为xxx
set transaction isolation level <隔离级别>; --xxx可以为 Read Uncommitted、read committed、Repeatable read、Serializable
sqlserver貌似只能更改当前连接的，不能全局改。

--xxx可以为 Read Uncommitted、read committed、Repeatable read、Serializable

3.查询锁表

-- 查询锁表
select 
    request_session_id spid,
    OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks 
where resource_type='OBJECT'; 

4.查询死锁语句

-- 查询死锁语句
select 
    er.session_id, 
    CAST(csql.text AS varchar(max)) AS CallingSQL
from master.sys.dm_exec_requests er WITH (NOLOCK) CROSS APPLY fn_get_sql (er.sql_handle) csql
where er.session_id in (select request_session_id from sys.dm_tran_locks where resource_type = 'OBJECT');

5.显示死锁相关信息

-- 显示死锁相关信息
exec sp_who2 <进程编号>;

6.杀掉（死锁）进程

-- 杀掉（死锁）进程
kill <进程编号>;