MySQL获取锁失败求解-Lock wait timeout exceeded; try restarting transaction

[rchmin]

今天程序遇到mysql执行update语句时获取锁超时的问题，现象是update某条记录时报如下错误，而同张表中的其他记录都可以正常update，而且是必现，根据错误信息，猜测问题可能是某次执行update操作时因为某些原因导致改事务（Spring管理的事务）未commit，也未rollback，所以导致该记录行锁一直未释放。
现在我还搞不清楚几个问题，求各位大神指点：
1、我现在如何查看当前数据库中加了哪些行锁，如何手动释放这些锁？
2、为何autocommit = on 的时候会出现这种现象？
3、有什么方法可以避免出现这种行锁未释放的情况？


错误堆栈信息如下：
2015-12-23 17:49:19:ERROR HSFBizProcessor-8-thread-40 com.comm.spring.aop.LogInterceptor - Caused by: org.springframework.dao.CannotAcquireLockException:
### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve com.biz.po.person.TPersonMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update t_person SET gender = ?, location_id = ?, birthdate = ?, name = ?, mobile = ?, id_number = ? where id = ?
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:261)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy40.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:254)
at com.comm.dao.mybatis.impl.DaoHelperImpl.update(DaoHelperImpl.java:309)
at com.biz.service.remotevideo.impl.RemotePatientOrderServiceImpl.updateRemotePatientInfo(RemotePatientOrderServiceImpl.java:282)
at sun.reflect.GeneratedMethodAccessor358.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at com.comm.spring.aop.LogInterceptor.invoke(LogInterceptor.java:55)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at com.comm.spring.aop.LogInterceptor.invoke(LogInterceptor.java:55)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at com.comm.spring.aop.ServiceInterceptor.invoke(ServiceInterceptor.java:51)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at com.comm.spring.aop.ServiceInterceptor.invoke(ServiceInterceptor.java:51)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy81.updateRemotePatientInfo(Unknown Source)
at com.biz.service.remotevideo.impl.RemoteVideoOrderServiceImpl.saveVideoOrder(RemoteVideoOrderServiceImpl.java:166)
at sun.reflect.GeneratedMethodAccessor332.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)

[文修]

楼主你好 innodb引擎表的锁可以可以这样看：

mysql> desc information_schema.INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |
| lock_trx_id | varchar(18)         | NO   |     |         |       |
| lock_mode   | varchar(32)         | NO   |     |         |       |
| lock_type   | varchar(32)         | NO   |     |         |       |
| lock_table  | varchar(1024)       | NO   |     |         |       |
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.10 sec)

至于释放锁的问题：读锁在第一个SQL语句就释放，写锁在事物完成后就会释放，我暂时不知道如何手动释放 autocommit=0代表的是必须手动commit，才能提交事物， 而autommit=1代表的是事物一结束，就自动提交事物 以 DBA角色,查看当前数据库里锁的情况可以用如下 SQL语句： select object_id,session_id,locked_mode from v$locked_object; select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time; 如果有长期出现的一列,可能是没有释放的锁,可以用下面 SQL语句杀掉长期没有释放非正常的 锁： alter system kill session ‘sid,serial#’; 如果出现了锁的问题,某个 DML操作可能等待很久没有反应，出现这种情况不要用 OS系统命 令$kill process_num或者 $kill -9 process_num来终止用用户连接等连接操作，因为一个用户进程 可能产生一以上锁,杀 OS进程并不能彻清除锁的问题.记得在数据库级别用 alter system kill session ‘sid,serial#’;杀掉不正常的锁。