这段代码会出现死锁么？

Dao层使用hibernate写的，通过java代码执行一段sql：

String sql = "select nextval('paramName') from dual";
List<Object[]> objects = dao.executeQueryBySql(sql, null);

Mysql数据库functiond 定义（其中字段“name”是“sequence”表的索引）：

CREATE DEFINER=`peak`@`%` FUNCTION `nextval`( seq_name VARCHAR(50)) RETURNS int(11)
    DETERMINISTIC
BEGIN    
    DELETE FROM sequence WHERE name=seq_name;  
    INSERT INTO sequence(name) VALUES(seq_name);   
    RETURN currval(seq_name);
    END;

CREATE DEFINER=`peak`@`%` FUNCTION `currval`( seq_name VARCHAR(50)) RETURNS int(11)
    DETERMINISTIC
BEGIN          
DECLARE value INTEGER;          
SET value = 0;           
SELECT id INTO value FROM sequence WHERE name=seq_name;          
RETURN value; 
END;

这段代码是在一个批处理里循环执行的，有时候会抛出异常：
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction，
错误日志的定位也是在“List<Object[]> objects = dao.executeQueryBySql(sql, null);”这一行，
实在想不出原因、
错误日志：
Caused by: java.lang.RuntimeException: org.hibernate.exception.LockAcquisitionException: could not execute query
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate.processFindForSql(BaseDAOWithHibernate.java:875)
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate.access$7(BaseDAOWithHibernate.java:838)
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate$18.doInHibernate(BaseDAOWithHibernate.java:772)
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate$18.doInHibernate(BaseDAOWithHibernate.java:1)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate.executeQueryBySql(BaseDAOWithHibernate.java:770)
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate.executeQueryBySql(BaseDAOWithHibernate.java:779)
at com.sohu.pay.base.gentransno.impl.MysqlKeyElementPool.get(MysqlKeyElementPool.java:27)
at com.sohu.pay.base.gentransno.AbstractKeyElementPool.getKeyElement(AbstractKeyElementPool.java:23)
at com.sohu.pay.base.gentransno.impl.TransnoGeneratorMysql.gen(TransnoGeneratorMysql.java:65)
at com.sohu.peak.investment.account.service.impl.AccountTransRecordManagerImpl.saveRecord(AccountTransRecordManagerImpl.java:85)
... 50 more
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at com.sohu.pay.base.dao.hibernate.BaseDAOWithHibernate.processFindForSql(BaseDAOWithHibernate.java:868)
... 61 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1066)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2076)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1932)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3426)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:488)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3131)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2299)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2722)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 68 more

wendal

1楼•2757天前

    DELETE FROM sequence WHERE name=seq_name;  
    INSERT INTO sequence(name) VALUES(seq_name);   
    SELECT id INTO value FROM sequence WHERE name=seq_name;

删,插,查, 这逻辑就不对吧

玩家19(qq_fe2fb6c7) 2楼•2757天前

这是之前人写的代码，我理解的逻辑是：先删除一行，再insert一行，这一行的id会+1，然后取出id处理成流水号。
呃其实我感觉，这么做应该没问题吧

wendal

3楼•2757天前

改成update+select?

wendal

4楼•2757天前

这是用mysql模拟oracle的序列吧...

玩家19(qq_fe2fb6c7) 5楼•2757天前

貌似是模拟oracle的序列，
不能改成update+selecct，这个表只有ID和NAME两个字段，而且不只一行数据，如果要改update+select ，那得先select max(id) ，再update。
可是我觉得原来的逻辑也不会造成死锁啊......

请先登陆