Oracle LOCK 内 部 机 制 中 国 Oracle 用 户 组 作 者 : 刘 盛 (Leonarding) http://www.acoug.org 版 本 发 布 时 间 1.0 2014/02/28 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 1
目 录 1 分 别 模 拟 insert,update 和 delete 造 成 阻 塞 的 示 例... 26 2 模 拟 RI 锁 定 导 致 阻 塞 的 场 景, 并 分 析 v$lock 相 应 的 锁 定 信 息, 给 出 SQL 演 示... 26 3 自 己 构 想 一 个 使 用 手 工 锁 定 解 决 一 种 业 务 需 求 的 场 景, 并 给 出 SQL 演 示... 26 4 给 出 从 mode 2-6 的 TM 锁 相 互 间 的 互 斥 示 例... 26 5 给 出 一 个 导 致 死 锁 的 SQL 示 例... 26 6 总 结... 26 Leonarding 的 个 人 简 介... 26 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 2
摘 要 :Oracle 锁 在 我 一 开 始 接 触 的 时 候 会 有 一 种 高 深 莫 测 的 感 觉, 就 像 是 遥 远 的 外 星 人 看 不 见 摸 不 着 但 是 能 感 觉 到, 我 在 实 际 的 工 作 中 就 遇 到 过 ORA-00054: resource busy acquire with nowait specified( 资 源 正 忙, 指 定 以 nowait 方 式 获 取 资 源 例 如 select * from leo where number>1 for update nowait; 如 果 不 想 让 其 他 会 话 继 续 等 待, 可 以 用 nowait 方 式 获 得 一 个 通 知 而 无 需 等 待 下 去 ) 错 误 不 能 插 入 表, 当 时 知 道 是 被 锁 定 了, 根 据 V$LOCK 也 定 位 出 阻 塞 的 会 话 了, 但 不 知 道 如 何 长 久 的 解 决 它, 究 其 原 因 就 是 不 清 楚 内 部 机 制 与 释 放 原 理, 下 面 根 据 例 子 来 揭 开 锁 的 面 纱, 走 进 Oracle 锁 的 世 界 1 模 拟 Insert/Update/Delete 造 成 阻 塞 LEO1@LEO1> create user leo2 identified by leo2 default tablespace leo1; // 新 创 建 一 个 LEO2 用 户 User created. LEO1@LEO1>grant connect,resource to leo2; // 授 予 基 本 权 限 Grant succeeded. LEO1@LEO1> select owner,table_name,tablespace_name from dba_tables where owner='leo1'; OWNER TABLE_NAME TABLESPACE_NAME -------- -------- LEO1 LEO_WAGE LEO1 LEO1@LEO1> create table t1 (id int primary key); // 创 建 t1 表, 设 置 id 列 为 主 键 Table created. LEO1@LEO1> insert into t1 values(1); 1 row created. LEO1@LEO1> select * from t1; ID 1 LEO2@LEO1> insert into leo1.t1 values(1); // 当 没 有 提 交, 在 插 入 同 样 的 values 时 就 发 生 了 对 会 话 的 阻 塞,hang 在 这 里 不 能 前 进 LEO1@LEO1> commit; // 必 须 提 交 后, 阻 塞 才 终 止, 也 就 是 说 commit 可 以 释 放 阻 塞 Commit complete. LEO2@LEO1> insert into leo1.t1 values(1); // 因 为 已 经 有 了 1 值, 故 违 反 了 主 键 约 束 insert into leo1.t1 values(1) * ERROR at line 1: http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 3
ORA-00001: unique constraint (LEO1.SYS_C0010817) violated 分 享 知 识 收 获 快 乐 http://www.acoug.org update 锁 阻 塞 LEO1@LEO1> select * from t1; ID 1 2 100 LEO1@LEO1> update t1 set id=200 where id=100; // 更 新 一 行 没 有 提 交, 没 有 提 交 的 事 物 对 别 人 是 不 可 见 的, 但 在 物 理 块 上 真 真 切 切 的 修 改 了, 他 人 只 能 访 问 undo 回 滚 段 中 镜 像 1 row updated. LEO2@LEO1> update leo1.t1 set id=300 where id=100; // 我 们 在 会 话 leo2 上 也 更 新 同 一 个 表 里 的 同 一 行, 此 时 hang 住 了 不 动 了, 因 为 2 个 会 话 在 争 用 同 一 条 记 录 的 修 改 权 // 一 般 影 响 业 务 性 能 的 就 TM and TX 锁 SID TY ID1 ID2 LMODE REQUEST BLOCK -- 138 TM 73449 0 3 0 0 138 TX 196615 912 6 0 1 156 TM 73449 0 3 0 0 156 TX 196615 912 0 6 0 SID: 会 话 id 号 TYPE: 锁 的 类 型 ID1: 会 话 操 作 对 象 的 id 号 ID2:ID1+ID2 定 位 回 滚 段 上 的 一 个 地 址 ( 即 修 改 之 前 数 据 镜 像 地 址 ), 由 于 138 和 156 会 话 是 一 样 的 说 明 指 向 的 是 同 一 个 地 址, 换 句 话 说 操 作 的 是 同 一 行 数 据 LMODE: 锁 模 式, 不 同 的 数 字 代 表 不 同 的 锁 模 式 例 如 0 现 在 没 有 申 请 到 锁 3 共 享 锁 模 式 ( 段 级 共 享 锁 ) 6 排 他 锁 模 式 锁 的 级 别 越 高 限 制 越 多 REQUEST: 目 前 会 话 没 有 锁, 正 在 申 请 的 锁 模 式 例 如 0 没 有 正 在 申 请 的 锁, 说 明 已 经 有 锁 了 6 现 在 正 在 申 请 6 号 锁, 目 前 因 为 没 有 才 申 请 BLOCK: 当 前 正 在 阻 塞 几 个 会 话 例 如 1 当 前 正 在 阻 塞 一 个 会 话 2 当 前 正 在 阻 塞 两 个 会 话 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 4
锁 的 实 质 : 是 维 护 一 个 事 务 完 整 性 的, 锁 的 信 息 是 数 据 块 的 一 个 属 性, 是 物 理 的, 并 不 是 逻 辑 上 属 于 某 个 表 或 者 某 几 行 的 LEO1@LEO1> select distinct sid from v$mystat; // 这 个 会 话 当 前 id 是 138, 我 们 怎 么 区 分 呢?=> LEO1 用 户 =138 LEO2 用 户 =156 SID 138 LEO1@LEO1> select object_name from dba_objects where object_id=73449; //138 会 话 操 作 的 对 象 是 T1 表 OBJECT_NAME T1 说 明 :138 会 话 在 T 表 上 加 了 TM 和 TX 锁,TM 锁 模 式 为 3( 共 享 锁 ) TX 锁 模 式 6( 排 他 锁 ), 目 前 TX 锁 正 在 阻 塞 一 个 会 话 ( 就 是 156 会 话 ) 156 会 话 就 是 当 前 被 阻 塞 的 会 话,156 会 话 操 作 对 象 也 是 T1 表 (ID1 都 一 样 的 ),TM 锁 模 式 也 为 3( 共 享 锁 就 是 有 几 个 会 话 就 可 以 创 建 几 个 共 享 锁, 同 时 存 在 ),TX 现 在 还 没 有 申 请 到 锁, 正 在 申 请 6 号 锁, 而 这 个 6 号 锁 就 是 138 会 话 所 持 有 的 ( 因 为 2 个 会 话 操 作 的 是 同 一 行 数 据 ) LEO1@LEO1> select sid,event from v$session_wait where sid in (138,156); // 从 会 话 等 待 视 图 上 可 以 看 出, 有 哪 些 会 话 由 于 什 么 原 因 导 致 等 待 事 件 不 能 前 进 SID EVENT ---- 138 SQL*Net message to client 156 enq: TX - row lock contention 156 会 话 由 于 TX 锁 争 用 原 因 导 致 hang 住 不 能 前 进,enq=enqueues 队 列 锁 ( 通 常 和 业 务 有 关, 为 了 保 护 业 务 的 锁 ) 小 结 : 现 在 我 们 应 该 很 晴 朗 的 看 出 138 会 话 阻 塞 156 会 话, 以 及 阻 塞 的 原 因 和 会 话 数 和 锁 类 型 insert 锁 阻 塞 LEO1@LEO1> select * from leo1.t1; ID 1 2 200 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 5
LEO1@LEO1> insert into leo1.t1 values(3); 插 入 一 行 但 没 有 提 交, 这 是 一 个 未 决 状 态, 还 不 清 楚 是 否 真 正 插 入 1 row created. LEO2@LEO1> insert into leo1.t1 values(3); 我 们 在 会 话 leo2 上 也 插 入 同 样 的 数 据, 此 时 hang 住 了 不 动 了, 这 里 实 际 上 是 插 入 了 2 条 独 立 的 记 录, 不 能 认 为 是 同 一 条 记 录, 只 是 值 一 样 SID TY ID1 ID2 LMODE REQUEST BLOCK -- 138 TM 73449 0 3 0 0 138 TX 65555 681 6 0 1 138 正 在 阻 塞 另 一 个 会 话 156 TM 73449 0 3 0 0 156 TX 458766 669 6 0 0 这 也 显 示 了 在 插 入 第 二 条 记 录 时 没 有 被 阻 塞 156 TX 65555 681 0 4 0 而 在 插 入 的 修 改 值 相 同 后 被 阻 塞 了, 锁 的 级 别 是 4 insert 时 v$lock 视 图 里 面 多 了 一 个 TX 锁 ( 就 是 最 后 一 行 ), 首 先 说 明 一 下 insert 和 update delete 操 作 的 不 同, 后 两 者 都 是 对 同 一 条 记 录 的 修 改 权 争 用 产 生 阻 塞 ( 这 里 不 涉 及 修 改 值 的 问 题 ), 而 insert 操 作 实 际 上 插 入 了 2 条 不 同 的 记 录, 由 于 这 2 条 不 同 的 记 录 的 修 改 值 一 样 违 反 了 主 键 约 束 从 而 产 生 阻 塞, 实 际 是 对 修 改 值 的 相 同 产 生 了 阻 塞 锁 的 级 别 为 4, 这 种 锁 比 update 的 锁 级 别 要 低, 锁 的 级 别 越 低 限 制 越 少 delete 锁 阻 塞 LEO1@LEO1> select * from leo1.t1; t1 表 中 有 5 条 记 录, 我 们 计 划 删 除 的 是 最 后 1 条 ID 1 2 4 5 200 LEO1@LEO1> delete from leo1.t1 where id=200; 138 会 话 正 在 删 除 id=200 的 记 录, 但 是 没 有 提 交, 此 时 就 是 加 上 一 个 TM TX 锁 1 row deleted. http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 6
LEO2@LEO1> delete from leo1.t1 where id=200; 这 时 158 会 话 也 做 同 样 的 动 作, 就 被 hang 住 了 不 能 动 了, 下 面 我 们 来 看 看 锁 定 情 况 SID TY ID1 ID2 LMODE REQUEST BLOCK -- 138 TM 73449 0 3 0 0 138 TX 524316 935 6 0 1 138 会 话 持 有 一 个 6 级 排 他 锁, 正 在 阻 塞 一 个 会 话 156 TM 73449 0 3 0 0 156 TX 524316 935 0 6 0 156 会 话 被 阻 塞 住 了 没 有 获 得 锁, 正 在 申 请 一 个 6 级 锁 LEO1@LEO1> select object_name from dba_objects where object_id=73449; 现 在 可 知 锁 定 的 就 是 t1 表 OBJECT_NAME T1 LEO1@LEO1> select sid,event from v$session_wait where sid in (138,156); 从 会 话 等 待 视 图 上 也 可 以 看 出 138 阻 塞 了 156 会 话 SID EVENT ---- 138 SQL*Net message from client 156 enq: TX - row lock contention 156 会 话 由 于 TX 锁 争 用 原 因 导 致 hang 住 不 能 前 进,enq=enqueues 队 列 锁 小 结 : 我 们 在 了 解 锁 的 同 时, 也 要 在 业 务 设 计 的 流 程 上 尽 量 去 避 免 它 们 的 发 生, 比 如 说 2 个 人 的 工 作 没 有 协 调 好, 在 同 一 时 间 去 做 了 同 一 件 事, 这 就 有 可 能 产 生 锁 select...for update 锁 阻 塞 这 是 一 种 对 结 果 集 修 改 的 保 护 机 制 场 景 : 一 次 性 修 改 多 条 记 录 的 时 候 会 用 到 这 个 命 令, 起 到 锁 定 结 果 集 的 效 果, 这 也 是 结 果 集 修 改 引 起 的 阻 塞 LEO1@LEO1> select * from leo1.t1; ID 1 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 7
2 3 200 LEO1@LEO1> select * from leo1.t1 where id<=3 for update; 如 果 我 们 想 对 查 询 出 的 结 果 集 进 行 独 占, 并 且 此 时 不 允 许 其 他 会 话 进 行 修 改, 可 以 这 么 来 写 ID 1 2 3 138 TM 73449 0 3 0 0 138 TX 589839 915 6 0 0 这 3 行 记 录 都 已 经 被 TX 锁 锁 定 了, 在 没 有 提 交 之 前 别 人 不 能 修 改 LEO2@LEO1> update leo1.t1 set id=4 where id=1; LEO2@LEO1> update leo1.t1 set id=4 where id=2; LEO2@LEO1> update leo1.t1 set id=4 where id=3; 我 们 在 会 话 leo2 上 测 试 更 新 结 果 集 中 的 每 条 记 录, 都 会 hang 住 了 不 能 前 进, 说 明 这 个 结 果 集 已 经 整 体 被 锁 定 SID TY ID1 ID2 LMODE REQUEST BLOCK -- 138 TM 73449 0 3 0 0 138 TX 589839 915 6 0 1 138 正 在 阻 塞 另 一 个 会 话 156 TM 73449 0 3 0 0 156 TX 589839 915 0 6 0 LEO1=138 会 话 LEO2=156 会 话, 我 们 可 以 看 出 138 会 话 阻 塞 156 会 话,156 会 话 TX 在 请 求 一 个 6 号 排 他 锁, 因 为 2 个 会 话 都 在 修 改 同 一 个 结 果 集 这 种 方 法 可 以 一 次 性 锁 定 n 行 记 录 重 点 : 一 个 表 上 只 能 有 一 个 6 号 锁 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 8
2 模 拟 RI 锁 定 导 致 阻 塞 的 场 景 分 享 知 识 收 获 快 乐 http://www.acoug.org LEO1@LEO1> create table a (id int primary key); a 是 主 表, 定 义 了 id 字 段 为 主 键 Table created. LEO1@LEO1> create table b (id references a(id)); b 是 从 表,id 字 段 是 引 用 主 表 的 id 字 段 Table created. LEO1@LEO1> insert into a values(1); 往 主 表 a 中 插 入 一 条 数 据 但 没 有 提 交, 事 务 没 有 结 束 会 产 生 锁 定 1 row created. 138 TM 73465 0 3 0 0 insert 由 于 有 从 属 关 系 因 此 会 在 2 个 表 上 都 加 3 号 共 享 锁 138 TM 73467 0 3 0 0 138 TX 196640 940 6 0 0 LEO1@LEO1> select object_name from dba_objects where object_id in (73465,73467); ID1 就 是 138 会 话 操 作 的 对 象 id, 我 们 会 在 主 表 和 从 表 上 都 加 上 表 级 锁 OBJECT_NAME A 73465 B 73467 LEO1@LEO1> commit; 提 交 之 后 释 放 锁 Commit complete. 锁 会 随 着 事 务 的 结 束 而 释 放 no rows selected LEO1@LEO1> select * from a; a 表 中 有 一 条 记 录 ID 1 LEO1@LEO1> select * from b; http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 9
no rows selected LEO1@LEO1> update a set id=100 where id=1; 主 表 a 上 更 新 了 一 条 记 录 1 row updated. 138 TM 73465 0 3 0 0 update 现 在 只 对 主 表 有 锁 定, 从 表 没 有 锁 定 138 TX 196634 941 6 0 0 LEO1@LEO1> commit; 提 交 之 后 释 放 锁 Commit complete. 现 在 没 有 锁 了 no rows selected LEO1@LEO1> select * from a; 主 表 里 的 值 已 经 更 新 了 ID 100 LEO1@LEO1> delete from a; 1 row deleted. 138 TM 73465 0 3 0 0 delete 也 是 只 对 主 表 有 锁 定, 从 表 没 有 锁 定 138 TX 655375 705 6 0 0 LEO1@LEO1> commit; 提 交 释 放 锁 Commit complete. no rows selected http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 10
LEO1@LEO1> insert into b values(2); insert into b values(2) * ERROR at line 1: ORA-02291: integrity constraint (LEO1.SYS_C0010831) violated - parent key not found 直 接 给 从 表 插 入 记 录, 如 果 主 表 没 有 的 话, 会 报 错 违 反 引 用 完 整 性 约 束, 没 有 主 表 依 据 LEO1@LEO1> insert into a values(2); 我 们 只 能 先 给 主 表 插 入 1 row created. LEO1@LEO1> insert into b values(2); 再 给 从 表 插 入 才 可 以, 因 为 从 表 的 数 据 必 须 在 主 表 里 先 存 在, 才 能 正 常 引 用 1 row created. LEO1@LEO1> select * from a; 主 表 有 了 ID 2 LEO1@LEO1> select * from b; 从 表 有 了 ID 2 LEO1@LEO1> select * from a; 主 表 有 3 ID 2 3 LEO1@LEO1> insert into b values(3); 才 能 给 从 表 插 入 1 row created. 138 TM 73465 0 3 0 0 从 表 的 insert 也 会 对 主 从 表 同 时 加 锁 138 TM 73467 0 3 0 0 138 TX 393246 939 6 0 0 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 11
LEO1@LEO1> select * from b; ID 2 3 LEO1@LEO1> commit; 释 放 锁 Commit complete. LEO1@LEO1> delete from b; 删 除 从 表 2 rows deleted. 138 TM 73465 0 3 0 0 从 表 delete 也 会 对 主 从 表 同 时 加 锁 138 TM 73467 0 3 0 0 138 TX 196620 944 6 0 0 LEO1@LEO1> commit; 释 放 锁 Commit complete. LEO1@LEO1> select * from a; 主 表 有 2 条 记 录 ID 2 3 LEO1@LEO1> select * from b; 从 表 没 有 记 录 no rows selected LEO1@LEO1> insert into a values(4); 向 主 表 插 入 1 条 记 录, 因 为 没 有 提 交 所 以 是 未 决 状 态 1 row created. http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 12
138 TM 73465 0 3 0 0 此 时 对 主 从 表 都 加 了 锁 定 138 TM 73467 0 3 0 0 138 TX 589834 937 6 0 0 LEO2@LEO1> insert into leo1.b values(4); 此 时 向 从 表 也 插 入 1 条 记 录, 由 于 从 表 的 数 据 必 须 引 用 自 主 表, 而 主 表 数 据 现 在 是 一 种 未 决 状 态, 所 以 hang 住 不 能 前 进 138 TM 73465 0 3 0 0 138 TM 73467 0 3 0 0 138 TX 589834 937 6 0 1 138 会 话 阻 塞 156 会 话, 这 个 6 代 表 已 经 插 入 成 功, 但 修 改 值 还 是 未 决 状 态 156 TM 73467 0 3 0 0 156 TM 73465 0 3 0 0 156 TX 393221 942 6 0 0 这 个 6 代 表 也 已 经 插 入 成 功, 但 修 改 值 还 是 未 决 状 态 156 TX 589834 937 0 4 0 主 从 表 插 入 后 产 生 了 2 个 TX 锁, 这 说 明 这 是 2 条 不 同 的 记 录,2 个 独 立 的 记 录, 不 是 争 用 同 一 条 记 录 小 结 : 之 所 以 还 有 一 个 TX 锁 正 在 申 请 4 号 锁, 是 因 为 2 条 记 录 的 修 改 值 都 是 未 决 状 态 违 反 了 引 用 完 整 性 约 束 从 而 产 生 阻 塞 导 致 156 会 话 hang 住 不 能 前 进 3 构 想 一 个 使 用 手 工 锁 定 解 决 一 种 业 务 需 求 的 场 景 场 景 : 手 工 锁 定 一 个 表, 例 如 我 们 在 做 一 个 秘 密 交 易 的 时 候, 谈 好 的 价 格 就 不 能 变 了, 必 须 一 手 交 钱 一 手 交 货 完 成 交 易, 为 了 保 证 价 格 安 全, 我 们 先 把 价 格 表 锁 定, 这 期 间 不 允 许 篡 改, 保 证 完 成 交 易 LEO1@LEO1> lock table t1 in share mode; 手 工 锁 定 一 个 表, 设 置 锁 的 级 别 为 4 Table(s) Locked. http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 13
138 TM 73449 0 4 0 0 已 经 在 T1 表 上 加 上 了 4 号 锁, 除 了 当 前 会 话 外 阻 塞 其 他 会 话 进 行 修 改 LEO2@LEO1> insert into leo1.t1 values(5); 我 们 在 会 话 leo2 上 插 入 一 条 记 录, 此 时 hang 住 了 不 动 了 138 TM 73449 0 4 0 1 156 TM 73449 0 0 3 0 这 就 是 由 于 我 们 加 了 4 号 锁 除 了 当 前 会 话 外 阻 塞 156 会 话 进 行 修 改,156 会 话 默 认 可 以 获 得 一 个 3 号 锁, 由 于 3 号 和 4 号 互 斥 不 能 共 存, 所 以 没 有 获 得 锁, 正 在 等 待 申 请 3 号 锁 LEO1@LEO1> commit; 锁 跟 着 事 务 的 结 束 而 释 放, commit rollback 都 行 此 时 没 有 锁 信 息 了 no rows selected 4 给 出 从 mode 2-6 的 TM 锁 相 互 间 的 互 斥 示 例 Oracle TM 锁 的 类 型 锁 模 式 锁 描 述 含 义 锁 定 表 的 SQL 0 None 1 Null 空, 本 模 式 是 oracle 预 留 模 式 2 Row Share(RS) 又 叫 (SS) 3 Row Exclusive Table Lock(RX) 又 叫 (SX) 行 级 共 享 锁, 是 限 制 最 少 的 TM 锁, 可 以 提 供 最 高 程 度 的 并 发 性 其 他 会 话 可 以 对 锁 定 的 表 进 行 任 何 类 型 的 DML 操 作, 还 可 以 与 其 他 会 话 锁 并 存 行 级 排 他 锁, 通 常 已 经 有 事 务 在 修 改 行 或 者 select for update 修 改 结 果 集 允 许 其 他 事 务 对 锁 定 的 表 进 行 select insert update delete 或 lock table 同 时 锁 定 一 张 表 Lock table t in row share mode; Lock table t in row exclusive mode; 4 Share Table 共 享 锁, 其 他 事 务 可 以 查 询 锁 定 的 表 但 不 能 Lock table t in share http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 14
Lock(S) 5 Share Row Exclusive Table Lock(SRX) 又 叫 SSX 6 Exclusive Table Lock (X) 分 享 知 识 收 获 快 乐 http://www.acoug.org 修 改, 只 允 许 当 前 事 务 修 改, 但 可 以 多 个 事 务 持 有 它 共 享 行 级 排 他 锁, 同 一 时 间 只 允 许 一 个 事 务 持 有 和 修 改 锁 定 的 表, 其 他 事 务 可 以 查 询 但 不 能 修 改 排 他 锁, 是 限 制 最 高 的 TM 锁, 禁 止 其 他 事 务 执 行 任 何 类 型 的 DML 语 句 或 者 锁 表 一 个 表 一 般 只 能 有 一 个 6 号 锁 mode; Lock table t in share row exclusive mode; Lock table t in exclusive mode; Oracle 锁 模 式 互 斥 关 系 图 锁 模 式 锁 名 称 允 许 级 别 互 斥 级 别 2 行 级 共 享 锁 2 3 4 5 6 3 行 级 排 他 锁 2 3 4 5 6 4 共 享 锁 2 4 3 5 6 5 共 享 行 级 排 他 锁 2 3 4 5 6 6 排 他 锁 2 3 4 5 6 实 验 锁 互 斥 准 备 工 作 LEO1@LEO1> select distinct sid from v$mystat; LEO1 用 户 的 会 话 id=138 SID -------- 138 LEO2@LEO1> select distinct sid from v$mystat; LEO2 用 户 的 会 话 id=156 SID -------- 156 LEO1@LEO1> create table lock1 (x int primary key); 创 建 lock1 表, 设 置 x 列 为 主 键 Table created. LEO1@LEO1> insert into lock1 values(1); 我 们 插 入 1 1 row created. http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 15
LEO1@LEO1> commit; 提 交 Commit complete. LEO1@LEO1> select * from lock1; 现 在 只 有 一 条 记 录, 并 且 没 有 锁 X -------- 1 no rows selected 行 级 共 享 锁 Row Share(RS) 2 LEO1@LEO1> lock table lock1 in row share mode; 把 lock1 表 设 置 为 行 级 共 享 锁 模 式 Table(s) Locked. 138 TM 73472 0 2 0 0 模 式 标 识 :2 LEO1@LEO1> select object_name from dba_objects where object_id=73472; lock1 表 对 象 id 为 73472 OBJECT_NAME LOCK1 LEO2@LEO1> insert into leo1.lock1 values(2); 1 row created. LEO2@LEO1> select * from leo1.lock1; http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 16
X 1 2 LEO2@LEO1> delete from leo1.lock1 where x=1; 1 row deleted. LEO2@LEO1> select * from leo1.lock1; X 2 LEO2@LEO1> update leo1.lock1 set x=10 where x=2; 1 row updated. LEO2@LEO1> select * from leo1.lock1; X 10 LEO2@LEO1> select * from leo1.lock1 for update; X -------- 10 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 17
---- ------ 138 TM 73472 0 2 0 0 156 TM 73472 0 3 0 0 156 TX 524321 936 6 0 0 小 结 : 行 级 共 享 锁, 是 限 制 最 少 的 TM 锁, 可 以 提 供 最 高 程 度 的 并 发 性 其 他 会 话 可 以 对 锁 定 的 表 进 行 任 何 类 型 的 DML 操 作, 还 可 以 与 其 他 会 话 锁 并 存 行 级 排 他 锁 Row Exclusive Table Lock(RX) 3 LEO1@LEO1> lock table leo1.lock1 in row exclusive mode; 把 lock1 表 设 置 为 行 级 排 他 锁 Table(s) Locked. 138 TM 73472 0 3 0 0 模 式 标 识 :3 3 级 锁 是 一 个 灵 活 性 比 较 大 的 锁,insert delete update 都 可 以 产 生 一 个 3 级 锁, 也 允 许 其 他 事 务 来 修 改 锁 定 的 表 LEO1@LEO1> select * from leo1.lock1; X ------- 10 20 30 40 50 会 话 27 LEO1@LEO1> update leo1.lock1 set x=100 where x=10; 1 row updated. LEO1@LEO1> select * from leo1.lock1; 已 经 被 修 改 了 X http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 18
-------- 20 30 40 50 100 会 话 156 LEO2@LEO1> insert into leo1.lock1 values(60); 也 已 经 插 入 了, 注 意 没 有 提 交 的 事 务 对 别 人 是 不 可 见 的 1 row created. LEO2@LEO1> select * from leo1.lock1; X ------- 10 20 30 40 50 60 会 话 146 LEO2@LEO1> delete from leo1.lock1 where x=20; 已 经 删 除 1 row deleted. LEO2@LEO1> select * from leo1.lock1; X 10 30 40 50 ------ 27 TM 73472 0 3 0 0 27 TX 327698 1144 6 0 0 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 19
138 TM 73472 0 3 0 0 146 TM 73472 0 3 0 0 146 TX 196626 975 6 0 0 156 TM 73472 0 3 0 0 156 TX 262163 782 6 0 0 大 家 都 是 共 存 的, 谁 也 没 有 阻 塞 谁,block 列 全 部 是 0 小 结 : 行 级 排 他 锁, 通 常 已 经 有 事 务 在 修 改 行 或 者 select for update 修 改 结 果 集 允 许 其 他 事 务 对 锁 定 的 表 进 行 select insert update delete 或 lock table 同 时 锁 定 一 张 表 共 享 锁 Share Table Lock(S) 4 LEO1@LEO1> select * from leo1.lock1; 还 是 有 5 条 记 录 X ------- 10 20 30 40 50 LEO1@LEO1> lock table leo1.lock1 in share mode; 把 lock1 表 设 置 为 共 享 锁 Table(s) Locked. ---- ------ 138 TM 73472 0 4 0 0 模 式 标 识 :4 4 级 锁 禁 止 其 他 会 话 对 锁 定 的 表 进 行 DML 操 作 但 可 以 select 查 询, 还 允 许 多 个 事 物 一 起 持 有 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 20
-------- 13 TM 73472 0 0 4 0 27 TM 73472 0 0 3 0 138 TM 73472 0 4 0 1 146 TM 73472 0 0 3 0 156 TM 73472 0 0 3 0 27 146 156 会 话 都 已 经 被 阻 塞 了, 有 的 朋 友 会 问 4 级 锁 可 以 和 4 级 锁 共 存 这 里 为 什 么 不 行 呢 呵 呵 因 为 有 3 级 锁 在 捣 乱,4 级 和 3 级 是 不 能 共 存 的, 所 以 我 们 把 所 有 的 3 级 锁 都 释 放 就 可 以 了, 我 们 来 看 一 下 13 TM 73472 0 4 0 0 27 TM 73472 0 2 0 0 138 TM 73472 0 4 0 0 现 在 4 级 锁 2 级 锁 都 可 以 同 时 存 在 了 小 结 : 共 享 锁, 其 他 事 务 可 以 查 询 锁 定 的 表 但 不 能 修 改, 只 允 许 当 前 事 务 修 改, 但 可 以 多 个 事 务 持 有 它 共 享 行 级 排 他 锁 Share Row Exclusive Table Lock(SRX) 5 LEO1@LEO1> lock table leo1.lock1 in share row exclusive mode; 把 lock1 表 设 置 为 共 享 行 级 排 他 锁 Table(s) Locked. -- ------ 138 TM 73472 0 5 0 0 模 式 标 识 :5 LEO1@LEO1> select object_name from dba_objects where object_id=73472; 现 在 锁 定 的 表 就 是 lock1 OBJECT_NAME http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 21
LOCK1 会 话 138 LEO1@LEO1> select * from leo1.lock1; 现 在 表 里 有 5 条 记 录, 我 们 看 看 其 他 会 话 能 不 能 修 改 表 X 10 20 30 40 50 会 话 156 LEO2@LEO1> insert into leo1.lock1 values(60); hang 住 了 不 能 够 前 进 说 明 被 阻 塞 会 话 27 LEO1@LEO1> update leo1.lock1 set x=100 where x=10; hang 住 了 不 能 够 前 进 说 明 被 阻 塞 会 话 146 LEO2@LEO1> delete from leo1.lock1 where x=20; hang 住 了 不 能 够 前 进 说 明 被 阻 塞 那 么 select for update 当 然 也 会 被 阻 塞 的, 我 们 来 看 看 其 他 事 务 能 不 能 查 询 呢! 会 话 23 LEO2@LEO1> select * from leo1.lock1; X --------- 10 20 30 40 50 --------- ------ 27 TM 73472 0 0 3 0 138 TM 73472 0 5 0 1 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 22
146 TM 73472 0 0 3 0 156 TM 73472 0 0 3 0 会 话 23 没 有 被 阻 塞 可 以 正 常 查 询 耶, 这 也 验 证 了 我 们 的 观 点, 读 操 作 不 会 被 任 何 事 务 阻 塞, 也 不 会 加 锁,27 146 156 会 话 3 个 行 级 排 他 锁 都 在 等 待 着 138 会 话 释 放 锁 --- ---- 13 TM 73472 0 2 0 0 138 TM 73472 0 5 0 0 会 话 13 的 2 级 锁 和 会 话 138 的 5 级 锁 可 以 共 存 符 合 我 们 的 锁 互 斥 关 系 小 结 : 共 享 行 级 排 他 锁, 同 一 时 间 只 允 许 一 个 事 务 持 有 和 修 改 锁 定 的 表, 其 他 事 务 可 以 查 询 但 不 能 修 改 排 他 锁 Exclusive Table Lock (X) 6 LEO1@LEO1> lock table leo1.lock1 in exclusive mode; 把 lock1 表 设 置 为 排 他 锁, 等 级 最 高 的 锁 Table(s) Locked. ---- 138 TM 73472 0 6 0 0 模 式 标 识 :6 会 话 156 LEO2@LEO1> insert into leo1.lock1 values(60); 会 话 27 LEO1@LEO1> update leo1.lock1 set x=100 where x=10; 会 话 146 LEO2@LEO1> delete from leo1.lock1 where x=20; 会 话 13 LEO2@LEO1> lock table leo1.lock1 in row share mode; 测 试 锁 互 斥 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 23
------ -------- 13 TM 73472 0 0 2 0 27 TM 73472 0 0 3 0 138 TM 73472 0 6 0 1 146 TM 73472 0 0 3 0 156 TM 73472 0 0 3 0 6 级 锁 会 阻 塞 除 了 自 己 外 的 所 有 会 话 的 事 务 并 且 排 斥 其 他 的 所 有 锁 模 式 连 2 级 锁 都 不 行, 是 最 高 限 制 的 TM 锁, 当 然 select 还 是 没 有 问 题 的 小 结 : 排 他 锁, 是 限 制 最 高 的 TM 锁, 禁 止 其 他 事 务 执 行 任 何 类 型 的 DML 语 句 或 者 锁 表 一 个 表 一 般 只 能 有 一 个 6 号 锁 5 给 出 一 个 导 致 死 锁 的 SQL 示 例 死 锁 定 义 : 从 广 义 上 讲 包 括 操 作 系 统 应 用 程 序 数 据 库, 如 果 2 个 进 程 ( 会 话 ) 相 互 持 有 对 方 的 资 源, 都 一 直 等 待 对 方 释 放, 这 种 情 况 会 造 成 死 锁 误 解 : 会 话 的 阻 塞 可 不 是 死 锁, 因 为 其 中 有 一 个 会 话 还 是 可 以 继 续 操 作 的 释 放 :Oracle 会 自 动 检 测 死 锁 并 强 制 干 预 释 放 LEO1@LEO1> create table p1 ( x int primary key ); 我 们 新 建 一 个 p1 表, 设 置 x 字 段 为 主 键 Table created. LEO1@LEO1> insert into leo1.p1 values(10); 138 会 话 插 入 的 是 10 1 row created. LEO2@LEO1> insert into leo1.p1 values(20); 156 会 话 插 入 的 是 20 1 row created. 138 TM 73470 0 3 0 0 138 TX 327713 1124 6 0 0 138 有 一 个 TX 排 他 锁, 但 当 前 没 有 阻 塞 会 话 156 TM 73470 0 3 0 0 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 24
156 TX 589825 945 6 0 0 156 也 有 一 个 TX 排 他 锁, 但 当 前 也 没 有 阻 塞 会 话 LEO1@LEO1> select object_name from dba_objects where object_id=73470; 看 p1 表 上 存 在 正 常 的 TM TX 锁, 都 没 有 阻 塞 到 对 方 的 会 话 OBJECT_NAME P1 LEO1@LEO1> insert into leo1.p1 values(20); 此 时 我 在 138 会 话 上 再 插 入 20, 发 现 hang 住 了 不 能 前 进, 这 是 什 么 原 因 呢? 我 们 看 看 v$lock 视 图 SID TY ID1 ID2 LMODE REQUEST BLOCK -- 138 TM 73470 0 3 0 0 138 TX 589825 945 0 4 0 这 时 138 会 话 就 继 续 插 入 了, 但 这 个 插 入 动 作 是 成 功 的 没 有 阻 塞, 而 是 由 于 138 156 会 话 修 改 值 的 相 同 138 TX 327713 1124 6 0 0 违 反 了 主 键 约 束 从 而 产 生 阻 塞, 实 际 是 对 修 改 值 的 相 同 产 生 了 阻 塞, 所 以 申 请 的 是 4 级 锁, 而 非 6 级 锁 156 TM 73470 0 3 0 0 156 TX 589825 945 6 0 1 156 会 话 此 时 正 在 阻 塞 138 会 话, 因 为 156 会 话 的 事 务 还 没 有 完 成 还 是 一 个 未 决 状 态 LEO2@LEO1> insert into leo1.p1 values(10); 我 在 156 会 话 上 也 插 入 10, 这 时 死 锁 的 效 果 就 出 来 了 SID TY ID1 ID2 LMODE REQUEST BLOCK -- 138 TM 73470 0 3 0 0 138 TX 327713 1124 6 0 1 138 会 话 此 时 正 在 阻 塞 156 会 话, 因 为 138 会 话 的 事 务 还 没 有 完 成 还 是 一 个 未 决 状 态 156 TM 73470 0 3 0 0 156 TX 327713 1124 0 4 0 实 际 上 是 对 修 改 值 的 相 同 产 生 了 阻 塞,156 会 话 正 在 申 请 4 级 锁 156 TX 589825 945 6 0 0 LEO1@LEO1> insert into leo1.p1 values(20); 我 们 看 一 下 138 会 话 报 错,Oracle 自 动 检 测 死 锁 并 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 25
强 制 干 预 释 放 insert into leo1.p1 values(20) * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource( 等 待 资 源 时 检 测 到 死 锁 -> 释 放 之 ) 这 时 请 注 意 一 下, 只 是 释 放 掉 了 第 一 个 锁 定, 但 第 二 个 锁 定 还 在 等 待, 所 以 我 们 要 手 工 释 放 6 总 结 Ø 总 结 : 上 面 讲 到 了 好 几 种 锁 的 机 制, 我 们 崇 尚 的 思 想 就 是 先 要 想 一 想 为 什 么 会 出 现 锁, 不 出 现 行 不 行, 锁 的 作 用 有 哪 些, 这 种 启 发 式 的 思 路 能 够 让 我 们 记 忆 深 刻 Ø LOCK 作 用 : 独 占 业 务 资 源 保 证 读 一 致 性 维 护 事 务 完 整 性 Ø LOCK 宗 旨 : 没 有 并 发 就 没 有 锁, 一 个 人 操 作 数 据 库 是 不 会 产 生 锁 的 Leonarding 2012.11.28 天 津 &winter 分 享 技 术 ~ 成 就 梦 想 Blog:www.leonarding.com Leonarding 的 个 人 简 介 刘 盛, 英 文 名 Leonarding 现 任 中 国 征 信 中 心 (PBCCRC) 高 级 数 据 架 构 师, 负 责 金 融 征 信 项 目 基 础 架 构 数 据 建 模 设 备 选 型 解 决 方 案 曾 任 职 于 中 国 联 通 天 津 公 司 网 管 中 心 DBA, 参 与 设 计 联 通 分 组 网 信 令 监 控 系 统, 运 维 TB 级 海 量 数 据 库 项 目 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 26
天 津 大 学 计 算 机 科 学 与 技 术 硕 士 本 人 具 有 丰 富 Oracle 管 理 经 验 和 项 目 设 计 经 验, 擅 长 大 型 数 据 库 架 构 设 计 与 建 模 性 能 调 优 故 障 诊 断 及 高 可 用 容 灾 技 术 喜 欢 专 研 各 种 数 据 库 (Mysql/SybaseIQ/Hbase/Redis/Memcache/Cassandra) 原 理 及 应 用 场 景 对 大 数 据 的 采 集 处 理 整 合 利 用 具 有 独 到 见 解 撰 写 了 大 量 数 据 库 & 数 据 分 析 原 创 文 章, 荣 获 2012&2013 年 度 ITPUB 最 佳 技 术 原 创 精 华 奖 乐 于 参 加 各 种 行 业 会 议 交 流, 提 倡 分 享 技 术 ~ 成 就 梦 想 理 念 timer shaft: 2009 Redhat RHCE 2010 Oracle10g OCP 2012 Oracle10g OCM 2013 ACOUG Core Member 2013 年 被 提 名 为 DATAGURU Oracle 数 据 库 与 大 数 据 解 决 方 案 版 版 主 2013 年 被 提 名 为 ITPUB HADOOP 版 版 主 技 术 博 客 Blog:www.leonarding.com 新 浪 微 博 :@itpub_leonarding Email: leonarding@yeah.net MSN: lsls1984@hotmail.com QQ: 40354446 I a DBA,Also a consultant,also a lecturer 面 朝 大 海 春 暖 花 开 http://www.acoug.org 实 力 成 就 稳 健 技 术 创 造 价 值 27