oracle锁学习小记

最新推荐文章于 2024-11-15 22:11:31 发布

chan_lewis

最新推荐文章于 2024-11-15 22:11:31 发布

阅读量156

点赞数

分类专栏： oracle 文章标签：数据库

本文链接：https://blog.csdn.net/chan_lewis/article/details/84537926

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

好久没写博客了，使用了印象笔记之后，就没在这里写过，今天整理笔记发现关于oracle锁的问题，和大家一起分享。

我觉得如果不是DBA，只是普通的程序猿，知道查询锁，然后解锁就行了，关于原理啊一大堆理论，Who Care ! 以下是我一个前辈给我的查询，解锁语句，挺好用在此分享：

select t2.username,   
       t2.sid,   
       t2.serial#,   
       t3.object_name,   
       t2.OSUSER,   
       t2.MACHINE,   
       t2.PROGRAM,   
       t2.LOGON_TIME,   
       t2.COMMAND,   
       t2.LOCKWAIT,   
       t2.SADDR,   
       t2.PADDR,   
       t2.TADDR,   
       t2.SQL_ADDRESS,   
       t1.LOCKED_MODE,
       'alter system kill session '''||t2.SID||','||t2.serial#||''';'
  from v$locked_object t1, v$session t2, user_objects t3   
 where t1.session_id = t2.sid   
   and t1.object_id = t3.object_id  order by t2.logon_time;
--查询出来的结果，copy出最后一列，然后执行即可

下边是不知从哪copy来的一堆理论，稍微了解一下还是蛮有用的。

在数据库中有两种基本的锁类型：

排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改；加了共享锁的数据对象可以被其他事务读取，但不能修改。

根据保护对象的不同，Oracle数据库锁可以分为以下几大类：

(1) DML lock（data locks，数据锁）：用于保护数据的完整性；

(2) DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）；

(3) Internal locks 和latches（内部锁与闩）：保护内部数据库结构；

(4) Distributed locks（分布式锁）：用于OPS（并行服务器）中；

(5) PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中。

1）DML锁：

在Oracle中最主要的锁是DML（也可称为data locks，数据锁）锁。DML锁的目的在于保证并发情况下的数据完整性。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了 RS、RX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

模式	描述	解释	SQL操作
0	none
1	Null		select
2	RS(Row-S)	行级共享锁，其他对象只能查询被锁数据	select for update、lock for update、lock table in row share mode
3	RX(Row-X)	行级排它锁，在提交之前不允许做DML操作	insert、update、delete、lock table in row exclusive mode
4	S(Share)	共享锁	lock table in share mode
5	SRX(S/Row-X)	共享行级排它锁	lock table in share row exclusive mode
6	X	排它锁	lock table in exclusive mode

①row lock (TX锁）行级锁，主要用来防止两个事务对同一行的同时修改。TX锁用作为一种排队机制，使得其他会话来等待这个事务执行。行级别上只有这一种锁，当行上有这个锁时，就永远不会在有第二个这个锁。但是一个表上可以在多行上有多个这种锁。如果要在表上的某一行加这个tx锁，那么一定要在这个表上加一个表级锁。

②table lock(TM锁) 表级锁，当有DML操作insert,update,delete,select……for update,lock table时，将在表上加上TM锁，DML操作需要加上表级锁有两个目的：1）为该事务保留对该表的DML操作权限 2）防止有ddl操作改变表的结构。TM锁可以排斥DDL锁（DDL锁中的一种共享ddl锁可以与TM锁共存，但大多数DDL操作并不会用这种锁），这样 DDL操作就无法进行（下面会讲到DDL锁）。但不会影响DML操作。

2）DDL锁：

在DDL操作中会自动为对象加DDL锁，从而保护这些对象不会被其他会话锁修改。例如，如果我执行了一个DDL操作 alter table t,表T上就会加一个排他DDL锁，这个排他DDL锁会防止其他会话得到这个表上的DDL锁和TM锁。

有三种类型的DDL锁

1.排他DDL锁（Exclusive DDL lock):这会防止其他会话得到它们自己的DDL锁或TM（DML)锁。这也表明了，在加上这种类型的DDL锁后，DDL操作期间可以查询一个表，但是无法以任何方式修改这个表的结构和数据。

2.共享DDL锁（Share DDL lock):这些锁会保护锁引用对象的结构，使之不被其他会话修改，但是允许修改数据。

3. 可中断解析锁（Share DDL lock):这些锁允许一个对象（如共享池中缓存的一个查询计划）像另外某个对象注册依赖性。如果在被依赖的对象上执行DDL，ORACEL会查看已经对该对象注册了依赖性的对象列表，并使这些对象无效。因此，这些所是“可中断的”，它们不能防止DDL出现。举个例子：你的会话解析一条语句时，对于该语句引用的每一个对象都会加一个解析锁。加这些锁的目的是：如果以某种方式删除或修改了一个被引用的对象，可以将共享池中已解析的缓存语句置为无效（刷新输出）。

大多数DDL带有一个排他DDL锁。

3）关于后三种锁： Internal locks 和latches（内部锁与闩）； Distributed locks（分布式锁）；PCM locks（并行高速缓存管理锁），我这里也不清楚，目前也没有相关资料。

当数据库发生锁事件之后，我们需要一下SQL查看和解决不必要的锁：

1.相关视图说明

视图名	描述	主要字段说明
v$session	查询会话的信息和锁的信息。	sid,serial#：表示会话信息。 program：表示会话的应用程序信息。 row_wait_obj#：表示等待的对象,和dba_objects中的object_id相对应。 lockwait :该会话等待的锁的地址,与v$lock的kaddr对应.
v$session_wait	查询等待的会话信息。	sid：表示持有锁的会话信息。 Seconds_in_wait：表示等待持续的时间信息 Event：表示会话等待的事件，锁等于enqueue

dba_locks	对v$lock的格式化视图。	Session_id：和v$lock中的Sid对应。 Lock_type：和v$lock中的type对应。 Lock_ID1：和v$lock中的ID1对应。 Mode_held,mode_requested：和v$lock中的lmode,request相对应。
v$locked_object	只包含DML的锁信息，包括回滚段和会话信息。	Xidusn,xidslot,xidsqn：表示回滚段信息。和 v$transaction相关联。 Object_id：表示被锁对象标识。 Session_id：表示持有锁的会话信息。 Locked_mode：表示会话等待的锁模式的信息，和v$lock中的lmode一致。

2.相关查询语句

--查询数据库中的锁
select * from v$lock;
select * from v$lock where block=1;

--查询被锁的对象
select * from v$locked_object;

--查被阻塞的会话
select * from v$lock where lmode=0 and type in ('TM','TX');

--查阻塞别的会话锁
select * from v$lock where lmode>0 and type in ('TM','TX');

--查询数据库正在等待锁的进程
select * from v$session where lockwait is not null;

--查询会话之间锁等待的关系
select a.sid holdsid, b.sid waitsid, a.type, a.id1, a.id2, a.ctime
  from v$lock a, v$lock b
 where a.id1 = b.id1
   and a.id2 = b.id2
   and a.block = 1
   and b.block = 0;

--查询锁等待事件
select * from v$session_wait where event='enqueue';

查找锁住的表和解锁
select b.owner       TABLEOWNER,
       b.object_name TABLENAME,
       c.OSUSER      LOCKBY,
       c.USERNAME    LOGINID,
       c.sid         SID,
       c.SERIAL#     SERIAL
  from v$locked_object a, dba_objects b, v$session c
 where b.object_id = a.object_id
   AND a.SESSION_ID = c.sid;

--通过SID, SERIAL解锁 
--alter system kill session 'SID, SERIAL';