最近在分析sql server的死锁时，发现一个比较有意思的现象，发现死锁当中一个会话的隔离级别为序列化（serializable），这个是让人比较奇怪的地方，我们知道sql server数据库的默认隔离级别为已提交读（read committed），除非人为设置事务隔离级别(transaction isolation level)，否则事务隔离级别会使用数据库的默认隔离级别。在分析了死锁相关的存储过程后，没有发现有人为修改事务隔离级别的地方。在分析过后，我们判断应该是在应用程序代码里面有设置隔离级别，下面我们通过一个小实验来构造这样的一个案例。
测试环境数据库为adventureworks2014，如下所示，我简单写了一点c#代码，截取黏贴部分c#代码在此，在这段代码中，我们使用transactionscope，我们先更新sales.salesorderdetail,然后查询 [sales].[salesorderheader]的相关数据来绑定grid控件
try
{
using (transactionscope scope = new transactionscope())
{
using (sqlconnection conn = new sqlconnection(connstring))
{
string cmdtext = "update sales.salesorderdetail set orderqty=2 where salesorderid=43659 and salesorderdetailid=1;";
sqlcommand cmd = new sqlcommand(cmdtext, conn);
conn.open();
cmd.executenonquery();
}
using (sqlconnection conn = new sqlconnection(connstring))
{
dataset sqldataset = new dataset();
string cmdtext = "select * from [sales].[salesorderheader] where salesorderid=43659;";
sqlcommand cmd = new sqlcommand(cmdtext, conn);
sqldataadapter sqladapter = new sqldataadapter(cmdtext, conn);
sqladapter.fill(sqldataset, "spt_values");
gvdata.datasource = sqldataset;
gvdata.databind();
}
scope.complete();
}
}
catch (transactionabortedexception exc)
{
log.error("错误", exc);
}
然后另外一个会话，就直接用ssms开启一个事务（懒得构造c#代码案例，主要是太浪费时间了），主要执行下面逻辑：
begin tran
update [sales].[salesorderheader] set subtotal = subtotal + 10
where salesorderid=43659;
waitfor delay '00:00:10';
select top 10 * from sales.salesorderdetail
--rollback tran;
执行上面sql语句，然后运行最上面c#代码，立马就能构造出一个死锁案例，如下截图所示，测试环境为sql server 2014，我就使用扩展事件system_health捕获的死锁（当然，你可以使用任何方式，例如profile或trace捕获死锁相关信息），使用sql将死锁的xml信息查出
如下所示，你会看到使用transactionscope的会话的隔离级别为isolationlevel=”serializable (4)”， 具体可以参考下面死锁的xml文件。
<deadlock>
<victim-list>
<victimprocess id="process17676e108" />
</victim-list>
<process-list>
<process id="process17676e108" taskpriority="0" logused="384" waitresource="key: 7:72057594048479232 (0ca7b7436f59)" waittime="379" ownerid="46635671" transactionname="user_transaction" lasttranstarted="2019-04-02t23:26:21.150" xdes="0x17f0511f0" lockmode="s" schedulerid="1" kpid="13440" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-04-02t23:26:21.147" lastbatchcompleted="2019-04-02t23:26:09.343" lastattention="1900-01-01t00:00:00.343" clientapp="microsoft sql server management studio - query" hostname="mynb00021" hostpid="9728" loginname="test" isolationlevel="read committed (2)" xactid="46635671" currentdb="7" locktimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionstack>
<frame procname="adhoc" line="8" stmtstart="282" stmtend="368" sqlhandle="0x020000002a285923f5e38f7347b53337195c56a4a1bc33080000000000000000000000000000000000000000">
unknown </frame>
</executionstack>
<inputbuf>
begin tran
update [sales].[salesorderheader] set subtotal = subtotal + 10
where salesorderid=43659;
waitfor delay '00:00:10';
select top 10 * from sales.salesorderdetail </inputbuf>
</process>
<process id="process175603c28" taskpriority="0" logused="436" waitresource="key: 7:72057594048544768 (6a8a6db47ef5)" waittime="4420" ownerid="46635065" transactionname="user_transaction" lasttranstarted="2019-04-02t23:25:36.807" xdes="0x1762fa9f0" lockmode="s" schedulerid="1" kpid="51760" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-02t23:26:26.450" lastbatchcompleted="2019-04-02t23:25:36.807" lastattention="1900-01-01t00:00:00.807" clientapp=".net sqlclient data provider" hostname="mynb00021" hostpid="1700" loginname="kkk" isolationlevel="serializable (4)" xactid="46635065" currentdb="7" locktimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionstack>
<frame procname="adventureworks2014.sales.idusalesorderdetail" line="18" stmtstart="982" stmtend="2448" sqlhandle="0x0300070076146e6c18e00a016ba3000000000000000000000000000000000000000000000000000000000000">
insert into [production].[transactionhistory]
([productid]
,[referenceorderid]
,[referenceorderlineid]
,[transactiontype]
,[transactiondate]
,[quantity]
,[actualcost])
select
inserted.[productid]
,inserted.[salesorderid]
,inserted.[salesorderdetailid]
,'s'
,getdate()
,inserted.[orderqty]
,inserted.[unitprice]
from inserted
inner join [sales].[salesorderheader]
on inserted.[salesorderid] = [sales].[salesorderheader].[salesorderid </frame>
<frame procname="adhoc" line="1" stmtstart="52" stmtend="262" sqlhandle="0x02000000abf4ee0ff24fea415c6f35709c721203030a173b0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="1" stmtend="186" sqlhandle="0x02000000b0cd40243d43ed1a51b1baa9cbf70d1628eae7880000000000000000000000000000000000000000">
unknown </frame>
</executionstack>
<inputbuf>
update sales.salesorderdetail set orderqty=2 where salesorderid=43659 and salesorderdetailid=1; </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594048479232" dbid="7" objectname="adventureworks2014.sales.salesorderdetail" indexname="pk_salesorderdetail_salesorderid_salesorderdetailid_old" id="lock154ffb300" mode="x" associatedobjectid="72057594048479232">
<owner-list>
<owner id="process175603c28" mode="x" />
</owner-list>
<waiter-list>
<waiter id="process17676e108" mode="s" requesttype="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594048544768" dbid="7" objectname="adventureworks2014.sales.salesorderheader" indexname="pk_salesorderheader_salesorderid" id="lock155a8fa00" mode="x" associatedobjectid="72057594048544768">
<owner-list>
<owner id="process17676e108" mode="x" />
</owner-list>
<waiter-list>
<waiter id="process175603c28" mode="s" requesttype="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
我们也可以使用下面sql语句来捕获会话的的隔离级别（根据实际情况调整）， 在运行上面c#代码期间捕获会话信息，如下截图所示：
declare @end_time datetime;
set @end_time = dateadd(second, 10, getdate());
while getdate() < @end_time
begin
insert into mintor_isolcation_level
select session_id ,
start_time ,
status ,
total_elapsed_time ,
case transaction_isolation_level
when 1 then 'readuncomitted'
when 2 then 'readcommitted'
when 3 then 'repeatable'
when 4 then 'serializable'
when 5 then 'snapshot'
else 'unspecified'
end as transaction_isolation_level ,
sh.text ,
ph.query_plan
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle) sh
cross apply sys.dm_exec_query_plan(plan_handle) ph
end
因为上面的脚本执行时间太短，所以有可能捕获到的是相关sql运行期间的触发器脚本。如果要清晰的捕获相关sql，可以构造一个执行时间较长的sql
是否有点意外，其实官方文档已有详细介绍（详见参考资料），摘抄部分信息如下，transactionscope如果不指定隔离级别，默认情况下，事务隔离级别为serializable
设置 transactionscope 隔离级别
除超时值之外，transactionscope 的有些重载构造函数还接受 transactionoptions 类型的结构，用于指定隔离级别。 默认情况下，事务在隔离级别设置为 serializable 的情况下执行。 通常对频繁执行读取的系统选择 serializable 之外的隔离级别。 这需要全面地了解事务处理理论、事务本身的语义、所涉及的并发问题以及系统一致性的结果。
总结
这里只是一个案例，仅仅说明应用程序的驱动程序或api函数，有可能会需要（或默认）设定事务的隔离级别，这个一定要当心，避免由于人为失误导(不了解技术细节）致不小心提高事务隔离级别，造成不必要的死锁出现。另外，这里总结这篇文章，也仅仅是对这种案例感到有意思。
参考资料：
https://docs.microsoft.com/en-us/dotnet/framework/data/transactions/implementing-an-implicit-transaction-using-transaction-scope