Oracle 锁详解（lock）

文章目录

• 1 概述
• 2 锁类型
• • 2.1 按用户与系统划分：隐式锁和显式锁
  • 2.2 按锁级别划分： 共享锁和排他锁
  • 2.3 按保护对象划分：DML、DDL、SYSTEM
• 3 扩展
• • 3.1 select ... for update 进阶
  • 3.2 死锁

1 概述

1. 加锁的目的：实现对 '共享资源' 的 '并发控制'
   例如，'同时' 更新 '同一条记录'
       若不加锁，则更新无先后顺序，得到的结果集可能并非想要的
       若加了锁，则更新有先后顺序，能得到想要的结果集   
2. 锁的分类：从不同的角度，有不同的分类方法
3. 相关视图
   (1) select * from v$locked_object; -- 查询 dml 锁信息
   (2) select * from dba_ddl_locks;   -- 查询 ddl 锁信息
   (3) select * from v$lock;          -- 查询所有锁信息

 
 

2 锁类型

2.1 按用户与系统划分：隐式锁和显式锁

1. 隐式锁（自动锁）: Oracle 系统自行维护，无需人为干预，常见有
   (1) DDL 语句：create、alter 等
   (2) DML 语句：insert、update、delete、select ... for update   
2. 显式锁: 人为手动添加锁，lock table ... [nowait]
   (1) lock table <table_name> in row share mode nowait;     -- 2：rs
   (2) lock table <table_name> in share update mode nowait;  -- 2：rs
   (3) lock table <table_name> in row exclusive mode nowait; -- 3：rx
   (4) lock table <table_name> in share mode nowait;         -- 4：s
   (5) lock table <table_name> in share row exclusive mode;  -- 5：srx
   (6) lock table <table_name> in exclusive mode nowait;     -- 6：x

 
 

2.2 按锁级别划分： 共享锁和排他锁

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

图表解释说明：

1. 能否再加锁
   (1) 如果 事务T 对 数据A 加上 '共享锁' 后，则其它事务只能对 A 再加 共享锁，不能加 排他锁
   (2) 如果 事务T 对 数据A 加上 '排他锁' 后，则其它事务不能再对 A 加任任何类型的锁
2. 能否读取和修改数据 
   (1) 获得 '共享锁' 的事务只能读数据，不能修改数据 
   (2) 获准 '排他锁' 的事务既能读数据，又能修改数据

 
 

测试数据：

create table scott.stu_info (
  sno   number(2),
  sname varchar2(30)
);
insert into scott.stu_info(sno, sname) values(1, 'a');
commit;

 
 

演示：准备三个窗口, session 1 和 2 加锁，3 验证
session 1:

lock table scott.stu_info in share mode nowait; -- 共享锁，显示锁定
-- 同理，排他锁，显示锁定
-- lock table scott.stu_info in exclusive mode nowait;

 
 

session 2:

lock table scott.stu_info in share mode nowait;
-- 共享模式 share    更新失败
-- 排他模式 exclusive 更新成功
update scott.stu_info t set t.sname = 'b' where t.sno = 1;

 
 

session 3:

select * from v$locked_object;

 
 

2.3 按保护对象划分：DML、DDL、SYSTEM

1. DML Locks: 保护 '数据'，如：表锁锁定整个表、行锁锁定选中的行
   (1) TM 锁（表级锁）
   (2) TX 锁（事务锁 或 行级锁）   
2. DDL Locks: 保护 '对象的结构'，如：表、视图的定义
3. System locks: 保护数据库 '内部的结构'，如：数据文件、闩

 
 

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

详细的解释可以参考 Oracle 官方文档 v$locked_object 中的 locked mode 链接（Oracle Database Concepts …）

select t.locked_mode, t.* from v$locked_object t;

锁模式	简称	全称	解释	常用 sql 操作
0	none	lock requested but not yet obtained	已请求锁，但未获得
1	null		查询	select
2	rows_s(ss)	row share lock	行级共享锁
3	row_x(sx)	row exclusive table lock	行排它表锁	insert、update、delete
4	share(s)	share table lock	共享表锁
5	s/row-s(ssx)	share row exclusive table lock	共享行独占表锁
6	exclusive(x)	exclusive table lock	排它表锁	alter table、drop table、drop index、truncate table

lock table 的方式，同上 “2.1 按用户与系统划分：隐式锁和显式锁”
比较少用，故不在此赘叙

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#';

 
 

 
 
1
2
3
4

’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')  -- 只需要关注 TM / TX 锁即可
-- AND s.username = ''
;