Oracle 锁详解（lock）

1 概述

2 锁详解

2.1 锁类型

1. DML：data locks，数据锁
   (1) 保护 '数据' 的一致性和完整性
   (2) insert、update、delete
   (3) DML 锁主要有以下两种
       TM 锁（表级锁）：锁住整个 '表'
       TX 锁（事务锁 或 行级锁） ：锁住表中的 '某行'

2. DDL：dictionary locks，字典锁
   (1) 保护 '数据库对象' 的结构，如：表、索引等结构定义
   (2) create、alter 等

3. 内部锁和闩（latch）  
   (1) 保护 '数据库的内部结构'
   (2) latch：低级别、轻量级的锁，获得和释放的速度都快
       lock：可能持续很长时间，通过使用队列，先进先出的方式实现

2.2 锁模式

基本锁模式	简称	能否再加锁	能否读数据	能否改数据
共享锁	Share Locks，即 S 锁	√	√	×
排他锁	Exclusive Locks，即 X 锁	×	√	√

“锁模式” 值越大，所影响的行数越多

锁模式	简称	全称	解释	常用 sql 操作
0	none	lock requested but not yet obtained	已请求锁，但未获得
1	null		查询	select
2	rows_s(ss)	row share lock	行级共享锁 其他对象只能查询这些数据行	Select for update、Lock for update、Lock row share
3	row_x(sx)	row exclusive table lock	行排它表锁 在提交前不允许做 DML 操作	Insert、Update、Delete、Lock row share
4	share(s)	share table lock	共享表锁	Create table、Lock share
5	s/row-s(ssx)	share row exclusive table lock	共享行独占表锁	Lock share row exclusive
6	exclusive(x)	exclusive table lock	排它表锁	Alter table、Drop table、Drop index、Truncate table 、Lock exclusive

2.3 查询

select l.sid,
       l.type,
       (select lt.name
          from v$lock_type lt -- type
         where lt.type = l.type) 锁类型,
       l.lmode,
       decode(l.lmode,
              0,
              null,
              1,
              'null (NULL)',
              2,
              'row-S (SS)',
              3,
              'row-X (SX)',
              4,
              'share (S)', -- s 锁：共享锁
              5,
              'S/Row-X (SSX)',
              6,
              'exclusive (X)' -- x 锁：排他锁
              ) 锁模式
  from v$lock l
 where l.type in ('TM', 'TX') -- 通常关注该两个即可
 order by l.sid;

3 扩展

3.1 select … for update 进阶

博客链接	简述
Oracle for update skip locked 详解	跳过被锁定的记录，锁定未被锁定的记录
Oracle for update [of column] [nowait] 详解	多表连接时，锁定单个表中的某列

3.2 死锁

原因：互相等待对象释放资源。 -- 我让你先走，你让我先走，结果大家都不走

解决：找到 'SID' 和 'SERIAL#'，并执行下列语句
     alter system kill session 'SID, SERIAL#';

’SID’ 和 ‘SERIAL#’ 获取方式

select l.sid         "会话ID",
       s.serial#     "会话序列号",
       p.spid        "会话进程号",
       l.type        "锁类型",
       s.username    "所属用户",
       s.machine     "客户端",
       o.object_name "被锁对象",
       o.object_type "被锁对象类型",
       l.ctime       "被锁时间(S)"
  from v$lock      l, -- addr
       v$session   s, -- saddr 
       dba_objects o, -- object_id 
       v$process   p -- addr 
 where s.sid = l.sid
   and o.object_id = l.id1
   and p.addr = s.paddr
   and l.type in ('TM', 'TX')
-- and s.username = ''
;