oracle锁的使用

一、查询使用

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

二、锁的类型

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

查询是否有锁，

方法一：通过管理员权限用户查询被锁表信息

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
 from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
 and t1.object_id = t3.object_id
 order by t2.logon_time;

方法二：’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 = ''
;

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 扩展

死锁

死锁原因：互相等待对象释放资源。 -- 我让你先走，你让我先走，结果大家都不走
解决办法：找到 'SID' 和 'SERIAL#'，并执行下列语句
alter system kill session 'SID, SERIAL#';

 

详细可见：https://blog.csdn.net/m0_37253968/article/details/120408685
　　　　　http://t.zoukankan.com/smellpawn-p-10799174.html