Oracle 锁表问题查询处理

缺乏、安全感 2023-07-20 12:07 16阅读 0赞

## 数据库操作语句的分类 ##

DDL：数据库模式定义语言，关键字：create  
DML：数据操纵语言，关键字：Insert、delete、update  
DCL：数据库控制语言 ，关键字：grant、remove  
DQL：数据库查询语言，关键字：select

## oracle表在什么情况下会被锁住 ##

**DML锁又可以分为，行锁、表锁、死锁**

行锁：当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。

表级锁：当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行DDL语句影响记录行的更新。事务也可以在进行过程中获得共享锁或排它锁，只有当事务显示使用LOCK TABLE语句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用LOCK TABLE显示的定义一个表级的共享锁(LOCK TABLE具体用法请参考相关文档)。

死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁。  
如事务1在表A行记录\#3中有一排它锁，并等待事务2在表A中记录\#4中排它锁的释放，而事务2在表A记录行\#4中有一排它锁，并等待事务1在表A中记录\#3中排它锁的释放，事务1与事务2彼此等待，因此就造成了死锁。死锁一般是因拙劣的事务设计而产生。  
死锁只能使用SQL下:alter system kill session “sid,serial\#”；或者使用相关操作系统kill进程的命令，如UNIX下kill -9 sid,或者使用其它工具杀掉死锁进程。

**DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁**

排它DDL锁：创建、修改、删除一个数据库对象的DDL语句获得操作对象的 排它锁。如使用alter table语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它DDL锁。

共享DDL锁：需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁。  
如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时，该事务就获得了引用表的共享DDL锁。

分析锁：ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使运行相同语句的应用速度更快。一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型，ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。当一个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语句时，ORACLE重新分析编译此语句。

## Oracle锁表查询和解锁 ##

**背景描述**

程序执行过程中卡住不动，查找了日志发现卡在某个位置，本地测试没发现问题后，猜测是数据库发送了锁表情况。

### 第一步：通过管理员权限用户查询被锁表信息 ###

select 
    sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode 
    from v$locked_object lo,dba_objects ao,v$session sess 
    where ao.object_id=lo.object_id and lo.session_id=sess.sid;

FACE	24	788  	AIFACE_DEALFLAG	WIN-TU6BO56S2TL\Administrator	WORKGROUP\WIN-TU6BO56S2TL	sqlnavigator.exe	2020/3/29 16:36:19	3	                	00007FF78B2D3E20	00007FF78C224450	00007FF7886769B8	00007FF76ED43D50	3
    FACE	45	55498	AIFACE_DEALFLAG	LOCAL SERVICE                	WIN-TU6BO56S2TL          	JDBC Thin Client	2020/4/1 13:39:38 	0	00007FF78C4D5138	00007FF78B2E8330	00007FF7901EEDC8	00007FF787BB4FD0	00              	3

![在这里插入图片描述][20200401142042316.png]

### 第二步：通过拥有管理员权限的用户解除数据库中被锁住的表(SID,SERIAL) ###

通过第一步查出来的信息找到被锁的表之后执行如下语句解锁该表：

alter system kill session 'sid,seial#';

注意：sid和seial\#就是第一步中查询出来的进程号和序列号。  
eg：解除第一步中表的锁

alter system kill session '24,788';
    alter system kill session '45,55498';

## 锁的模式 ##

v$locked\_object中的LOCKED\_MODE字段表示锁的模式，oracle中锁的模式有如下几种:  
0：none  
1：null 空  
2：Row-S 行共享(RS)：共享表锁，sub share  
3：Row-X 行独占(RX)：用于行的修改，sub exclusive  
4：Share 共享锁(S)：阻止其他DML操作，share  
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive  
6：exclusive 独占(X)：独立访问使用，exclusive

数字越大锁级别越高, 影响的操作越多。

1级锁有：Select，有时会在v$locked\_object出现。  
2级锁有：Select for update,Lock For Update,Lock Row Share  
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。  
3级锁有：Insert, Update, Delete, Lock Row Exclusive  
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。  
4级锁有：Create Index, Lock Share  
locked\_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。  
00054, 00000, “resource busy and acquire with NOWAIT specified”  
// \*Cause: Resource interested is busy.  
// \*Action: Retry if necessary.  
5级锁有：Lock Share Row Exclusive  
具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。  
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

还有一条比较实用的sql：

--查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.
    SELECT /*+ ORDERED */ 
     sql_text
      FROM v$sqltext a
     WHERE (a.hash_value, a.address) IN
           (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
                   DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.sid = '67')  /* 此处67 为SID*/
     ORDER BY piece ASC;

--------------------

> 天下英雄出我辈，一入江湖岁月催  
> 我是爱生活的「无间行者」，努力把实践过的解决方案分享给大家  
> 一个赞、一个评论、一个关注，真的好开心，努力没有白费。

--------------------

[20200401142042316.png]: /images/20230528/7d1e282cf035462ca9c3032f292be708.png