数据库的锁级别

兰必鑫 發表於 2025-9-2 20:33:00

锁是数据库保证 并发一致性 的重要手段，不同锁级别决定了锁的粒度和范围，从而影响并发性能与数据安全性。锁级别从粗到细：全局锁 → 表级锁 → 页级锁 → 行级锁，粒度越细，并发度越高，但开销越大。
<h3>一、按锁的粒度划分（从粗到细）</h3>
1、全局锁（Global Lock）
        (1) 定义：对整个数据库实例加锁，限制所有表的读写操作。
        (2) 适用场景：全库逻辑备份（如 MySQL 的 <code>FLUSH TABLES WITH READ LOCK</code>），确保备份期间数据不被修改，保证备份一致性。
        (3) 特点：
                ① 加锁期间，整个库处于只读状态，所有更新、插入、删除操作都会被阻塞。
                ② 粒度最粗，并发影响最大，一般仅在特殊场景（如全库备份）短期使用。
2、表级锁（Table-level Lock）
        (1) 定义：对整张表加锁，控制对表的并发访问。
        (2) 常见类型：
                ① 表共享锁（Table Shared Lock，读锁 S）：持有读锁时，所有事务可读取表数据，但不能修改，且其他事务也可加读锁，但不能加写锁。
                ② 表排他锁（Table Exclusive Lock，写锁 X）：持有写锁时，只有当前事务可读写表数据，其他事务既不能读也不能写（需等待锁释放）。
                ③ 意向锁（Intention Lock）：
                        (a) 数据库自动添加，用于表级锁和行级锁的协调（如 MySQL InnoDB）。
                        (b) 意向共享锁（IS）：事务 打算 对表中某些行加读锁。
                        (c) 意向排他锁（IX）：事务 打算 对表中某些行加写锁。
                        (d) 作用：提前声明锁意图，避免表级锁和行级锁的冲突检查效率过低。
                ④ 自增锁（Auto-increment Lock）：
                        (a) 针对自增列（如 <code>AUTO_INCREMENT</code>）的特殊锁，保证插入时自增值唯一。
                        (b) MySQL 可通过 <code>innodb_autoinc_lock_mode</code> 调整锁粒度（0：全表锁； 1： 连续值批量锁；2：无锁，依赖二进制日志确保一致性）。
        (3) 适用场景：
                ①  表结构修改（如 <code>ALTER TABLE</code>）、全表扫描或大量数据更新（避免行锁竞争）。
                ② 非事务引擎（如 MySQL MyISAM）默认使用表级锁。
        (4) 特点：开销小、加锁快，适合表级操作，但 并发度低（容易阻塞）。
3、行级锁（Row-level Lock）
        (1) 定义：对表中单行数据加锁，是粒度最细的锁，并发度最高。
        (2) 常见类型（以 MySQL InnoDB 为例）：
                ① 行共享锁（Row Shared Lock，S）：事务对某行加读锁，允许其他事务读该行，但不能修改（需等锁释放）。
                ② 行排他锁（Row Exclusive Lock，X）：事务对某行加写锁，其他事务既不能读（需等锁释放）也不能写该行。
                ③ 间隙锁（Gap Lock）：
                        (a) 锁定索引记录之间的 "间隙"（如 <code>WHERE id BETWEEN 5 AND 10</code>，锁定 5~10 之间的空白区域），防止其他事务插入数据导致 "幻读"。
                        (b) 仅在 InnoDB 的 可重复读（RR）隔离级别 下生效。
                ④ 临键锁（Next-key Lock）：
                        (a) 行锁 + 间隙锁的组合，锁定索引记录本身及前面的间隙（如索引值为 10 的行，锁定 (-∞,10] 范围）。
                        (b) InnoDB 默认的行级锁类型，用于平衡并发和一致性。
                ⑤ 记录锁（Record Lock）：仅锁定单行记录（不包含间隙），在 读已提交（RC）隔离级别 或条件命中唯一索引时生效。
        (3) 适用场景：
                ① 高并发的单行或少量行操作（如用户余额更新、订单状态修改）。
                ② 事务引擎（如 InnoDB、PostgreSQL）的核心锁机制。
        (4) 特点：
                ① 开销大、加锁慢（需定位具体行），但并发度高，适合精细化控制。
                ② 可能引发死锁（多个事务互相等待对方释放行锁）。
4、页级锁（Page-level Lock）
        (1) 定义：对数据页（数据库存储的基本单位，如 InnoDB 一页默认 16KB）加锁，粒度介于表级和行级之间。
        (2) 适用场景：部分数据库（如 MySQL BDB 引擎、SQL Server）支持，平衡并发和开销。
        (3) 特点：加锁速度快于行锁，并发度高于表锁，但可能出现 "页内无关行被锁定" 的浪费。
<h3>二、按锁的功能 / 模式划分</h3>
1、共享锁（Shared Lock，S 锁）
        (1) 作用：允许事务读取数据，多个事务可同时持有同一资源的 S 锁。
        (2) 兼容性：与 S 锁兼容，与 X 锁互斥（读锁和写锁不能同时存在）。
        (3) 示例：<code>SELECT ... LOCK IN SHARE MODE</code>（MySQL InnoDB 手动加读锁）。
2、排他锁（Exclusive Lock，X 锁）
        (1) 作用：允许事务修改数据，仅当前事务可持有，阻止其他事务读写。
        (2) 兼容性：与所有锁（S 锁、X 锁）互斥。
        (3) 示例：<code>SELECT ... FOR UPDATE</code>（MySQL InnoDB 手动加写锁），或 <code>UPDATE</code>/<code>DELETE</code> 语句自动加 X 锁。
3、乐观锁（Optimistic Lock）
        (1) 定义：非数据库原生锁，通过 "版本号" 或 "时间戳" 实现并发控制，假设冲突概率低，仅在提交时检查是否有冲突。
        (2) 实现方式：表中增加 <code>version</code> 字段，更新时 <code>WHERE version = 原版本</code>，若失败则重试。
        (3) 适用场景：读多写少、冲突少的场景（如商品库存查询）。
        (4) 特点：无锁等待，性能高，但需应用层实现，不适合高冲突场景。
4、悲观锁（Pessimistic Lock）
        (1) 定义：数据库原生锁机制，假设冲突概率高，操作前先加锁，确保独占资源。
        (2) 示例：行级锁、表级锁均属于悲观锁。
        (3) 适用场景：写多读少、冲突频繁的场景（如秒杀库存扣减）。
        (4) 特点：保证一致性，但可能导致锁等待和死锁。
<h3>三、不同数据库的锁机制差异</h3>
1、MySQL：
        (1) InnoDB 支持行级锁（Next-key Lock 为主）、表级锁（意向锁、自增锁）、全局锁。
        (2) MyISAM 仅支持表级锁（读锁 / 写锁），不支持事务。
2、PostgreSQL：支持行级锁（共享 / 排他）、表级锁、页级锁，且有 "咨询锁"（应用层自定义锁）。
3、Oracle：以行级锁为主，通过 "回滚段" 实现多版本并发控制（MVCC），锁机制更灵活。
4、SQL Server：支持表级锁、页级锁、行级锁，可通过 <code>SET TRANSACTION ISOLATION LEVEL</code> 调整锁行为。
不断成长，走出舒适区，实现自我增值。-- 烟沙九洲 
来源：https://www.cnblogs.com/yanshajiuzhou/p/19070679

頁: [1]

琼殿技术论坛's Archiver

数据库的锁级别