oracle解除锁表【原】
在日常操作中,经常会有不小心被锁表的情况发生
一般造成原因有:
- 开发人员不小心执行了 for update 查询语句后,没有解锁
- 不合理代码中开启事务(begin transaction)后,没有关闭(close)或回滚(rollback)该事务.
此时我们必须拥有dba的权限才能对被锁的表进行解锁操作.而锁表的时如果突然断网或断电那么可能会报如下异常:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Exception in thread "Thread-4" org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: java.sql.SQLException: Io 异常: Connection reset ### The error may exist in file [D:\application\eclipse64ee\workspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp7\wtpwebapps\isc\WEB-INF\classes\com\testdemo\pcis\isc\dao\LockMapper.xml] ### The error may involve com.testdemo.pcis.isc.dao.LockMapper.queryLockTableForUpdate-Inline ### The error occurred while setting parameters ### SQL: SELECT T.C_PK_ID as cId, T.C_IP as cIP,T.C_MEMO as cMemo ,T.T_CRT_TM as tCrtTm FROM WEB_ISC_LOCK T FOR UPDATE ### Cause: java.sql.SQLException: Io 异常: Connection reset at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:128) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) at com.sun.proxy.$Proxy24.queryLockTableForUpdate(Unknown Source) at com.testdemo.pcis.isc.threadpool.MasterSlaveThread.judgeMasterNewCode(MasterSlaveThread.java:73) at com.testdemo.pcis.isc.threadpool.MasterSlaveThread.run(MasterSlaveThread.java:54) Caused by: java.sql.SQLException: Io 异常: Connection reset at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:829) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3476) at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ... 7 more
创造锁表情景:
SELECT * FROM STUDENT FOR UPDATE;
现在查看有哪些表被锁了
执行以下语句必须有oracle管理员权限,不然会报错 "ORA-00942:表或视图不存在"
SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL# FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID;
解除被锁定的表
ALTER SYSTEM KILL SESSION '219,59457'; -- 219为SID,59457为SERIAL
继续查看哪些表被锁了
SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL# FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID;
在我自己的mybatis3项目中
如果用select * from tableName where id='XXX' for update 锁表后不 rollback() 或 commit() ,那么只会锁 id = 'XXX' 这一数据行
查看表被锁的其它形式语句
SELECT OBJECT_ID, SESSION_ID, SERIAL# AS SERIAL, ORACLE_USERNAME, OS_USER_NAME, S.PROCESS FROM V$LOCKED_OBJECT A, V$SESSION S WHERE A.SESSION_ID = S.SID AND SYSDATE - S.LOGON_TIME > 0.5
其它参考
ORACLE脚本之锁表时 查询谁锁了表而又引起谁的等待--https://blog.csdn.net/haiross/article/details/17223295
其它相关语句
查看oracle中的锁的情况.
SESSION_ID, USERNAME, MODE_HELD, MODE_REQUESTED, OBJECT_NAME, LOCK_TYPE, LOCK_ID
分别是 拥有锁的SESSION_ID,拥有锁的USERNAME,锁的执行模式MODE_HELD,锁的请求MODE_REQUESTED,锁所在的数据库对象名,锁的类型,锁的ID
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(REQUEST)) MODE_REQUESTED, O.OWNER || '.' || O.OBJECT_NAME || ' (' || O.OBJECT_TYPE || ')', S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND S.USERNAME = 'bobo';
感觉空虚寂寞,只是因为你无所关注,无处付出。