性能问题案例02——sybase连接堵塞问题
现象：近期现场反馈一个问题，系统在审批的时候，常常卡死，整个系统全然用不了，浏览器訪问处于loading的状态。
排查：
1.一般系统挂了首先想到内存问题，可是现象是loading，也就是说没有挂，线程正在运行，怀疑是线程被堵塞了。配置上jvisualvm监控了一下，出问题后内存没满确定不是内存问题，查看线程dump发现大部分都在运行sql查询，
初步发现是运行sql慢导致的。
2.我们用的是sybase数据库，运行了几个简单sql发现几分钟都没运行完，使用sp_sysmon "00:00:30"监控近30秒的情况发现cpu、内存、线程都没问题。差点儿1%使用率都不到，怀疑是某个连接堵塞了表，导致其它连接所有堵塞导致的。
3.我们使用自己写的存储过程查看堵塞的连接，结果例如以下：
PS：使用sp_lock命令就能够查看哪个连接堵塞了数据库，可是显示的都是tableid等，还须要再次查询转换成详细表名等，自己写的存储过程仅仅是此处转换了一下，后面会附上。
发现当中1万多个锁，7000多个排它锁，Ex_row-blk是堵塞了其它连接的锁，发现有21个堵塞了其它连接的锁，相应的表是T_ZXLD_SYYH，运行select * from master..sysprocesses找到相应的连接，如上图的User有316/287/283等，结果例如以下：
此时基本确定了原因，我们用的是c3p0连接池，应该是某个连接堵塞了表，其它全部连接查询时候都被堵塞了，导致连接池被占满，全部请求凡是涉及数据库查询的都被堵塞了。页面始终处于loading状态。
4.那么接下来就是找到堵塞的地方，上图发现tran_name都是$chained_transaction，结合程序推断，也就是说都在运行某个责任链里面的事务时堵塞了，系统就2处使用了责任链。直接就能够推断到时审批的责任链导致的，那么接下来就是排查此处代码是否有问题了。审批流程例如以下：
（1）开启事务
（2）依据參数查询出要审批的主表数据，2个sql
（3）逐条调用审批组件（类似工作流的一个组件）审批。每条数据大约5个更新sql，2个查询sql
（4）更新主表状态，每条数据1个更新sql
（5）插入审计日志，每条数据1个插入sql
（6）生成提醒消息。查询所有主表数据，20个查询sql，5个更新删除sql（很慢）
（7）更新增量记录表，记录该条数据改动时间和状态等，每条数据1个更新sql
（8）提交事务
怀疑并发时相互堵塞导致的，生成提醒消息的地方。会查询所有业务表，假设此时有其它连接在事务中审批。就会堵塞。其它连接在生成消息，相互堵塞。造成死锁。理论上数据库会自己主动处理死锁的，可是不知道什么原因，日志报的死锁数量不是特别多。
并且此处业务处理也太合理，正经常使用户每次批量审批大约100条数据。所以大约有900多个增删改sql。200多个查询sql，像生成消息等sql运行很慢（由于涉及更新旧消息）。这么多操作放到一个事务中很慢。审批表大约3000w数据，业务主表500w数据，运行起来也不是很快。
解决的方法：重构了此处代码。讲上面运行慢的（5）（6）（7）步骤新起一个线程，不放到事务中运行，即使失败也影响不大。
这样sql降低了30%，速度快了不少。更新到现场，观察了一个星期。发现未出现不响应等情况，算是问题解决。
尽管问题解决，可是根本原因没有找到。为什么会相互堵塞，为什么死锁没自己主动检測，这个是兴许须要跟踪的。
最后。问题尽管攻克了，可是中间沟通花了不少时间。和运维人员要现场数据耽误了非常多事，简单整理一下，兴许出问题的时候搜集这几个数据：
1、sp__lock的结果。（使用文章最后提供的存储过程）
2、select * from master..sysprocesses；
3、假设数据库慢。加上sp_sysmon "00:00:30"的结果；
4、中间件线程dump文件。
附上sybase查看锁的存储过程
IF OBJECT_ID ('dbo.sp__lock') IS NOT NULL
DROP PROCEDURE dbo.sp__lock
GO
create procedure sp__lock(
@dbname char(30)=null,@spid int=null,
@dont_format char(1) = null
)
as
begin
declare @dbid smallint
if @dbname is not null
select @dbid=db_id(@dbname)
if (charindex("sa_role", show_role()) > 0)
begin
if @dont_format is null
select "Type"=substring(v.name,1,11),
"User"=substring(suser_name(p.suid)+" ("+rtrim(convert(char(6),l.spid))+")",1,20),
"Table"=substring(db_name(l.dbid)+".."+convert(char(20),object_name(l.id,l.dbid)),1,26),
"Page"=convert(char(8),l.page),
"Cmd"=substring(p.cmd,1,11)
from master..syslocks l,
master..sysprocesses p,
master..spt_values v
where p.spid=l.spid and
l.type = v.number and
v.type = "L" and
p.dbid=isnull(@dbid,p.dbid) and
p.spid=isnull(@spid,p.spid) and
l.dbid=isnull(@dbid,l.dbid) and
l.spid=isnull(@spid,l.spid)
order by l.dbid, l.id, v.name
else
select "Type"=v.name,
"User"=suser_name(p.suid)+" ("+rtrim(convert(char(6),l.spid))+")",
"Table"=db_name(l.dbid)+".."+object_name(l.id,l.dbid),
"Page"=l.page,
"Cmd"=p.cmd
from master..syslocks l,
master..sysprocesses p,
master..spt_values v
where p.spid=l.spid and
l.type = v.number and
v.type = "L" and
p.dbid=isnull(@dbid,p.dbid) and
p.spid=isnull(@spid,p.spid) and
l.dbid=isnull(@dbid,l.dbid) and
l.spid=isnull(@spid,l.spid)
order by l.dbid, l.id, v.name
return
end
select "Type"=v.name,
"Usernm"=convert(varchar(60),suser_name(p.suid)+" ("+rtrim(convert(char(6),l.spid))+")"),
"TableNm"=convert(varchar(60),db_name(l.dbid)+".."),
"Page"=l.page,
"Cmd"=p.cmd,
l.id,
l.dbid
into #locks
from master..syslocks l,
master..sysprocesses p,
master..spt_values v
where p.spid=l.spid and
l.type = v.number and
v.type = "L" and
l.dbid=isnull(@dbid,l.dbid) and
l.spid=isnull(@spid,l.spid) and
p.dbid=isnull(@dbid,p.dbid) and
p.spid=isnull(@spid,p.spid)
update #locks
set TableNm=TableNm+object_name(id,dbid)
where dbid=db_id() or dbid=1 or dbid=2
update #locks
set TableNm=TableNm+convert(varchar,id)
where dbid<>db_id() and dbid>2
delete #locks
where TableNm like "tempdb..#locks%"
if @dont_format is null
select substring(Type, 1,11),
"User"=substring(Usernm, 1,14),
"Table"=convert(char(26),TableNm),
"Page"=convert(char(8),Page),
"Cmd"=substring(Cmd,1,11)
from #locks
order by dbid, id, Type
else
select Type, "User"=Usernm, "Table"=TableNm, Page, Cmd
from #locks
order by dbid, id, Type
end
GO
