1.现象
在das测试过程中发现发送方由于时序控制导致频繁出现死锁,牺牲事务的情况.
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300590,0'导致tb_0031中的记录进入f009n_0031=100的状态非常慢,大量实际已经送达的消息在发送端长时间处于发送中的状态(SS_SEND)。
2.死锁跟踪
用SQL Server Profiler跟踪死锁.图:
<deadlock-list>
<deadlock victim="process78a868">
<process-list>
<process id="process775438" taskpriority="0" logused="496" waitresource="KEY: 25:72057594042253312 (70002fa012a1)" waittime="5007" ownerId="423777181" transactionname="implicit_transaction" lasttranstarted="2014-06-13T17:02:39.137" XDES="0x1018b5800" lockMode="S" schedulerid="2" kpid="3748" status="suspended" spid="68" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2014-06-13T17:02:39.137" lastbatchcompleted="2014-06-13T17:02:39.137" hostname="PC-200906221632" hostpid="5704" loginname="sa" isolationlevel="read committed (2)" xactid="423777181" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x0200000091a57c33e82bf15eca2e771204f65770fb0fbae8">
(@1 tinyint,@2 varchar(8000))SELECT MIN([object_id]) FROM [tb_0031] WHERE [f009n_0031]=@1 AND [f023v_0031]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000e8928a071ea2fe5e743b11abb26559be24d4513c">
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300582,0' </frame>
</executionStack>
<inputbuf>
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300582,0' </inputbuf>
</process>
<process id="process78a868" taskpriority="0" logused="496" waitresource="KEY: 25:72057594042253312 (6b00528dc380)" waittime="5007" ownerId="423777182" transactionname="implicit_transaction" lasttranstarted="2014-06-13T17:02:39.137" XDES="0xcce41270" lockMode="S" schedulerid="3" kpid="3744" status="suspended" spid="99" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2014-06-13T17:02:39.137" lastbatchcompleted="2014-06-13T17:02:39.137" hostname="PC-200906221632" hostpid="5704" loginname="sa" isolationlevel="read committed (2)" xactid="423777182" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x0200000091a57c33e82bf15eca2e771204f65770fb0fbae8">
(@1 tinyint,@2 varchar(8000))SELECT MIN([object_id]) FROM [tb_0031] WHERE [f009n_0031]=@1 AND [f023v_0031]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000000a97680a5c97acb45ada2d5a9ccd6de08314e059">
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300587,0' </frame>
</executionStack>
<inputbuf>
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300587,0' </inputbuf>
</process>
<process id="process7a0da8" taskpriority="0" logused="496" waitresource="KEY: 25:72057594042253312 (6b00528dc380)" waittime="5007" ownerId="423777185" transactionname="implicit_transaction" lasttranstarted="2014-06-13T17:02:39.137" XDES="0x101629810" lockMode="S" schedulerid="4" kpid="3580" status="suspended" spid="70" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2014-06-13T17:02:39.137" lastbatchcompleted="2014-06-13T17:02:39.137" hostname="PC-200906221632" hostpid="5704" loginname="sa" isolationlevel="read committed (2)" xactid="423777185" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x0200000091a57c33e82bf15eca2e771204f65770fb0fbae8">
(@1 tinyint,@2 varchar(8000))SELECT MIN([object_id]) FROM [tb_0031] WHERE [f009n_0031]=@1 AND [f023v_0031]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000e750ee148a161bcd440bd202f9a33439da606d09">
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300592,0' </frame>
</executionStack>
<inputbuf>
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300592,0' </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594042253312" dbid="25" objectname="das_ws.dbo.tb_0031" indexname="PK_TB_0031" id="lock96137100" mode="X" associatedObjectId="72057594042253312">
<owner-list>
<owner id="process775438" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process7a0da8" mode="S" requestType="wait"/>
<waiter id="process78a868" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594042253312" dbid="25" objectname="das_ws.dbo.tb_0031" indexname="PK_TB_0031" id="lockba708a00" mode="X" associatedObjectId="72057594042253312">
<owner-list>
<owner id="process78a868" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process775438" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
3个进程执行的都是相同的语句,这个语句怎么会要求排它锁,共享锁?
猜测:
select min()并不会要求排它锁.事务过程中获取排它锁的并非这里显示的SQL语句.而是,之前update记录状态的语句.
select min(object_id) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300590,0'
和
select count(*) from tb_0031 where f009n_0031 = 0 and f023v_0031='100,1361,10600,Lsj2014061300590,0'
估计的查询计划相同.
图:
3.处理
采用下面的方式对比测试:
(1)屏蔽时序控制
bbox.conf增加是否支持时序控制的配置:
<enable_seq_ctrl>false></eanble_seq_ctrl> <!--是否支持时序控制,默认:true -->
屏蔽时序控制程序没有出现死锁!
(2)改变事务过程操作的顺序,先检查时序控制键,再修改记录状态.
修改后测试程序没有出现死锁!
原流程: CSEMQItem qe;
int iRet = semq_.GetItem(qe, 0,0,ref_object_id);
if(iRet < 0)
return -3;
if(iRet == 1)
{
GETDBC_RETURN(pdbor,this->local_dbc_.c_str(),-1);
pdbor->BeginTrans();
result = semq_.UpdateStatus(ref_object_id,ISEMQ::SS_ACK2);
if(result)
return -2;
if (semq_.IsEnableSeqCtrl()) {
if (!qe.sc_key_.empty()) {
CQQ_OBJECT_ID next_object_id=qe.object_id_;
int ret = semq_.GetNextSeqCtrlRecord(qe.sc_key_.c_str(),next_object_id);
GetThisLogger()->log(LO_STDOUT|LO_FILE,SEVERITY_DEBUG,"取下一个相同时序键%s的记录.qe.object_id=%I64d,next_object_id=%I64d,ret=%d.\n",
qe.sc_key_.c_str(),qe.object_id_,next_object_id,ret);
if (ret==1) {
pdbor->SetTXHook(TxHook,&semq_);
semq_tss_->v_rec.push_back(new SEMQ_RECORD_DATA_PAIR(next_object_id, qe.sc_key_));
}
else if (ret==-1) {
pdbor->RollbackTrans();
return -3;
}
}
}
修改后流程:
GETDBC_RETURN(pdbor,this->local_dbc_.c_str(),-1);
///< 如果目标是平台(且status==6)或者已送达,该确认的是有时序控制的记录,则取下一条
if (semq_.IsEnableSeqCtrl()) {
if ((qe.dest_type_==PT_CENTRAL||status==ISEMQ::SS_ACK2)&&!qe.sc_key_.empty()) {
CQQ_OBJECT_ID next_object_id=qe.object_id_;
int ret = semq_.GetNextSeqCtrlRecord(qe.sc_key_.c_str(),next_object_id);
if (ret==1) {
pdbor->SetTXHook(TxHook,&semq_);
semq_tss_->v_rec.push_back(new SEMQ_RECORD_DATA_PAIR(next_object_id, qe.sc_key_));
}
else if (ret==-1) {
return -3;
}
}
}
pdbor->BeginTrans();
if (semq_.UpdateStatus(ssr.record_id_,status,ISEMQ::SS_ACK2)) {
pdbor->RollbackTrans();
return -2;
}
在UpdateStatus之前GetNextSeqCtrlRecord时没有相同时序键的后续消息待发送, 此时新增了一条消息,由于受时序控制而未发送。
这种情况下就需要依赖后续扫描处理该记录。
逻辑完整性依赖一个后台定时执行的线程,这也是修改带来的弊端。