关于Deadlock 怎么解决

jlhwlw

谢谢各位大侠，帮我分析一下错误原因：Deadlock Id 192: Process (Familyid 311 311) (suid 5) was executing a SELECT command in the procedure 'get_by07ryspda'.
SQL Text: @ka = @P018 output , @prm_code = @P019 output , @prm_ErrorMsg = @P020 output
Deadlock Id 192: Process (Familyid 65 65) (suid 5) was executing a INSERT command in the procedure 'prc_sjzy_jsbsjzy'.
SQL Text: DECLARE @P002 numeric(5,0), @P003 varchar(56) SELECT @P002 = 1, @P003 = '' execute prc_sjzy_jsbsjzy @prm_bh = '200417674', @prm_appcode = @P002 output , @prm_errormsg = @P003 output
Deadlock Id 192: Process (Familyid 0, Spid 65) was waiting for a 'exclusive page' lock on page 4882110 of the 'by07ryspda' table in database 5 but process (Familyid 311, Spid 311) already held a 'shared page' lock on it.
Deadlock Id 192: Process (Familyid 0, Spid 311) was waiting for a 'shared page' lock on page 4882109 of the 'by07ryspda' table in database 5 but process (Familyid 65, Spid 65) already held a 'exclusive page' lock on it.

Deadlock Id 192: Process (Familyid 0, 311) was chosen as the victim. End of deadlock information.


Deadlock Id 193: detected. 1 deadlock chain(s) involved.

Deadlock Id 193: Process (Familyid 367 367) (suid was executing a UPDATE command in the trigger 'tU_gcwyfz'.
SQL Text: execute pf_yhjzdsj_zb @bxxm ='B' , @yhhh ='800000119' , @yhzh ='4402902009024988826x' , @zph ='10000704' , @zpzl ='2' , @zje =12831559.98 , @yspzbh ='2005041900005' , @cwry ='6' , @ztbh ='04' , @jsrq ='20050419'
Deadlock Id 193: Process (Familyid 218 21 (suid 5) was executing a COND command in the trigger 'tI_gcwyfmxz'.
SQL Text: DECLARE @P012 numeric(5,0), @P013 varchar(200) SELECT @P012 = 0, @P013 = '' execute Prc_K_UnitPayment @prm_dwbm = '900288', @prm_kssj = '20050315', @prm_jzsj = '20050419', @prm_czry = '谷银丽', @prm_zflb = '38', @prm_tccwsxh = 'BC2005041900303', @prm_bccwsxh = 'XC2005041900085', @prm_gwycwsxh = 'WC2005041900085', @prm_zhcwsxh = 'BC2005041900304', @prm_zfxlh = 'ZX2005041900085', @prm_zfnd = '2005', @prm_AppCode = @P012 output , @prm_ErrorMsg = @P013 output
Deadlock Id 193: Process (Familyid 0, Spid 21 was waiting for a 'shared intent' lock on the 'gcwyfz' table in database 9 but process (Familyid 367, Spid 367) already held a 'exclusive table' lock on it.
Deadlock Id 193: Process (Familyid 0, Spid 367) was waiting for a 'exclusive page' lock on page 476065 of the 'gcwyfmxz' table in database 9 but process (Familyid 218, Spid 21 already held a 'exclusive page' lock on it.

Deadlock Id 193: Process (Familyid 0, 21 was chosen as the victim. End of deadlock information.
Deadlock Id 214: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0050000100001.00 18.0000
1
1
118.00
0
Deadlock Id 214: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0030000100001.005.0000
1
1
15.00
0
Deadlock Id 214: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697772 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 214: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697597 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 214: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

00:00000:00082:2005/06/17 12:47:43.18 server  Deadlock Id 215 detected
Deadlock Id 215: detected. 1 deadlock chain(s) involved.

Deadlock Id 215: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0050000100001.00 18.0000
1
1
118.00
0
Deadlock Id 215: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0030000100001.005.0000
1
1
15.00
0
Deadlock Id 215: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697772 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 215: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697597 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 215: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

00:00000:00105:2005/06/17 12:47:48.18 server  Deadlock Id 216 detected
Deadlock Id 216: detected. 1 deadlock chain(s) involved.

Deadlock Id 216: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0030000100001.005.0000
1
1
15.00
0
Deadlock Id 216: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0030000100001.005.0000
1
1
15.00
0
Deadlock Id 216: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697773 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 216: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697747 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 216: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

01:00000:00105:2005/06/17 12:47:50.68 server  Deadlock Id 217 detected
Deadlock Id 217: detected. 1 deadlock chain(s) involved.

Deadlock Id 217: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0120000100001.001.0000
1
1
11.00
0
Deadlock Id 217: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0120000100001.001.0000
1
1
11.00
0
Deadlock Id 217: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697773 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 217: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697747 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 217: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

01:00000:00105:2005/06/17 12:47:52.18 server  Deadlock Id 218 detected
Deadlock Id 218: detected. 1 deadlock chain(s) involved.

Deadlock Id 218: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0040000100001.00100.0000
1
1
1100.00
0
Deadlock Id 218: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0120000100001.001.0000
1
1
11.00
0
Deadlock Id 218: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697773 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 218: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697747 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 218: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

01:00000:00105:2005/06/17 12:47:53.18 server  Deadlock Id 219 detected
Deadlock Id 219: detected. 1 deadlock chain(s) involved.

Deadlock Id 219: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0100000100001.00485.0000
1
1
1485.00
0
Deadlock Id 219: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0120000100001.001.0000
1
1
11.00
0
Deadlock Id 219: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697773 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 219: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697747 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 219: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

00:00000:00105:2005/06/17 12:47:57.18 server  Deadlock Id 220 detected
Deadlock Id 220: detected. 1 deadlock chain(s) involved.

Deadlock Id 220: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0120000100001.001.0000
1
1
11.00
0
Deadlock Id 220: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 59.9000
1
1
159.90
0
Deadlock Id 220: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697775 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 220: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697747 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 220: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

00:00000:00082:2005/06/17 12:48:04.68 server  Deadlock Id 221 detected
Deadlock Id 221: detected. 1 deadlock chain(s) involved.

Deadlock Id 221: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 37.3000
1
1
137.30
0
Deadlock Id 221: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00104.0000
1
1
1104.00
0
Deadlock Id 221: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697795 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 221: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 221: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

00:00000:00082:2005/06/17 12:48:06.68 server  Deadlock Id 222 detected
Deadlock Id 222: detected. 1 deadlock chain(s) involved.

Deadlock Id 222: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.007.2000
1
1
17.20
0
Deadlock Id 222: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.007.2000
1
1
17.20
0
Deadlock Id 222: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697797 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 222: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 222: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

01:00000:00082:2005/06/17 12:48:08.68 server  Deadlock Id 223 detected
Deadlock Id 223: detected. 1 deadlock chain(s) involved.

Deadlock Id 223: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0100100001.00 19.9000
1
1
119.90
0
Deadlock Id 223: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 70.0000
1
1
170.00
0
Deadlock Id 223: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697798 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 223: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 223: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

01:00000:00082:2005/06/17 12:48:09.68 server  Deadlock Id 224 detected
Deadlock Id 224: detected. 1 deadlock chain(s) involved.

Deadlock Id 224: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0100100001.00 20.8000
1
1
120.80
0
Deadlock Id 224: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00257.2000
1
1
1257.20
0
Deadlock Id 224: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697798 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 224: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 224: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

00:00000:00082:2005/06/17 12:48:10.68 server  Deadlock Id 225 detected
Deadlock Id 225: detected. 1 deadlock chain(s) involved.

Deadlock Id 225: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0030000100001.00 30.0000
1
1
130.00
0
Deadlock Id 225: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0100100001.00 75.2000
1
1
175.20
0
Deadlock Id 225: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697798 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 225: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 225: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

00:00000:00082:2005/06/17 12:48:12.18 server  Deadlock Id 226 detected
Deadlock Id 226: detected. 1 deadlock chain(s) involved.

Deadlock Id 226: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 10.0000
1
1
110.00
0
Deadlock Id 226: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0070000100001.00143.0000
1
1
1143.00
0
Deadlock Id 226: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697799 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 226: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 226: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

00:00000:00105:2005/06/17 12:48:15.68 server  Deadlock Id 227 detected
Deadlock Id 227: detected. 1 deadlock chain(s) involved.

Deadlock Id 227: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0030000100001.00186.0000
1
1
1186.00
0
Deadlock Id 227: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0040000100001.00280.0000
1
1
1280.00
0
Deadlock Id 227: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697748 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.
Deadlock Id 227: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697799 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.

Deadlock Id 227: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

00:00000:00105:2005/06/17 12:48:18.18 server  Deadlock Id 228 detected
Deadlock Id 228: detected. 1 deadlock chain(s) involved.

Deadlock Id 228: Process (Familyid 82 82) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 10.0000
1
1
110.00
0
Deadlock Id 228: Process (Familyid 105 105) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.006.4000
1
1
16.40
0
Deadlock Id 228: Process (Familyid 0, Spid 105) was waiting for a 'shared page' lock on page 5697800 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'exclusive page' lock on it.
Deadlock Id 228: Process (Familyid 0, Spid 82) was waiting for a 'exclusive page' lock on page 5697749 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'shared page' lock on it.

Deadlock Id 228: Process (Familyid 0, 82) was chosen as the victim. End of deadlock information.

01:00000:00082:2005/06/17 12:48:19.18 server  Deadlock Id 229 detected
Deadlock Id 229: detected. 1 deadlock chain(s) involved.

Deadlock Id 229: Process (Familyid 105 105) (suid 5) was executing a INSERT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 51.6000
1
1
151.60
0
Deadlock Id 229: Process (Familyid 82 82) (suid 5) was executing a SELECT command in the procedure 'huxf_insert_by09zyfymx'.
SQL Text: 0010100100001.00 78.3000
1
1
178.30
0
Deadlock Id 229: Process (Familyid 0, Spid 82) was waiting for a 'shared page' lock on page 5697800 of the 'by09zyfymx' table in database 5 but process (Familyid 105, Spid 105) already held a 'exclusive page' lock on it.
Deadlock Id 229: Process (Familyid 0, Spid 105) was waiting for a 'exclusive page' lock on page 5697749 of the 'by09zyfymx' table in database 5 but process (Familyid 82, Spid 82) already held a 'shared page' lock on it.

Deadlock Id 229: Process (Familyid 0, 105) was chosen as the victim. End of deadlock information.

hobbylu

在存储过程中设置set isolation level 0就应该可以解决这个问题

jlhwlw

要怎么设置，我对SYSBASE实在不熟悉，谢谢再次请教

1017of

不是所有业务都适合设置成0，可以先使用行级锁，调整锁升级参数看看，如果还不行，还是要从应用上找找原因

jlhwlw

那该怎么设置呢？我实在不熟悉SYBASE

hobbylu

你先按照我的方法去测试看看，然后看有什么问题,如果没有问题就OK了

jtyhyl

[quote]原帖由 "1017of"]不是所有业务都适合设置成0，可以先使用行级锁，调整锁升级参数看看，如果还不行，还是要从应用上找找原因[/quote 发表：


看好此观点

chuxu

先把出错的几个表改成行锁试试，如果还不行就是应用本身的问题了，