oracle解除锁表【原】

2022-10-19 09:26:12

在日常操作中,经常会有不小心被锁表的情况发生

一般造成原因有:

开发人员不小心执行了 for update 查询语句后,没有解锁
不合理代码中开启事务(begin transaction)后,没有关闭(close)或回滚(rollback)该事务.

此时我们必须拥有dba的权限才能对被锁的表进行解锁操作.而锁表的时如果突然断网或断电那么可能会报如下异常:

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正在执行的sql语句

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

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';

码农公寓