300分解决我的问题（可能死锁造成），欢迎讨论！(300分)

我的程序是一个收费系统，银行代收费，使用indy9中Tidtcpserver，我采用临界保护方式，现在正常情况下运行挺好，但当访问量大的时候，有时出现无法查询，只能服务器接收，我猜测是数据库死锁，不知道如何解决才好，下面是我的例码（略有删减），请高手提供解决问题思路，十分感谢！<br>//-------接收数据事件,客户端向服务器传送数据时触发----------------------------//<br>procedure TForm1.serverExecute(AThread: TIdPeerThread);<br> &nbsp;var<br> &nbsp; &nbsp;r_data:string; &nbsp; &nbsp; &nbsp; &nbsp;//接收的字符串<br> &nbsp; &nbsp;sl:tstringlist; &nbsp; &nbsp; &nbsp; //分隔接收数据为各个字段<br> &nbsp; &nbsp;lenstr:string; &nbsp; &nbsp; &nbsp; &nbsp;//pklen:数据长度字段<br> &nbsp; &nbsp;package_len:integer; &nbsp;//数据包长度 = 去掉pklen字段以后的长度<br> &nbsp;begin<br> &nbsp; &nbsp;with athread.Connection do<br> &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp;//athread.Connection.ReadTimeout := 5000; //设置读数据超时时间为5秒<br> &nbsp; &nbsp; &nbsp; &nbsp;sl:=tstringlist.Create; &nbsp; //创建tstringlist<br> &nbsp; &nbsp; &nbsp; &nbsp;lenstr:= readstring(4); &nbsp; //读取数据长度<br> &nbsp; &nbsp; &nbsp; &nbsp;try<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;package_len := strtoint(lenstr);<br> &nbsp; &nbsp; &nbsp; &nbsp;except<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; exit ;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp; &nbsp; &nbsp;//try<br> &nbsp; &nbsp; &nbsp; &nbsp;//begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r_data := readstring(package_len); &nbsp; &nbsp;//读取数据包<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sl:= split(r_data,'|'); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //将各个字段分解出来<br> &nbsp; &nbsp; &nbsp; &nbsp;//-----------------判断是何种操作-------------------------------------//<br> &nbsp; &nbsp; &nbsp; &nbsp;if sl.Strings[1] = '1111' then &nbsp; &nbsp; &nbsp; &nbsp;//查询操作<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cscx.Enter; &nbsp;//进入临界区，进行保护<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; query(sl.Strings[2],athread);<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; r_data := '';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cscx.Leave; &nbsp;//操作完成，离开临界区<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br><br> &nbsp; &nbsp; &nbsp; &nbsp;if sl.Strings[1]='2222' then &nbsp; //收费功能<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; athread.Connection.InputBuffer.Clear; //清接收缓冲区<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cssf.Enter; &nbsp;//进入临界区，进行保护<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sf(sl.strings[2],sl,athread); &nbsp;//用户编号<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; r_data:='';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cssf.Leave; &nbsp;//离开线程保护区<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp; end;<br> &nbsp;end;<br><br>//-----按卡号查询用户应收费用信息 交易代码：1111------------------------------------//<br>procedure tform1.query(cardid:String;athread:tidpeerthread);<br> &nbsp;var<br> &nbsp;begin<br> &nbsp; &nbsp; //----进行查询------------------------------------------------------------//<br> &nbsp; &nbsp;try<br> &nbsp; &nbsp; &nbsp;begin<br><br> &nbsp; &nbsp; &nbsp; &nbsp;sql:= 'SELECT a.bh, a.fh, a.xm, a.postaddr, a.cardid, b.nd, b.dj,'+<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'b.mj, b.yjk, b.yshk, b.qk, b.shfybh, b.shfbzhid '+<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'FROM a,b WHERE b.qk &lt;&gt; 0 and a.bh = b.bh and a.cardid=:cardid;';<br> &nbsp; &nbsp; &nbsp; &nbsp;query_cx.Close;<br> &nbsp; &nbsp; &nbsp; &nbsp;query_cx.SQL.Clear;<br> &nbsp; &nbsp; &nbsp; &nbsp;query_cx.sql.Add(sql);<br> &nbsp; &nbsp; &nbsp; &nbsp;query_cx.Parameters.ParamByName('cardid').Value := trim(cardid);<br> &nbsp; &nbsp; &nbsp; &nbsp;if query_cx.Prepared=false then query_cx.Prepared:=true;<br><br> &nbsp; &nbsp; &nbsp; &nbsp;query_cx.Open;<br> &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp;except<br> &nbsp; &nbsp; &nbsp; begin<br> &nbsp; &nbsp; &nbsp; &nbsp; send('','0','1111','100',athread);<br> &nbsp; &nbsp; &nbsp; &nbsp; console.Lines.Add(' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;查询时出错');<br> &nbsp; &nbsp; &nbsp; &nbsp; exit;<br> &nbsp; &nbsp; &nbsp; end;<br> &nbsp; &nbsp;end;<br><br> &nbsp; &nbsp; &nbsp;athread.Connection.InputBuffer.Clear; //清接收缓冲区<br> &nbsp; &nbsp; &nbsp;exit;<br> &nbsp;end;<br>//------------------------收费功能函数-交易代码：2222---------------------------------------//<br>procedure tform1.sf(yhbh:String;sl:tstringlist;athread:tidpeerthread);<br> &nbsp;var<br><br> &nbsp; &nbsp;send_cmd:string;<br> &nbsp; &nbsp;sql:string; &nbsp;//sql语句<br> &nbsp; &nbsp;q_sql:string; //<br> &nbsp; &nbsp;dm:string; &nbsp; //流水表的代码<br> &nbsp; &nbsp;lsh:string; &nbsp; &nbsp; //银行流水<br> &nbsp; &nbsp;xm:string; &nbsp; &nbsp; &nbsp;//姓名<br> &nbsp; &nbsp;addr:string;<br> &nbsp; &nbsp;nd:string; &nbsp; &nbsp; &nbsp;//年度<br> &nbsp; &nbsp;dj:double;<br> &nbsp; &nbsp;mj:double;<br> &nbsp; &nbsp;gnqshrq:string; //起始日期<br> &nbsp; &nbsp;gnjsrq:string; &nbsp;//结束日期<br> &nbsp; &nbsp;pjh:string; &nbsp; &nbsp; //票据号<br> &nbsp; &nbsp;shfzhlid:string;<br><br> &nbsp; &nbsp;yf:string; &nbsp; &nbsp; &nbsp;//月份<br> &nbsp; &nbsp;shfje:double; &nbsp; //收费金额<br> &nbsp; &nbsp;shm:string; &nbsp; &nbsp; //说明<br> &nbsp; &nbsp;k,i:integer;<br> &nbsp; &nbsp;ibok:boolean;<br><br> &nbsp; &nbsp;lsreturn:string; &nbsp;//收费存储过程执行完标志<br> &nbsp;begin<br> &nbsp;ibok:=true;<br> &nbsp;zjesum:=0;<br> &nbsp;if sl.count=11 then<br> &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp;xm:=sl.strings[3];<br> &nbsp; &nbsp; &nbsp; &nbsp;addr:=sl.strings[4];<br> &nbsp; &nbsp; &nbsp; &nbsp;dj:=strtofloat(sl.strings[5]);<br> &nbsp; &nbsp; &nbsp; &nbsp;mj:=strtofloat(sl.strings[6]);<br> &nbsp; &nbsp; &nbsp; &nbsp;lsh:= sl.Strings[7];<br> &nbsp; &nbsp; &nbsp; &nbsp;nd:= sl.Strings[8];<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//pjh:=sl.Strings[i*4 + 3 ];<br> &nbsp; &nbsp; &nbsp; &nbsp;shfje:= strtofloat(sl.Strings[9]);<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//zjesum:=zjesum+shfje;<br> &nbsp; &nbsp; &nbsp; &nbsp;shm:= sl.Strings[10];<br> &nbsp; &nbsp; &nbsp; &nbsp;yf:=floattostr(monthof(date()));<br> &nbsp; &nbsp; &nbsp; &nbsp;if length(yf)=1 then yf:='0'+yf;<br> &nbsp; &nbsp; &nbsp; &nbsp;dm:=rightstr(FormatDateTime('YYMMDDHHMMSSzz',now()),14)+rightstr(inttostr(athread.ThreadID)+IntToStr(i),6);<br> &nbsp; &nbsp; &nbsp; &nbsp;if nd=gsshknd then &nbsp;shfzhlid:='71' else shfzhlid:='70';<br><br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;try &nbsp;//对执行sql语和存储过程进行异常保护<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br><br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;//执行收费的存储过程并返回的票据号等参数<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Close;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.SQL.Clear;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sql:='execute sp_shf &nbsp;:yhbh,:xm,:lsh,:nd,:shfje,:shm,:yf,:dm,'''+'2222'+ ''',:shfzhlid,:dj,:mj,:addr,jh output,:lsgnqshrq output,:lsgnjsrq output,:lsreturn output;';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.sql.Add(sql);<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('yhbh').Value := yhbh;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('xm').Value := xm;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('lsh').Value := lsh;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('nd').Value := nd;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('shfje').Value := shfje;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('shm').Value := shm;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('yf').Value := yf;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('dm').Value := dm;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('shfzhlid').Value := shfzhlid;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('dj').Value := dj;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('mj').Value := mj;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('addr').Value := addr;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('pjh').Value := '@pjh';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('lsgnqshrq').Value := '@lsgnqshrq';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('lsgnjsrq').Value := '@lsgnjsrq';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.Parameters.ParamByName('lsreturn').Value := '@lsreturn';<br><br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if query_pjh.Prepared=false then query_pjh.Prepared:=true;<br><br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;adocon.BeginTrans;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;query_pjh.ExecSQL;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lsreturn:=query_pjh.Parameters.parambyname('lsreturn').value;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if lsreturn='0' then &nbsp;//存储过程未执行完退出<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; send('','0','2222','200',athread); &nbsp;//交费交易失败,返回响应码200<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ADOCon.RollbackTrans; &nbsp;//回滚事务<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; exit;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;pjh:=query_pjh.Parameters.parambyname('pjh').value;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;gnqshrq:= query_pjh.Parameters.parambyname('lsgnqshrq').value;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;gnjsrq:= query_pjh.Parameters.parambyname('lsgnjsrq').value;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;adocon.CommitTrans;<br><br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;send(send_cmd,'0','2222','000',athread); &nbsp;//交费交易成功,返回响应码000<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;except //如果出现异常<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;adocon.RollbackTrans;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;send('','0','2222','200',athread); &nbsp;//交费交易失败,返回响应码200<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end;<br><br><br> &nbsp; &nbsp; end<br> &nbsp; else<br> &nbsp; &nbsp; begin<br><br> &nbsp; &nbsp; &nbsp; send('','0','2222','200',athread); &nbsp; //数据长度不对数据<br> &nbsp; &nbsp; &nbsp; exit;<br> &nbsp; &nbsp; end;<br> end;<br> //-----收费功能函数结束-------------------------------------------------------//

自已顶一下了！

建议重构，将逻辑简化，从而减少出错机会

感谢dreamfly1024回复，是不是我的问题表述不清啊，我试着简化了我的代码，但还是出现，其实我在收费存储过程开始处加上了SET LOCK_TIMEOUT 8000，结束时再加上SET LOCK_TIMEOUT -1。不知这样为什么没起作用。

我说一下我的思路，请各路高手帮忙，真的挺愁。我的思路是这样：<br>数据量：用户表A共有5万条记录，用户年度应收款表B共有约15万条，用户表交款明细表C共20万条,凭证表较小：c_pzh，银行流水表，只当天记录较小：c_yhls。<br>与银行边接短连接方式，即操作成功后断开。<br>1.刷卡操作：用户在银行刷卡，银行请求后，将该用户年度欠款返回。<br>2.收款：银行根据欠款情况进行收款，一次只收一笔，提交收费请求，我处理过程：<br> &nbsp;(1): 接到收费请求后，程序进入临界保护 <br> &nbsp; if sl.Strings[1]='2222' then &nbsp; //收费功能<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; athread.Connection.InputBuffer.Clear; //清接收缓冲区<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cssf.Enter; &nbsp;//进入临界区，进行保护<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sf(sl.strings[2],sl,athread); &nbsp;//进入收费过程<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; r_data:='';<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cssf.Leave; &nbsp;//离开线程保护区<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end;<br> &nbsp; &nbsp; end;<br><br> &nbsp;收费过程处理过程如下：<br> &nbsp;(2)：分配一个凭证号给该笔收款记录（每一笔款对应一个唯一的凭证号）<br> &nbsp;(3)：添加C表交款明细（insert c）<br> &nbsp;(4): 计算B表收款与欠款情况后更新(update b)<br> &nbsp;(5): 添加凭证记录。 (insert c_pzh)<br> &nbsp;(6): 记录银行流水。(insert c_yhls)<br><br>问题：现在出现问题基本都是在提交进入收费时开始，就死到那了，银行再查也查询不了了，只有结束任务再进正常。

顶一下