一个简单的存储过程产生死锁

由于系统从Oracle数据库迁移成MsSQL,数据表的主键由程序控制产生,不想过多的改程序,于是我把所有表的主键保存在一个数据表里,表结构很简单: 
create table my_seq( 
  sequence_name varchar(50) not null, 
  sequence_value int(4) 
) 


自定义存储过程如下: 

SQL code 
CREATE PROCEDURE  GET_NEXTVAL
 
    @sequence_name  varchar(200),
    @sequence_value INT output

AS
    BEGIN 
    -- DECLARE @sequence_value INT;
    SET NOCOUNT ON
    set @sequence_value = -1;
              
    begin  TRANSACTION 
       update my_seq 
          SET  sequence_value=sequence_value+1 
             WHERE  sequence_name=@sequence_name ;
      select  @sequence_value=sequence_value from my_seq  WHERE  sequence_name=@sequence_name ;
    COMMIT   TRANSACTION 
       
    SET NOCOUNT OFF

    RETURN @sequence_value
    END
GO




存储过程做的事主要是:把键值加1后,马上更新数据库里的数据,最后返回处理后的下一数值. 

应用系统运行一段时间会出现如下异常: 
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call dbo.GET_NEXTVAL(?, ?)}]; SQL state [40001]; error code [1205]; [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]事务（进程 ID  58）与另一个进程已被死锁在  lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]事务（进程 ID  58）与另一个进程已被死锁在  lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。 
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]事务（进程 ID  58）与另一个进程已被死锁在  lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。 


网络上找到很多类似这样的问题,但我还是没有找到解决的办法.

42 个解决方案

#1

http://blog.csdn.net/arrow_gx/archive/2009/03/19/4004938.aspx
http://blog.csdn.net/arrow_gx/archive/2008/06/01/2501368.aspx
锁的概念级例程分析，也许对楼主有点用处

另外，修改一下存储过程，
强制 update 使用行级锁ROWLOCK，缩小锁的粒度， 
select 使用 nolock ，试试这样能否解决你的问题

CREATE PROCEDURE  GET_NEXTVAL 
    @sequence_name  varchar(200), 
    @sequence_value INT output 

AS 
    BEGIN 
    -- DECLARE @sequence_value INT; 
    SET NOCOUNT ON 
    set @sequence_value = -1; 
              
    begin  TRANSACTION 
      update my_seq with(ROWLOCK)
          SET  sequence_value=sequence_value+1 
            WHERE  sequence_name=@sequence_name ; 
      select  @sequence_value=sequence_value 
      from my_seq with(nolock) 
      WHERE  sequence_name=@sequence_name ; 
    COMMIT  TRANSACTION 
      
    SET NOCOUNT OFF 

    RETURN @sequence_value 
    END 
GO

#2

感觉是事务和事务冲突导致的锁，这样你在这里加上一句；

CREATE PROCEDURE  GET_NEXTVAL 

    @sequence_name  varchar(200), 
    @sequence_value INT output 

AS 
    BEGIN 
    -- DECLARE @sequence_value INT; 
    SET NOCOUNT ON 
    set @sequence_value = -1; 
    wait delay '00:00:00.1'       
    begin  TRANSACTION 
      update my_seq(rowlock) 
          SET  sequence_value=sequence_value+1 
            WHERE  sequence_name=@sequence_name ; 
      select  @sequence_value=sequence_value from my_seq(nolock)
          WHERE  sequence_name=@sequence_name ; 
    COMMIT  TRANSACTION 
      
    SET NOCOUNT OFF 

    RETURN @sequence_value 
    END 
GO 

#3

关注

#4

学习一下

#5

先试下arrow_gx的方法,不过"select 使用 nolock"我担心会读脏数据.

#6

ORACLE没有用过,继续关注

#7

如果存储过程就如楼主写的这个简单的话，建议把事务去掉试试。

#8

加这句就好了

select  @sequence_value=sequence_value from my_seq(nolock)
          WHERE  sequence_name=@sequence_name ; 

#9

存储过程不用动。

TRY:

CREATE CLUSTERED INDEX PK_IDX ON my_seq(sequence_name)
GO

#10

ding

#11

引用 5 楼 lengue 的回复:

先试下arrow_gx的方法,不过"select 使用 nolock"我担心会读脏数据.

呵呵，担心这个的话， select  也指定使用 ROWLOCK 行级锁好了

另外，在 sequence_name  字段建立索引，如果这个字段是唯一的话，直接设置成主键，对查询性能可以提高很多的

#12

顶起

#13

学习了 

#14

up

#15

死锁是4种情况都满足，你打破其中一个就不死锁了

#16

mark

#17

学习

#18

学习  学习

#19

很不错  顶你一个

#20

Mark!

#21

MARK~~

#22

 回帖是一种美德！每天回帖即可获得 10 分可用分

#23

学习一下啊

#24

这是一个并发性的问题，不应该仅仅从解决死锁这个角度考虑，否则即便暂时解决了死锁，可能也会出现其他的比如数据错误
我认为如果是应该再调用这个过程的代码加控制,每一个时刻保证对一个表(sequence_name )，只能有一个进程访问，不通表才能同时访问
或者对每个事务设置优先级，不过性能可能不如前者了

#25

主要是学习

#26

主要是蹭分

#27

先试下arrow_gx的方法,不过"select 使用 nolock"我担心会读脏数据.

#28

不为得分。

#29

xue xi  ing 

#30

guan zhu yi xia ..

#31

up

#32

答题的还没1/3呢.20多个都是顶的....嗨....

#33

该回复于2009-06-04 11:52:17被版主删除

#34

引用 24 楼 Logson 的回复:

这是一个并发性的问题，不应该仅仅从解决死锁这个角度考虑，否则即便暂时解决了死锁，可能也会出现其他的比如数据错误 
我认为如果是应该再调用这个过程的代码加控制,每一个时刻保证对一个表(sequence_name )，只能有一个进程访问，不通表才能同时访问 
或者对每个事务设置优先级，不过性能可能不如前者了

我给方法加同步(synchronized)了也没用,还是报同样的异常.

#35

这么简单的过程也能死锁？不用强制什么锁吧

#36

可以提高一下效率
update my_seq 
     SET  @sequence_value = sequence_value+1,sequence_value=sequence_value+1 
            WHERE  sequence_name=@sequence_name ; 
      
产生锁的话，显然是你运行此存储过程太频繁了，有些程序不是你想不要多改就能不改的
当然的设计显然是自己在制造瓶颈！

#37

Mark

#38

发现一个很低级的问题,我用工具把数据库从Oracle迁移到MsSQL的,重新检查了下发现MsSQL上的主键唯一约束有的没有生成.加上后,再没有发现那个异常了.

#39

 Up 学习!!

#40

该回复于2010-09-06 13:59:33被版主删除

#41

学习了

#42

学习了,用加索引的方法解决了问题, 非常感谢 Garnett_KG