ITPUB论坛－专业的IT技术社区

登录 注册
ITPUB论坛－专业的IT技术社区»论坛 › ≡ 大数据与数据库 ≡ › MS SQL Server › SQL SERVER 锁/进程 ID 问题
12345下一页
返回列表 发新帖
楼主: 寂寞的雨
收藏 | 打印 上一主题 下一主题

SQL SERVER 锁/进程 ID 问题

[复制链接]
寂寞的雨
论坛徽章:
0
21#
 楼主| 发表于 2006-3-20 08:09 | 只看该作者
谢谢楼上了，今天我再试试，建一个跟踪，不过中午再建，昨天建的哪个时间长，产生的跟踪文件太多太大了，看看今天数据库运行的怎么样，能不能找到死锁进程？

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
22#
 楼主| 发表于 2006-3-20 08:11 | 只看该作者
希望大家继续关注这个问题呀！顶呀！

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
23#
 楼主| 发表于 2006-3-20 08:19 | 只看该作者
上传一个SQL跟大家一起分享，不错呀！有兴趣的Download！

sql_ox.pdf

340.22 KB, 下载次数: 126

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
24#
 楼主| 发表于 2006-3-20 18:43 | 只看该作者
今天运行了一天的“事件探查器”，到下午6点的时候，我的客户端程序中返回下面的死锁提示窗口，以前从来没有出现在，只是没有响应，是不是我运行事件探查器的问题。不过弹出这个窗口后并没有死机，只是时间长一点。
我用Select blocked From master.dbo.sysprocesses where blocked<>0 and dbid=db_id('GSP_V80') 语句查询，返回值为：71、59、71、79
第二次查返回值为：71、59、然后从事件探查器中筛选了进程71的部分数据，另存为SQL脚本。（此时我已经停止的事件探查器，但是在企业管理器中，无法打开当前活动，提示超时）

警告.jpg (32.86 KB, 下载次数: 77)

警告.jpg

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
25#
 楼主| 发表于 2006-3-20 18:50 | 只看该作者
我从事件探查器中筛选的SQL语句，请高手帮忙分析一下（最下面的附件为原文件，请下载分析一下）：
begin tran
go
commit tran
go
execute dm_xcl 'fyjjx'
go
begin tran
go
commit tran
go
execute dm_xcl 'fyjjxk'
go
begin tran
go
commit tran
go
execute dm_xcl 'fyjjx'
go
begin tran
go
commit tran
go
execute dm_xcl 'fyjjxk'
go
begin tran
go
commit tran
go
execute dm_xcl 'fyjjx'
go
begin tran
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
commit tran
go
execute sp_xs_czdmggd 'asbn'
go
begin tran
go
execute dbo.sp_dw_xs_xzspdm;1 @tempdm = 'asbn%'
go
commit tran
go
execute dm_xcl 'asbnya'
go
begin tran
go
commit tran
go
execute dm_xcl 'asbnyjl'
go
begin tran
go
commit tran
go
execute dm_xcl 'asbnya'
go
begin tran
go
commit tran
go
execute dm_xcl 'asbnyjl'
go
begin tran
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
commit tran
go
execute sp_xs_czdmggd 'pxp'
go
begin tran
go
execute dbo.sp_dw_xs_xzspdm;1 @tempdm = 'pxp%'
go
commit tran
go
execute dm_xcl 'pxpln'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpsykd'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpln'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpsykd'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpln'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpsykd'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpln'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpsykd'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpln'
go
begin tran
go
commit tran
go
execute dm_xcl 'PXPNXJTY'
go
begin tran
go
commit tran
go
execute dm_xcl 'pxpsykd'
go
begin tran
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
commit tran
go
execute sp_xs_czdmggd 'gxp'
go
begin tran
go
execute dbo.sp_dw_xs_xzspdm;1 @tempdm = 'gxp%'
go
commit tran
go
execute dm_xcl 'gxpbys'
go
begin tran
go
commit tran
go
execute dm_xcl 'gxpgd'
go
begin tran
go
commit tran
go
execute dm_xcl 'gxpbys'
go
begin tran
go
commit tran
go
execute dm_xcl 'gxpgd'
go
begin tran
go
commit tran
go
execute dm_xcl 'gxpbys'
go
begin tran
go
select pt_jk_teshu from yplbk where lb_id =''
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
execute dbo.sp_jh_xs_db_kh_sl_01;1 @tempdm = 'gxpbys', @zxkh = 0, @zdkh = 15
go
commit tran
go
execute wmy_lkh_dj 'bnkj' , 'gxpbys'
go
begin tran
go
  SELECT  t_khdj.rq ,           t_khdj.pzdm ,           t_khdj.dj     FROM t_khdj      WHERE ( t_khdj.dwdm = 'bnkj' ) and          ( t_khdj.pzdm = 'gxpbys' )   
go
select yorn from p_kh where name ='贾玉宁' and kh ='6库'     
go
execute dbo.sp_dw_xs_xgspsl;1 @tempdm = 'gxpbys', @tempkh = 6
go
select yorn from p_kh where name ='贾玉宁' and kh ='6库'     
go
commit tran
go
execute wmy_lkh_dj 'bnkj' , 'gxpbys'
go
begin tran
go
  SELECT  t_khdj.rq ,           t_khdj.pzdm ,           t_khdj.dj     FROM t_khdj      WHERE ( t_khdj.dwdm = 'bnkj' ) and          ( t_khdj.pzdm = 'gxpbys' )   
go
select yorn from p_kh where name ='贾玉宁' and kh ='6库'     
go
select dj_bz from ckyek where dm ='gxpbys' and pihao ='G5D004'
go
commit tran
go
execute sp_xs_dj 'gxpbys'
go
begin tran
go
select ccwd from dmk where dm ='gxpbys'
go
commit tran
go
execute gsp_check_hw 6 , 'gxpbys'
go
begin tran
go
commit tran
go
execute sp_xs_qpjdj 'gxpbys' , 'G5D004' , 6 , '0' , '0' , '1'
go
execute sp_xs_qzxdj 'gxpbys'
go
begin tran
go
commit tran
go
execute sp_ckyek_ckj 'gxpbys' , 'G5D004' , 6 , '0' , '0' , '1'
go
begin tran
go
commit tran
go
execute wmy_lkh_dj 'bnkj' , 'gxpbys'
go
begin tran
go
  SELECT  t_khdj.rq ,           t_khdj.pzdm ,           t_khdj.dj     FROM t_khdj      WHERE ( t_khdj.dwdm = 'bnkj' ) and          ( t_khdj.pzdm = 'gxpbys' )   
go
select yorn from p_kh where name ='贾玉宁' and kh ='6库'     
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
commit tran
go
execute sp_xs_czdmggd 'slad'
go
begin tran
go
execute dbo.sp_dw_xs_xzspdm;1 @tempdm = 'slad%'
go
commit tran
go
execute dm_xcl 'sladp'
go
begin tran
go
commit tran
go
execute dm_xcl 'sladph'
go
begin tran
go
commit tran
go
execute dm_xcl 'sladps'
go
begin tran
go
commit tran
go
execute dm_xcl 'sladpsx'
go
begin tran
go
commit tran
go
execute dm_xcl 'sladps'
go
begin tran
go
commit tran
go
execute dm_xcl 'sladph'
go
begin tran
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
execute dbo.sp_dw_xs_xgckxs;1 @temppzh = 8028, @temprq = '2006-3-20 0:0:0.000'
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
execute dbo.sp_dw_xs_dy;1 @temppzh = 8028, @temprq = '2006-3-20 0:0:0.000'
go
commit tran
go
execute sp_xs_dyf '2006-3-20 0:0:0.000' , 8028
go
begin tran
go
select kqmc from ypkqb where left ( kq_id , 1 ) ='I'
go
select lx from gsp_ck where kh =6.000000000000000000
go
select kqmc from ypkqb where left ( kq_id , 1 ) ='J'
go
select lx from gsp_ck where kh =6.000000000000000000
go
select kqmc from ypkqb where left ( kq_id , 1 ) ='J'
go
select lx from gsp_ck where kh =6.000000000000000000
go
select kqmc from ypkqb where left ( kq_id , 1 ) ='P'
go
select lx from gsp_ck where kh =6.000000000000000000
go
commit tran
go
execute sp_xs_pzh_ljjesl 8028 , '2006-3-20'
go
begin tran
go
commit tran
go
select object_id('sp_xs_maxpzh')
go
select type, length from syscolumns where id = 724197630 and name = '@xs_rq'
go
DECLARE @P002 datetime SELECT @P002 = '2006-3-20 0:0:0.000' execute sp_xs_maxpzh @xs_max_pzh = 7999, @xs_rq = @P002 output SELECT @P002
go
begin tran
go
select count ( *) from xs where rq ='2006-3-20 0:0:0.000' and pzh =8029 and hsf =1
go
Select Dwdm , Xsdw , bm From Xs Where Pzh =8029 And Rq ='2006-3-20'
go
Select Kl From Dwk Where Dwdm =''
go
select count ( *) from xs where rq ='2006-3-20 0:0:0.000' and pzh =8029 and hsf =1
go
execute dbo.sp_dw_xs_xzghdw;1 @tempdm = 'xssd'
go
select dwdm from xs where rq ='2006-3-20 0:0:0.000' and pzh =8029
go
Select Kl From Dwk Where Dwdm ='xssdxczlrj'
go
select ywy from dwk where dwdm ='xssdxczlrj'
go
  SELECT  BMDM.BMMC ,           BMDM.BM ,           BMDM.bm_jzfs     FROM BMDM      WHERE ( BMDM.BM like '贾玉宁'+'%' ) or          ( BMDM.BMMC like '贾玉宁'+'%' )         
go
commit tran
go
execute sp_xs_pzh_ljjesl 8029 , '2006-3-20'
go
begin tran
go
execute dbo.sp_dw_xs_llckxs;1 @temppzh = 7999, @temprq = '1900-1-1 0:0:0.000'
go
exec sp_server_info 18
go
select @@langid
go
select dateformat from master.dbo.syslanguages where langid = 30
go
use  gsp_v80
go
begin tran
go
commit tran
go
execute sp_date
go
begin tran
go
commit tran
go
execute sp_xs_bak_ckyek '2006-3-19 0:0:0.000'
go
begin tran
go
declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=2
declare @P3 int
set @P3=-1
exec sp_cursoropen @cursor = @P1 output, @stmt = 'select BMMC from', @scrollopt = @P2 output, @ccopt = 4, @rows = @P3 output
select @P1, @P2, @P3
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 1, @rownumber = 0, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorclose @cursor = 180150000
go
declare @P1 int
set @P1=180150001
declare @P2 int
set @P2=2
declare @P3 int
set @P3=-1
exec sp_cursoropen @cursor = @P1 output, @stmt = 'Select p_name Fr', @scrollopt = @P2 output, @ccopt = 4, @rows = @P3 output
select @P1, @P2, @P3
go
exec sp_cursorfetch @cursor = 180150001, @fetchtype = 1, @rownumber = 0, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150001, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150001, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorfetch @cursor = 180150001, @fetchtype = 32, @rownumber = 1, @nrows = 2
go
exec sp_cursorclose @cursor = 180150001
go
select p_name , p_pass from pass where p_name ='abc'
go
SELECT pass.添加入库验收单 , pass.销售退回 , pass.库存退货 , pass.验收签字 , pass.进货复核 , pass.进货计划 , pass.缺货情况 , pass.合同管理 , pass.库存盘点 , pass.首营企业品种 , pass.查询入库及库存 , pass.添加销售单 , pass.销售复核 , pass.营业收款台 , pass.财务查询 , pass.财务收付款 , pass.药品检验 , pass.药品养护 , pass.公司人事档案 , pass.操作人员权限 , pass.系统维护 , pass.yp , pass.db , pass.fhsl , syqysq , syqysp , sqpzsq , sypzsp , shebei , xsth_shenpi FROM pass WHERE pass.p_name ='abc'                                                                                                   
go
select count ( *) from gsp_zlys where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 or pzh > 18999 + 2000000 and pzh < 18999 + 2000001 + 1000 OR pzh > 18999 + 1000000 and pzh < 18999 + 1000001 + 1000 )
go
select count ( *) from xs where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 or pzh > 18999 + 2000000 and pzh < 18999 + 2000001 + 1000 OR pzh > 18999 + 1000000 and pzh < 18999 + 1000001 + 1000 )
go
select count ( *) from db where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 )
go
select count ( *) from wbdb where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 )
go
execute dbo.sp_skt_ssdz_jiang;1 @temprq = '2006-3-20 0:0:0.000'
go
execute dbo.sp_skt_ssdz_jiang;1 @temprq = '2006-3-20 0:0:0.000'
go

sql.sql

654.92 KB, 下载次数: 11

使用道具 举报

回复
pydwh
论坛徽章:
2
2010新春纪念徽章 日期:2010-03-01 11:20:08
26#
发表于 2006-3-20 20:10 | 只看该作者

  2. begin tran
  3. go
  4. declare @P1 int
  5. set @P1=180150000
  6. declare @P2 int
  7. set @P2=2
  8. declare @P3 int
  9. set @P3=-1
  10. exec sp_cursoropen @cursor = @P1 output, @stmt = 'select BMMC from', @scrollopt = @P2 output, @ccopt = 4, @rows = @P3 output
  11. select @P1, @P2, @P3
  12. go
  13. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 1, @rownumber = 0, @nrows = 2
  14. go
  15. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  16. go
  17. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  18. go
  19. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  20. go
  21. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  22. go
  23. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  24. go
  25. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  26. go
  27. exec sp_cursorfetch @cursor = 180150000, @fetchtype = 32, @rownumber = 1, @nrows = 2
  28. go
  29. exec sp_cursorclose @cursor = 180150000
  30. go
  31. declare @P1 int
  32. set @P1=180150001
  33. declare @P2 int
  34. set @P2=2
  35. declare @P3 int
  36. set @P3=-1
  37. exec sp_cursoropen @cursor = @P1 output, @stmt = 'Select p_name Fr', @scrollopt = @P2 output, @ccopt = 4, @rows = @P3 output
  38. select @P1, @P2, @P3
  39. go
  40. exec sp_cursorfetch @cursor = 180150001, @fetchtype = 1, @rownumber = 0, @nrows = 2
  41. go
  42. exec sp_cursorfetch @cursor = 180150001, @fetchtype = 32, @rownumber = 1, @nrows = 2
  43. go
  44. exec sp_cursorfetch @cursor = 180150001, @fetchtype = 32, @rownumber = 1, @nrows = 2
  45. go
  46. exec sp_cursorfetch @cursor = 180150001, @fetchtype = 32, @rownumber = 1, @nrows = 2
  47. go
  48. exec sp_cursorclose @cursor = 180150001
  49. go
  50. select p_name , p_pass from pass where p_name ='abc'
  51. go
  52. SELECT pass.添加入库验收单 , pass.销售退回 , pass.库存退货 , pass.验收签字 , pass.进货复核 , pass.进货计划 , pass.缺货情况 , pass.合同管理 , pass.库存盘点 , pass.首营企业品种 , pass.查询入库及库存 , pass.添加销售单 , pass.销售复核 , pass.营业收款台 , pass.财务查询 , pass.财务收付款 , pass.药品检验 , pass.药品养护 , pass.公司人事档案 , pass.操作人员权限 , pass.系统维护 , pass.yp , pass.db , pass.fhsl , syqysq , syqysp , sqpzsq , sypzsp , shebei , xsth_shenpi FROM pass WHERE pass.p_name ='abc'
  53. go
  54. select count ( *) from gsp_zlys where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 or pzh > 18999 + 2000000 and pzh < 18999 + 2000001 + 1000 OR pzh > 18999 + 1000000 and pzh < 18999 + 1000001 + 1000 )
  55. go
  56. select count ( *) from xs where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 or pzh > 18999 + 2000000 and pzh < 18999 + 2000001 + 1000 OR pzh > 18999 + 1000000 and pzh < 18999 + 1000001 + 1000 )
  57. go
  58. select count ( *) from db where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 )
  59. go
  60. select count ( *) from wbdb where rq <> '2006-03-20' and ( pzh > 18999 and pzh < 18999 + 1001 )
  61. go
  62. execute dbo.sp_skt_ssdz_jiang;1 @temprq = '2006-3-20 0:0:0.000'
  63. go
  64. execute dbo.sp_skt_ssdz_jiang;1 @temprq = '2006-3-20 0:0:0.000'
  65. go
复制代码

你注意一下上面那段的事务只有begin tran,没有commit tran,很明显,这段事务太长了,而且其它进程估计也执行了类似的事务,锁住了对方要的资源,死锁就产生了...

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
27#
 楼主| 发表于 2006-3-21 08:13 | 只看该作者
谢谢了，我会进一步处理，应该没有什么问题，看来我要查原程序了（PB 6.5编的原程序），一两天内我会把测试的结果贴上来的，请关注呀！

2205.gif (20.06 KB, 下载次数: 70)

2205.gif

使用道具 举报

回复
pydwh
论坛徽章:
2
2010新春纪念徽章 日期:2010-03-01 11:20:08
28#
发表于 2006-3-21 09:08 | 只看该作者
最初由 寂寞的雨 发布
[B]谢谢了，我会进一步处理，应该没有什么问题，看来我要查原程序了（PB 6.5编的原程序），一两天内我会把测试的结果贴上来的，请关注呀！ [/B]

呵呵，如果你的sql语句是给全了的，问题就在那里了。你还是先去学习一下事务与锁的关系再去检查程序吧。

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
29#
 楼主| 发表于 2006-3-21 09:28 | 只看该作者
SQL语句并没有给全，上面还有好多，不过看得出来上面的SQL语句并没有什么问题，我是复制了接近死锁的最近时间段的SQL语句。问题应该出在这里！

使用道具 举报

回复
寂寞的雨
论坛徽章:
0
30#
 楼主| 发表于 2006-3-21 09:30 | 只看该作者
你还是先去学习一下事务与锁的关系再去检查程序吧

可否提供相关的好文章和学习资料！

使用道具 举报

回复

12345下一页
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　联系我们 未成年人举报专区　
京ICP备16024965号-8 　北京市公安局海淀分局网监中心备案编号：11010802021510　广播电视节目制作经营许可证：编号（京）字第1149号   
快速回复 返回顶部 返回列表