大数据-玩转数据-oracle的锁查询及处理

人猿宇宙

已于 2023-08-30 14:14:39 修改

阅读量2.2k

点赞数 1

分类专栏：数据库-玩转数据-Oracle 文章标签： oracle 大数据数据库

于 2023-04-15 16:53:44 首次发布

本文链接：https://blog.csdn.net/s_unbo/article/details/130171479

版权

数据库-玩转数据-Oracle 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

一、Oracel 的锁介绍

oracle造成锁表的情况：

1.1、查看锁的对象视图：

select object_id,session_id,locked_mode from v$locked_object;

1.2、锁的级别

0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁
3：Row-X 行专用(RX)：用于行的修改
4：Share 共享锁(S)：阻止其他DML操作
5：S/Row-X 共享行专用(SRX)：阻止其他事务操作
6：exclusive 专用(X)：独立访问使用。

1.3 常用的sql会造成锁表

1、select … from … for update; 3 级锁
2、insert / update / delete .（DML）… ; 3级锁
3、创建索引的时候也会产生3,4级别的锁
4、主外键约束时 update / delete … ; 可能会产生4,5的锁
5、DDL（alter,drop）语句可能会产生时是6的锁

在锁定中，存在以下两种基本的模式。

排他锁（Exclusive，简称X锁）：一旦用户对某个资源添加了X锁，则其他用户都不能再对该资源添加任何类型的锁，直到该用户释放了资源上的X锁为止。

共享锁（Share，简称S锁）：一旦用户对某个资源添加了S锁，则其他用户都不能在该资源上添加X锁，只能添加S锁，直到该用户释放了资源上的S锁为止。

根据被保护的对象种类的不同，锁定可以分成多种类型，比如由于DDL引起的锁定、DML事务引起的锁定、分布式事务中涉及的锁定等。我们主要应该关注DML事务引起的锁定。

1.4 DML事务锁定的机制

锁定能够保证当某个用户正在更新表里的一行数据时，其他用户不能同时更新相同的数据行，而且也不能删除或修改被更新的表。

锁定分为两种级别：行级别（TX锁）和表级别（TM锁）。

1.4.1 行级锁（TX锁）

假设某个用户（假设为A）发出如下的语句更新一条记录：

update employees set last_name='HanSijie' 
where employee_id=100;

Oracle在对该SQL进行解析以后，找到employee_id为100的记录所在的数据块（假设为58号数据块），并找一个可用的undo数据块，将last_name列上被更新前的旧值放入该undo数据块，然后在数据块头部分配一个ITL槽，在该ITL槽里存放当前的事务ID号、SCN号、所使用的undo数据块的地址，以及当前还未提交的标记等信息。接下来，在58号数据块中，找到被更新的数据行，在其头部设置一个锁定标记，并在头部记录当前事务所使用的ITL槽的槽号。做完这些工作以后，将控制权（也就是光标）返回给用户。该锁定标记说明当前用户在被修改的数据行上已经添加了X锁。

如果这时，另一个用户（假设为N）也对employee_id为100的记录进行修改，则其过程和上面描述的一样，只不过B在对数据行的头部设置锁定标记时，发现该数据行头部已经有一个锁定标记了，说明该记录已经被添加了X锁，于是用户进程N必须等待，等待该X锁被释放。

可以看到，Oracle数据库是在物理层面上实现对数据行的锁定问题。而且锁定一条记录，并不影响其他用户对该记录的读取。比如，如果当前有一个用户（假设为C）发出SQL语句，检索employee_id为100的记录信息，这时服务器进程发现被检索的记录有锁定标记，说明当前该记录已经被其他用户修改了，但是还没提交。于是根据数据行头部记录的ITL槽的槽号，在数据块头部找到该ITL槽，并根据其中记录的undo数据块的地址，找到该undo数据块，将其中所保存的改变前的旧值取出，并据此构建CR（Consistent Read一致性读）块，该CR块中的数据就是被更新的数据块（也就是58号数据块）在更新前的内容。于是根据该CR块的内容，将用户所需要的信息返回给C。

对于Oracle数据库来说，行级锁只有X锁定模式，没有S锁定模式。Oracle的锁定总是尽可能地在最低级别上完成。比如更新数据行时，仅仅是锁定被更新的数据行，并不会锁定同一个数据块中的其他数据行，也不会阻塞其他用户查询被更新的数据行。

1.4.2 表级锁（TM锁）

还是接着上面的例子，这时A用户已经发出了更新employee_id为100的记录的SQL语句。当A还没有提交之前，另外一个用户D发出下面的语句：

drop table employees;

或者是 update 没有走索引

由于用户A还没有提交所做的事务，因此该事务还没有结束，其他用户还不能删除该表，否则A所发出的事务就无法正常结束。为了阻止这时用户D的删除操作，我们能够想到的最直观的方法就是，在执行删除表的命令之前，先依次检查employees表里的每一条记录，查看每一条数据行的头部是否存在锁定标记，如果是，则说明当前正有事务在更新该表，删除表的操作必须等待。

显然，这种方式会引起很大的性能问题，Oracle不会采用这种方式。实际上，当我们在对employees表的数据进行更新时，不仅会在数据行的头部记录行级锁，而且还会在表的级别上添加一个表级锁。那么当D用户要删除表时，发现employees表上具有一个表级锁，于是等待。

通过这种在表级别上添加锁定的方式，我们就能够比较容易并且高效地（因为不需要扫描表里的每一条记录来判断在表上是否有DML事务）对锁定进行管理了。表级锁共具有五种模式，如下所示。

行级排他锁（Row Exclusive，简称RX锁）

当我们进行DML时会自动在被更新的表上添加RX锁，或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下，允许其他的事务通过DML语句修改相同表里的其他数据行，或通过lock命令对相同表添加RX锁定，但是不允许其他事务对相同的表添加排他锁（X锁）。

行级共享锁（Row Shared，简称RS锁）

通常是通过select … from for update语句添加的，同时该方法也是我们用来手工锁定某些记录的主要方法。比如，当我们在查询某些记录的过程中，不希望其他用户对查询的记录进行更新操作，则可以发出这样的语句。当数据使用完毕以后，直接发出rollback命令将锁定解除。当表上添加了RS锁定以后，不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。

共享锁（Share，简称S锁）

通过lock table in share mode命令添加该S锁。在该锁定模式下，不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。

排他锁（Exclusive，简称X锁）

通过lock table in exclusive mode命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能进行查询。

共享行级排他锁（Share Row Exclusive，简称SRX锁）

通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁。

二、死锁的定位方法

通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。

2.1 用dba用户查看死锁

select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

如果有输出的结果，则说明有锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status：状态，active表示被死锁
Machine：死锁语句所在的机器。
Program：产生死锁的语句主要来自哪个应用程序。

2.2、用dba用户查看到被死锁的语句

select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

三、死锁的解决方法

一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

3.1、查找死锁的进程

sqlplus “/as sysdba” (sys/change_on_install)

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

3.2 查看引起锁的语句

select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID, C.SESSION_ID,  B.SERIAL#, C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,
       ''''||C.Session_ID||','||B.SERIAL#||''''
from v$sql A, v$session B, v$locked_object C
where A.HASH_VALUE = B.SQL_HASH_VALUE and
B.SID = C.Session_ID

3.2、kill掉这个死锁的进程：

alter system kill session ‘sid,serial#’; 
#（其中sid=l.session_id）

3.3、如果还不能解决：

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

其中sid用死锁的sid替换

ps -ef|grep spid

其中spid是这个进程的进程号，kill掉这个Oracle进程

(1)锁表查询的代码有以下的形式：

select count(*) from v$locked_object;
select * from v$locked_object;

(2)查看哪个表被锁

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

(3)查看是哪个session引起的

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

(4)杀掉对应进程
执行命令：

alter system kill session'1025,41';

其中1025为sid,41为serial#.

四、综上所诉形成kill语句

SELECT SQ.SQL_TEXT, SQ.SQL_FULLTEXT, A.OBJECT_ID, A.LOCKED_MODE, A.SESSION_ID,C.serial#, B.OBJECT_NAME, P.PID, P.SPID, 
 'ALTER SYSTEM KILL SESSION '''||C.sid|| ', '||C.serial#|| '''; ' 
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B, V$SESSION C, V$PROCESS P, V$SQLAREA SQ
 WHERE A.OBJECT_ID = B.OBJECT_ID
   AND A.SESSION_ID = C.SID(+)
   and c.SQL_ID=sq.SQL_ID(+)
   AND C.PADDR = P.ADDR;

五、行级锁查询处理

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('sys')
AND   l.id1        = o.object_id (+)
AND   l.sid        = s.sid
ORDER BY sid, type;

查看机器、用户、状态、SQL语句、连接工具

SELECT username, machine, type, status,SID,SERIAL#,MODULE,ACTION,EVENT#,EVENT
from v$session T
WHERE username IN（'SYS'）
AND SID IN (SELECT SID
FROM v$lock
WHERE  TYPE='TM');


SELECT a.username, a.machine, a.type, a.status,a.SID,a.SERIAL#,a.PROGRAM,a.MODULE,
a.MODULE,a.ACTION,a.EVENT#,a.EVENT,b.type
from v$session a 
left join  v$lock b on a.sid = b.sid
where a.USERNAME = 'SYS'  AND b.TYPE = 'TM';

在这里插入图片描述

SELECT l.sid, s.blocking_session blocker,s.event,l.type,
l.lmode,l.request,o.object_name,o.object_type,s.SQL_ID,s.ACTION
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('sys')
AND   l.id1        = o.object_id (+)
AND   l.sid        = s.sid
ORDER BY sid, type;

select * from v$sqlarea b where b.SQL_ID = 'd1scr63zq42gf'

人猿宇宙

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
大数据-玩转数据-oracle的锁查询及处理

一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。如果有输出的结果，则说明有锁，且能看到死锁的机器是哪一台。2）用dba用户执行以下语句，可以查看到被死锁的语句。Lockwait：死锁的状态，如果有内容表示被死锁。
复制链接

扫一扫