在日常操作中,經(jīng)常會(huì)有不小心被鎖表的情況發(fā)生

一般造成原因有:

• 開發(fā)人員不小心執(zhí)行了 for update 查詢語句后,沒有解鎖
• 不合理代碼中開啟事務(wù)(begin transaction)后,沒有關(guān)閉(close)或回滾(rollback)該事務(wù).

此時(shí)我們必須擁有dba的權(quán)限才能對(duì)被鎖的表進(jìn)行解鎖操作.而鎖表的時(shí)如果突然斷網(wǎng)或斷電那么可能會(huì)報(bào)如下異常:

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 resetat 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 resetat 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 View Code

?

創(chuàng)造鎖表情景:

SELECT * FROM STUDENT FOR UPDATE;

現(xiàn)在查看有哪些表被鎖了

執(zhí)行以下語句必須有oracle管理員權(quán)限,不然會(huì)報(bào)錯(cuò) "ORA-00942:表或視圖不存在"

SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL#FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION SWHERE L.OBJECT_ID　 = O.OBJECT_IDAND L.SESSION_ID = S.SID;

?

解除被鎖定的表

ALTER SYSTEM KILL SESSION '219,59457'; -- 219為SID,59457為SERIAL

繼續(xù)查看哪些表被鎖了

SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL#FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION SWHERE L.OBJECT_ID　 = O.OBJECT_IDAND L.SESSION_ID = S.SID;

　　

?

?

在我自己的mybatis3項(xiàng)目中

如果用select * from tableName where id='XXX' for update 鎖表后不 rollback() 或 commit() ,那么只會(huì)鎖 id = 'XXX' 這一數(shù)據(jù)行

?

查看表被鎖的其它形式語句

SELECT OBJECT_ID,SESSION_ID,SERIAL# AS SERIAL,ORACLE_USERNAME,OS_USER_NAME,S.PROCESSFROM V$LOCKED_OBJECT A, V$SESSION SWHERE A.SESSION_ID = S.SIDAND SYSDATE - S.LOGON_TIME > 0.5

?

其它參考

查詢Oracle正在執(zhí)行的sql語句?

?ORACLE腳本之鎖表時(shí) 查詢誰鎖了表而又引起誰的等待--https://blog.csdn.net/haiross/article/details/17223295

其它相關(guān)語句

查看oracle中的鎖的情況.
SESSION_ID, USERNAME, MODE_HELD, MODE_REQUESTED, OBJECT_NAME, LOCK_TYPE, LOCK_ID
分別是 擁有鎖的SESSION_ID,擁有鎖的USERNAME,鎖的執(zhí)行模式MODE_HELD,鎖的請(qǐng)求MODE_REQUESTED,鎖所在的數(shù)據(jù)庫對(duì)象名,鎖的類型,鎖的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_ID2FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION SWHERE L.SID = S.SIDAND L.ID1 = O.OBJECT_IDAND S.USERNAME = 'bobo'; View Code

?

轉(zhuǎn)載于:https://www.cnblogs.com/whatlonelytear/p/4714244.html

總結(jié)

以上是生活随笔為你收集整理的oracle解除锁表【原】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。