数据库锁机制

数据库锁机制

数据库锁主要分为三大类

1.全局锁

2.表级锁

3.行级锁

全局锁

• 定义：全局锁是对整个数据库实例加锁，禁止所有对数据库的写操作。

• 用途：主要用于备份和维护操作。

• 示例

  MySQL

  FLUSH TABLES WITH READ LOCK;
  

  这条命令会锁定所有表，只允许读操作，禁止写操作。

  解锁

  UNLOCK TABLES;
  

表级锁

• 定义：表级锁是对整个表加锁，限制对表的并发访问。

• 用途：适用于低并发场景，管理开销较小。

  表级锁分为：读锁和写锁

  读锁（共享锁，S 锁）

  • 定义：允许多个事务同时读取同一张表，但不允许任何事务对该表进行写操作。
  • 用途：适用于读多写少的场景，确保数据的一致性。
  • 特点：多个读锁可以共存。读锁与写锁互斥，即一个表上有读锁时，不能加写锁。

  LOCK TABLES table_name READ;
  

  这条命令会对指定表加共享锁，允许多个事务读取，但禁止写操作。

  写锁（排他锁，X 锁）

  • 定义：只允许一个事务对表进行读取和写操作，排除其他事务的任何访问。
  • 用途：适用于写多读少的场景，确保数据的独占性。
  • 特点：排他锁与其他任何类型的锁互斥，即一个表上有写锁时，不能加任何其他锁。

  LOCK TABLES table_name WRITE;
  

  这条命令会对指定表加排他锁，只允许一个事务读写，禁止其他事务访问。

  元数据锁（Metadata Lock，MDL）

  • 定义：元数据锁用于保护表的元数据（如表结构、索引等），防止在事务执行过程中表结构被修改。
  • 用途：确保在事务执行期间表的结构不会发生变化，防止数据不一致。
  • 特点：元数据锁分为读锁和写锁。读锁允许多个事务同时读取元数据，但不允许修改。写锁只允许一个事务修改元数据，排除其他事务的任何访问。

  读元数据锁

  SELECT * FROM table_name;
  

  写元数据锁

  ALTER TABLE table_name ADD COLUMN new_column INT;
  

  自增锁（Auto-Increment Lock）

  • 定义：自增锁用于管理自增列（AUTO_INCREMENT）的值分配，确保在并发插入时自增值的唯一性和连续性。
  • 用途：防止多个事务同时插入自增列时产生冲突。
  • 特点：自增锁在插入新记录时自动加锁，确保自增值的分配。InnoDB 存储引擎提供了多种自增锁模式，可以通过配置参数调整。

  插入新记录时自动加锁：

  INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
  

  InnoDB 存储引擎提供了以下几种自增锁模式，可以通过配置参数 innodb_autoinc_lock_mode 调整：

  传统模式（Traditional Mode，0）：

  • 插入每一条记录时都会加表级锁，确保自增值的连续性。

  • 适用于需要严格连续自增值的场景。

  • 示例

    SET GLOBAL innodb_autoinc_lock_mode = 0;
    

  连续模式（Consecutive Mode，1）：

  • 插入一批记录时加一次表级锁，确保自增值的连续性。

  • 适用于大多数场景，平衡了性能和连续性。

  • 示例

    SET GLOBAL innodb_autoinc_lock_mode = 1;
    

  交错模式（Interleaved Mode，2）：

  • 插入每一条记录时不加表级锁，自增值可能不连续。

  • 适用于高性能场景，牺牲了自增值的连续性。

  • 示例

    SET GLOBAL innodb_autoinc_lock_mode = 2;
    

  解锁

  UNLOCK TABLES;
  

行级锁

• 定义：行级锁是对表中的单个行加锁，限制对特定行的并发访问。

• 用途：适用于高并发场景，管理开销较大，但并发性能更高。

  行级锁的类型更为多样，主要包括记录锁、间隙锁、临键锁等。

  记录锁（Record Lock）

  • 定义：锁定索引记录本身。
  • 用途：确保对特定行的独占访问。
  • 特点：只锁定索引记录，不影响其他行。适用于高并发场景，减少锁的竞争。

  SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
  

  这条命令会对查询结果中的行加排他锁，确保在事务提交前其他事务不能修改这些行。

  间隙锁（Gap Lock）

  • 定义：锁定索引记录之间的间隙。
  • 用途：防止其他事务在该间隙内插入新记录。
  • 特点：间隙锁不锁定具体的记录，而是锁定记录之间的空隙。适用于防止幻读（Phantom Read）。

  SELECT * FROM table_name WHERE id > 10 AND id < 20 FOR UPDATE;
  

  这条命令会对 id 在 10 和 20 之间的间隙加锁，防止其他事务插入新的记录。

  临键锁（Next-Key Lock）

  • 定义：记录锁和间隙锁的组合，锁定索引记录及其之前的间隙。
  • 用途：确保对特定行及其之前间隙的独占访问。
  • 特点：临键锁不仅锁定记录本身，还锁定记录之前的间隙。适用于防止幻读和不可重复读（Non-Repeatable Read）。

  SELECT * FROM table_name WHERE id = 10 FOR UPDATE;
  

  这条命令会对 id 为 10 的记录及其之前的间隙加锁，确保在事务提交前其他事务不能修改这些行或插入新的记录。

  总结

  • 表级锁：

    读锁（共享锁，S 锁）：允许多个事务同时读取同一张表，但不允许写操作。

    写锁（排他锁，X 锁）：只允许一个事务对表进行读取和写操作，排除其他事务的任何访问。

    元数据锁（Metadata Lock，MDL）：保护表的元数据，防止在事务执行过程中表结构被修改。

    自增锁（Auto-Increment Lock）：管理自增列的值分配，确保在并发插入时自增值的唯一性和连续性。

  • 行级锁：

    记录锁（Record Lock）：锁定索引记录本身，确保对特定行的独占访问。

    间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止其他事务在该间隙内插入新记录。

    临键锁（Next-Key Lock）：记录锁和间隙锁的组合，锁定索引记录及其之前的间隙，确保对特定行及其之前间隙的独占访问。

  InnoDB 存储引擎

  InnoDB 默认使用行级锁

InnoDB 与 MyISAM 的区别

1. 锁机制

• InnoDB：

  支持行级锁，适用于高并发场景。

  支持事务和外键约束，确保数据的一致性和完整性。

  使用多版本并发控制（MVCC），提高并发性能。

• MyISAM：

  支持表级锁，适用于低并发场景。

  不支持事务和外键约束，适合简单的读写操作。

  不支持 MVCC，性能较低。

2. 事务支持

• InnoDB：

  支持事务，可以回滚和提交事务。

  支持 ACID（原子性、一致性、隔离性、持久性）特性。

• MyISAM：

  不支持事务，没有回滚和提交操作。

  不支持 ACID 特性。

3. 外键约束

• InnoDB：

  支持外键约束，确保数据的引用完整性。

• MyISAM：

  不支持外键约束。

4. 存储方式

• InnoDB：

  使用聚集索引，主键索引和数据存储在一起。

  支持全文索引。

• MyISAM：

  使用非聚集索引，索引和数据分开存储。

  支持全文索引。

5. 性能

• InnoDB：

  适用于高并发读写操作，性能较好。

  支持并发插入和更新操作。

• MyISAM：

  适用于低并发读操作，性能较低。

  插入和更新操作可能会导致表锁竞争。