SqlServer 可更新订阅中在订阅库并发获取最大单据号死锁测试及解
|
说明： 很多交易的系统都需要单据号，而单据号按顺序并发获取又是一件头疼的事！~ ? 一般单据号格式为：前缀+日期+编号（如：KK20150501000001） ? 现在模拟测试，数据库中的单据号作为一张表处理。 数据库单据号表（Billnumber）存储每个账号及其最大的单据号总共一条记录 单据号是组合而成的字符串，后6位为序号。 获取单据号的存储过程： EXEC [dbo].[GetBillnumber_Test]@Account='Account',@Billnumber?NVARCHAR(20)OUTPUT ? 存储过程功能：传递用户账号，返回用户的最大单据号。 存储过程内部逻辑：（查询和更新都使用了聚集索引查找） a. 表中找出该账号的单据号 b. 如果单据号存在，截取后位字符转数字加1生成新的单据号返回，并更新这条记录为最新单据号 c. 如果单据号不存在，则为这账号第一次插入新的单据号，并返回该单据号 但是并发操作时，就可能发生堵塞以及死锁。当前数据库的事务级别为读提交（read committed）。 通常的操作是，存储过程内部逻辑查询表中的单据号时（如上a.），加上锁并保持锁到事务结束。我这里为WITH(UPDLOCK,HOLDLOCK)，网上还有一种这样加法WITH (XLOCK,PAGLOCK) ，这种加锁方法会把整页数据都不能访问。 为了方便在SQLqueryStress测试，我把存储过程改为： EXEC [dbo].[GetBillnumber_Test]@Account='Account' ? 即不返回单据号，而在存储过程末尾把单据号抛出，让SQLqueryStress能够获取查看单据号 DECLARE @RE NVARCHAR(100) DECLARE @billNumber NVARCHAR(100)--已赋值的单据号（赋值过程不描述） SET @RE=N'[billNumber='+@billNumber+N']? [SPID='+CONVERT(VARCHAR(10),@@SPID)+N']' RAISERROR (@RE,16,1,@billNumber)?--抛出 打开2个SQLQuerySrees窗口，设置好连接参数： 第一个窗口：打开1个连接，每个连接执行500次；账号为A 第二个窗口：打开1个连接，每个连接执行500次；账号为A
数据库中开启死锁跟踪： --? 打开死锁跟踪 dbcc traceon(1222,-1) dbcc tracestatus 【执行测试】 快速执行第一个,第二个SQLQuerySrees窗口（点击“GO”） 执行完成后，点击SQLQuerySrees日志查看：
测试结果： 发现单据号都是递增的，只是中间有跳跃，因为另一个进程也在获取单据号。查看SqlServer日志，并没有看到死锁！ 打开几个SQLqueryStress测试也是一样。结果是正常和符合的！ ? ? 但是！问题来了！~当数据库中创建了可更新订阅，并且在订阅数据库操作，就会产生死锁！~ 对该表创建可更新订阅：
与上面一样，的步骤，对订阅数据库的单据表进行单据号的获取操作。 ? 在执行SQLQuerySrees时，同时也快速返回到数据库，查看锁情况。参考脚本：SqlServer 查看当前锁请求情况的一个脚本
可以看到，一个spid在对表BillNumberRecord更新时，也触发将数据输入插入到同步队列表中。对单据表使用的是范围所RangeS_U和RangeX_X。这会儿查看SqlServer日志， 3个参与死锁的存储过程机器堵塞的语句： sp_MSdel_dboBillNumberRecord：（死锁牺牲品） delete [dbo].[BillNumberRecord] where [Guid]=@pkc1and[Account]=@pkc2andmsrepl_tran_version=@msrepl_tran_version trg_MSsync_upd_BillNumberRecord： update [dbo].[BillNumberRecord]setmsrepl_tran_version=@c7? where? [Guid] = @c1 and [Account] = @c2 EXEC[dbo].[GetBillnumberBySID_Test]： 操作为：更新单据表的单据号列为最大单据号（此处语句就不显示了） 资源列表如下： --? deadlockvictim=processe988e0 resource-list keylock objectname= BillNumberRecordindexname=聚集索引id=locke1193c0mode=RangeX-X owner-list owner id=processcdac70mode=RangeX-X waiter-list waiter id= processe988e0mode=UrequestType=wait keylock objectname= BillNumberRecordindexname=主键(非聚集索引)id=lock58d986c0mode=U owner-list owner id= processe988e0mode=U waiter-list waiter id=processcdac70mode=UrequestType=wait 接下来在数据库中执行查看执行计划，到底处理了哪些语句： EXEC [dbo].[GetBillnumber_Test]@Account='Account' ? 结果如下，按存储过程内部逻辑： a. 首先查找这个账号当前的单据号（聚集索引查找）(UPDLOCK,HOLDLOCK)
b.单据号存在，生成一个新的最大单据号，更新回表中（聚集索引查找和更新）
c.扫描伪表inserted（聚集索引），在触发器trg_MSsync_upd_BillNumberRecord中
d. 扫描伪表deleted（聚集索引），在触发器trg_MSsync_upd_BillNumberRecord中
e. 触发器trg_MSsync_upd_BillNumberRecord执行更新msrepl_tran_version，（索引查找，更新聚集索引）
f.最后将同步的事务和命令插入到队列表中
上面能出现索引查找的，只有trg_MSsync_upd_BillNumberRecord了。使用主键非聚集索引（guid，account）查找，更新聚集索引（account）。 ? 这算是设计上的问题，作为同步中的主键，最好也是聚集索引，表主键，在同步触发中是不会更改的，而同步是经常触发更改的，主键又不是聚集索引，将频繁地更改。但是现在数据已经同步中，不能更改了，只能使用其他方法。而没有同步的时候，并发是正常的，没出现死锁。就没要更改原来获取单据号的存储过程了，也没有什么可以改了。 ? 既然是下面这个语句引起的，那就想办法更改索引。 update [dbo].[BillNumberRecord]setmsrepl_tran_version=@c7? where? [Guid] = @c1 and [Account] = @c2 最后解决死锁方法是： 更改同步触发器[dbo].[trg_MSsync_upd_BillNumberRecord] ? 将触发器内的更改语句注释。 update[dbo].[BillNumberRecord] set msrepl_tran_version = @c7? where? [Guid] = @c1?and?[Account]= @c2 ? 设置新的更新方法，并强制加上聚集索引。这样就能使用聚集索引查找，并且是聚集索引更新！ update bsetmsrepl_tran_version=@c7? from [dbo].[BillNumberRecord]asbwith(index=IX_BillNumberRecord) where? [Guid] = @c1 and ?[Account]=@c2??? ? 测试的时候打开了十几个SQLQuerySrees，除了几个是其他账号，其余都是相同账号。 这里要说明一下，为什么打开十几个。 因为我们要模拟十几个账号同时操作。SQLQuerySrees虽然有两个选择设置——迭代次数和线程数。 迭代次数：是重复执行的次数 线程数：是创建多少个新的数据库session连接 ? 迭代次数和线程数只是模拟并发，为了模拟在某一时刻同时操作才设置更多。虽然打开不同线程，但是似乎还是顺序创建。
这是某一刻查看的锁资源情况，如下图
此刻只有spid=199 的会话正在获取单据号，使用的是范围锁 RangX_X，并且对表加上意向排他锁（只能查询不能更改），而其他线程都是在查询单据号并想获取锁RangS_U，因此都在等待。结果再日志中是不会再出现死锁了！！~同步也是正常的！~而如果没有更改触发器，出现了死锁，并且要处理死锁，执行时间反而更长。 ? 至此，同步中并发获取最大单据号的测试就到这吧。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
