next key与'shared row' 'range'的 死锁问题，

littlezgj

00:00000:00035:2006/08/10 09:13:38.29 server  Deadlock Id 47 detected
Deadlock Id 47: detected. 1 deadlock chain(s) involved.

Deadlock Id 47: Process (Familyid 115, 115) (suid 7) was executing a INSERT command at line 1.
SQL Text: ^A ^D1100^D1100^A ^A1^A ^A ^D划款^A ^A ^A ^A ^A0
Deadlock Id 47: Process (Familyid 35, 35) (suid 7) was executing a INSERT command at line 1.
SQL Text:  ^A0^A ^H70000.00^A ^A ^A ^D1100^D2900^A ^A0^A0^A ^D货款^A1^A ^A0^A ^A
7
Deadlock Id 47: Process (Familyid 0, Spid 35) was waiting for a 'next key' lockon row 0 page 6069161 of the 'dedja' table in database 6 but process (Familyid 115, Spid 115) already held a 'shared row' 'range' lock on it.
Deadlock Id 47: Process (Familyid 0, Spid 115) was waiting for a 'exclusive intent' lock on the 'dedja' table in database 6 but process (Familyid 35, Spid 35) already held a 'exclusive intent' lock on it.

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

next key锁、'shared row' 'range' 锁是怎么产生的呢？
两个插入操作怎么会造成死锁呢？

chuxu

是不是有在事务隔离级为3的情况下，一个语句先update，如果没有记录就insert ?

1017of

what's your isolation level?

littlezgj

原因可能查到了
再isql里面select @@isolation是1
但是应用程序是cp程序，cpre编译中自动会加上隔离级别为3的

但是从上面日志上看，死锁是两个insert插入造成的，为什么会有这种情况呢？？

zhhui2000

高版本的SYBASE是有这种问题,我上次在12.0上没问题的程序,在12.5.3就不行。和SYBASE公司的咨询了很长时间，没有别的办法，只有降低隔离级别。
CPRE有个参数的。

littlezgj

我查了，，-r参数可以，，
-r
disables repeatable reads. If you do not use this option, a set transaction
isolation level 3 statement is generated and will be executed during connect
statements.
但是加上以后，从程序中输出来的exec sql select @@isolation，
怎么还是3呢？？

难道非得在程序用exec sql set transaction isolation 1
吗？