oracle锁

ORACLE TM锁与TX锁联系

理论部分：

1. DML 锁

DML 锁（也称为数据锁）保证多个用户同时访问的数据的完整性。例如，DML 锁可防止两个客户购买在线书商提供的图书的最后一本。DML 锁可防止同时发生冲突的 DML 或 DDL 操作。对用户的数据操纵， Oracle 可以自动为操纵的数据进行加锁，但也可以人为的实施加锁。DML 锁可由一个用户进程以显式的方式加锁，也可通过某些 SQL 语句隐含方式实现。

1.1. TX锁

行锁，也称为 TX 锁，是单行表上的锁。当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。事务发起第一个修改时会得到TX 锁，而且会一直持有这个锁，直至事务执行提交（COMMIT）或回滚（ROLLBACK）。行锁主要用作排队机制，以防止两个事务修改同一行。数据库始终以独占模式锁定已修改的行，以便其他事务在持有锁的事务提交或回滚之前无法修改该行。

1.2. TM锁

TM 锁用于确保在修改表的内容时，表的结构不会改变，例如防止在 DML 语句执行期间相关的表被移除。当用户对表执行 DDL 或 DML 操作时，将获取一个此表的表级锁。当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行 DDL 语句影响记录行的更新。事务也可以在进行过程中获得共享锁或排它锁，只有当事务显示使用 LOCK TABLE 语句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用 LOCK TABLE 显示的定义一个表级的共享锁。TM 锁包括了 SS、 SX、 S、 X 等多种模式，在数据库中用 0－6 来表示。不同的 SQL 操作产生不同类型的 TM 锁

1.3. Enq:tx row lock contention

enq是一种保护共享资源的锁定机制，一个排队机制排它机制。从一个事务的第一次改变直到rollback or commit 结束这个事务，TX等待mode是6，当一个session 在一个表的行级锁定时另一个会话总是等待，一般发生在一些用户插入或更新，而另一个用户同样也在插入或更新这同一批数据时发生。这种类型的等待通常就是eventenq：TX-rowlockcontention.解决方法是让第一个会话commit or rollback 结束这个事务。如果tx等待事件中mode如果是4，这种一般发生在同时更新一个数据块上的数据，但不一定是同一条记录，这就是热块。对于这种enq:TX - row lock等待事件，如果mode=6，那么唯一的解决方法就是kill 会话或者调整业务逻辑。

2. 锁相关的视图

2.1 V$lock视图

ADDR 锁状态对象的地址

KADDR 锁地址

SID         会话保持或获取锁的标识符

TYPE        用户或系统锁定的类型

ID1         锁定标识符

ID2     锁定标识符

LMODE 会话保持锁定的锁定模式：

REQUEST 进程请求锁定的锁定模式：

CTIME     自授予当前模式以来的时间

BLOCK 指示有问题的锁定是否阻止其他进程。可能的值为：

0- 锁未阻止任何其他进程

1- 锁正在阻止其他进程

2- 锁未阻止本地节点上任何阻塞的进程，但它可能会也可能不会阻塞远程节点上的进程。

2.2 Locked_object视图

XIDUSN                 撤消段编号

XIDSLOT                 插槽编号

XIDSQN                 序列号

OBJECT_ID             对象ID

SESSION_ID             会话ID

ORACLE_USERNAME           oracle用户名

OS_USER_NAME         操作系统用户名

PROCESS                 操作系统进程 ID

LOCKED_MODE             锁定模式

ID1对应视图V$TRANSACTION中的XIDUSN字段（Undo segment number：事务对应的撤销段序列号）和XIDSLOT字段（Slot number：事务对应的槽位号）。其中ID1的高16位为XIDUSN，低16位为XIDSLOT。

ID2对应视图V$TRANSACTION中的XIDSQN字段（Sequence number：事务对应的序列号）。

2.3 DBA_LOCK视图

DBA_LOCK列出数据库中持有的所有锁或闩锁，以及所有未完成的锁或闩锁请求。

SESSION_ID    会话持有或获取锁

LOCK_TYPE    锁类型

MODE HELD    锁定模式

MODE REQUESTED  请求锁定模式

LOCK_ID1        特定于类型的锁标识符，第 1 部分

LOCK_ID2        特定于类型的锁标识符，第 2 部分

LAST_CONVERT    最后一次转换

BLOCKING_OTHERS  锁当前是否阻止了其他锁

实验部分：

模拟出enq:tx row lock等待事件:

创建一个表并插入两行数据：

SQL> Create table pipi(id varchar(10),age varchar(10));
Table created
SQL> insert into pipi values('pipi','3');
1 row created.
SQL> insert into pipi values('zhegu','3');
1 row created.

提交更新结果以及查询更新结果：

SQL> commit;
Commit complete.
SQL> select * from pipi;
ID         AGE
---------- ----------
pipi       3
zhegu      3

 会话一：

执行update pipi set age=’12’;

SQL> update pipi set age='12';
2 rows updated.

会话二：

执行update pipi set age=’9’ where id=’pipi’;

发现会话2卡住

开启会话3：

首先在v$lock视图中相应的sid、锁类型和锁等级

SQL>  select sid,type,lmode,ctime from gv$lock where type='TM';
       SID TY      LMODE      CTIME
---------- -- ---------- ----------
        19 TM          3        400
      1353 TM          3        522

SQL> select sid,type,lmode,ctime from gv$lock where type='TX';
       SID TY      LMODE      CTIME
---------- -- ---------- ----------
        19 TX          0        425
      1353 TX          6        547

查询对应的等待事件：

SQL> select sid, seq#, state, seconds_in_wait,event
  2  FROM v$session_wait WHERE event='enq: TX - row lock contention';
       SID       SEQ# STATE               SECONDS_IN_WAIT
---------- ---------- ------------------- ---------------
EVENT
----------------------------------------------------------------
        19         24 WAITING                         894
enq: TX - row lock contention

由此enq：TX -row lock contention等待事件模拟成功

查询是哪个会话id阻塞了另外一个会话：

第一种方法：在Gv$session 里查找final_blocking_session参数：

SQL> select inst_id, final_blocking_session, sid, serial#, final_blocking_session_status
from gv$session where blocking_session is not null order by blocking_session;  
   INST_ID BLOCKING_SESSION        SID    SERIAL# BLOCKING_SE
---------- ---------------- ---------- ---------- -----------
         1             1353         19      56206 VALID

 blocking_session_status有几个值：其中valid代表有一个会话阻塞该会话，noholder代表没有会话阻塞该会话，no in wait代表会话不在等待中，unkonwn代表未知

由查询的内容可知：1353阻塞了19会话

第二种方法：在gv$lock和gv$locked_object查找：

先查找出哪些会话阻塞了其他会话：select sid,block from gv$lock where block='1';

SQL> select sid,block from gv$lock where block='1';
 
       SID      BLOCK
---------- ----------
      1353          1

查询被阻塞会话：select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=('&sid'));

SQL> select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=('&sid'));
Enter value for sid: 1353
old   1: select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=('&sid'))
new   1: select object_id,session_id from v$locked_object where object_id in (select object_id from v$locked_object where session_id=('1353'))
 OBJECT_ID SESSION_ID
---------- ----------
114363         19
114363       1353

 确定19会话被1353阻塞。

第三种方法：dba_waiters和dba_blockers查找(仅仅试用与阻塞和被阻塞在同一节点)

查找阻塞会话的会话：

SQL> select * from dba_blockers;
HOLDING_SESSION     CON_ID
--------------- ----------
           1353          1
SQL> select waiting_session,holding_session from dba_waiters;
WAITING_SESSION HOLDING_SESSION
--------------- ---------------
             19            1353

确定了阻塞会话通过sid找到对应的sql_id:

select inst_id,

sid, serial#, wait_class, sql_id from   gv$session

where blocking_session IS NOT NULL and sid=('&sid');

通过sql_id查找出相应的sql语句；确保不会误杀会话

杀死会话：alter system kill session ‘sid,serial#,@inst_id’;

其中sid是整个会话生命周期的一个唯一标识符，sid+serial#是整个实例生命周期的唯一标识符(避免误杀情况出现)，inst_id代表当前节点号(避免误杀情况出现)。

SQL> alter system kill session '19,56206,@1';

System altered.

被阻塞会话执行commit；并查询更新后的结果：

补充：若想查询一个被阻塞会话的阻塞源头：可采用gv$session视图中的final_blocking相关参数

相应的sql语句：

select inst_id, final_blocking_session, sid, serial#, final_blocking_session_status from gv$session where blocking_session is not null order by blocking_session;