MySQL：表级锁

表级锁

Table Lock（表锁）是一种数据库锁（Lock）机制，用于控制并发访问数据库表的操作。当一个会话对表进行操作时，会自动获取相应的锁，以确保其他会话无法同时修改该表的数据，从而维持数据库的一致性和完整性。

MySQL中的表级锁（Table-Level Lock）是对整个表进行锁定的一种机制。当表被锁定后，其他事务不能对该表进行写操作，部分情况下也不能进行读操作，具体取决于锁的类型。表级锁的实现简单，但并发性能较低，因为它会锁定整个表，导致其他事务的等待和阻塞。

关于数据库锁机制的详解：数据库的锁机制

表级锁的类型

MySQL 的表级锁主要分为以下几种类型：

1. 表锁（Table Lock）

   • 读锁（Read Lock）：也称为共享锁（Shared Lock）（S锁），允许其他事务读取该表，但不允许写入。

   • 写锁（Write Lock）：也称为排他锁（Exclusive Lock）（X锁），不允许其他事务读取或写入该表。

2. 元数据锁（Metadata Lock, MDL）

   • 主要用于避免 DML（数据操纵语言）与 DDL（数据定义语言）之间的冲突。当对表进行增删改查操作时，会自动加上 MDL 读锁；当要对表结构进行变更时，会加上 MDL 写锁。

3. 意向锁（Intention Lock）

   • 包括意向共享锁（IS）和意向排他锁（IX）。它们主要用于表明事务将来可能需要的锁类型，以减少表锁的判断成本。

加锁语句

使用 LOCK TABLE 语句手动为一个或多个表设置表锁，以确保在事务执行期间其他会话无法对这些表进行读写操作：

LOCK TABLES table_name [AS alias] lock_type 
					   [, table_name [AS alias] lock_type]...

-- 为单个表设置表锁
LOCK TABLE table_name READ | WRITE;

-- 为多个表设置表锁
LOCK TABLE table1 READ | WRITE,
		   table2 READ | WRITE,
		   ...

• READ 为表设置 共享锁，WRITE 为表设置 排他锁
• 当会话被终止后，无论是正常还是异常终止，表锁都会被 MySQL 自动解除
• 也可以通过显式的 COMMIT 或 ROLLBACK 事务来释放锁

注意事项

• 锁定表之后，其他会话将无法对锁定的表进行读写操作，直到使用 UNLOCK TABLES 释放表锁。

• 锁定表是一个重型操作，对系统性能有一定影响。

• 在使用 LOCK TABLES 之前，需要确保没有任何未提交的事务正在使用要锁定的表。

• 在使用 LOCK TABLES 时，需要小心避免死锁的情况，即多个会话相互等待彼此持有的锁而无法继续执行。

通常情况下，推荐使用隐式锁定来管理并发操作，而不是手动使用 LOCK TABLES 语句。只有在特殊情况下，如需要手动控制表的锁定和并发访问时，才使用 LOCK TABLES。

解除表锁

使用 UNLOCK TABLES 将表锁解除：

UNLOCK TABLES;

• 这条语句会释放当前会话持有的所有表锁。需要注意的是，UNLOCK TABLES后面不能跟表名，也不能只释放指定表的锁。

共享锁

在并发场景中，多个会话可能同时访问同一个数据对象（如表、行等），如果不加以限制，可能会引发一致性问题，例如脏读、不可重复读和幻读等。为了确保数据的一致性和避免并发问题，数据库系统引入了共享锁机制。

共享锁（Shared Lock）：共享锁允许多个会话同时对同一个表进行读取操作，这些会话之间不会互相阻塞。

共享锁尤其适用于读取密集型操作，如查询和报表生成；在需要修改数据的操作（例如插入、更新、删除）时，通常会使用排他锁来保证数据的一致性。

特点

1. 多个对话可以在同一时间获取一个表的共享锁，其他会话无需获取共享锁也可以读取该表数据

2. 只能读取持有共享锁的表中的数据，不能对其写入，只有共享锁被解除后，才能写入；写入操作会被放入等待队列中，当锁解除后才能执行。可以通过 SHOW PROCESSLIST 指令查看

3. 其他会话若插入数据，将会报错：

Error Code: 1099. Table 'messages' was locked with a READ lock and can't be updated.

获取共享锁

LOCK IN SHARE MODE 是 MySQL 中的锁定语句，用于在事务中获取共享锁。

语句只在事务中有效，使用时应确保在合适的事务范围内执行

当在一个事务中使用 SELECT 查询语句时，通过添加 LOCK IN SHARE MODE 语句，可以在读取数据的同时对返回的数据集加上共享锁。

START TRANSACTION;  -- 开启一个新事务
SELECT column_list FROM table [WHERE condition]
LOCK IN SHARE MODE;
COMMIT;

示例

-- 会话 A
BEGIN;
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 读取操作 ...

-- 会话 B
BEGIN;
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 读取操作 ...

会话 A 和会话 B 通过 LOCK IN SHARE MODE 获取了对表 table_name 的共享锁，它们可以同时读取数据。两个会话之间没有互相阻塞的情况

排他锁

排他锁（Exclusive Lock） 允许一个会话独占地持有对数据对象的锁，其他会话无法同时获取共享锁或排他锁，从而实现了并发写入操作的互斥性。

一个会话持有排他锁，其他会话无法同时对数据对象进行读取操作或写入操作。

读写操作会被放入等待队列中，当锁解除后才能执行。可以通过 SHOW PROCESSLIST 指令查看

获取排他锁

FOR UPDATE 是一种在 SQL 查询中使用的锁定语句，用于获取排他锁，确保在事务中对查询结果集进行排他性操作

使用 FOR UPDATE 语句时，数据库会为查询结果集中的每一行都加上排他锁，以防止其他会话对这些行进行修改操作，直到当前事务提交或回滚为止。

BEGIN TRANSACTION
SELECT column_list FROM table [WHERE condition]
FOR UPDATE;
[-- 对查询结果进行修改操作
UPDATE table_name SET column_name = value;]
COMMIT;

示例

-- 会话 A
BEGIN;
SELECT * FROM table_name WHERE ... FOR UPDATE;
-- 写入操作 ...

-- 会话 B
BEGIN;
SELECT * FROM table_name WHERE ... FOR UPDATE;
-- 写入操作 ...
-- 会话 A 提交之前，会话 B 在相同的查询条件下无法获取/写入对应的行数据

会话 A 和会话 B 通过 FOR UPDATE 获取了对表 table_name 的排他锁，它们不能同时读取或写入数据。如果会话 A 已经获取了排他锁，则会话 B 需要等待会话 A 释放锁后才能获取排他锁执行相应操作。

局限性和注意事项

• 表级别的锁粒度较大，当多个会话需要并发操作同一个表时，可能会出现阻塞和资源竞争的情况，降低系统的并发性能。

• 表锁的粒度较大也导致了锁的冲突概率增加，从而可能导致死锁的发生。死锁是指多个会话相互等待对方持有的锁资源，导致所有会话都无法继续执行。

• 当一个会话持有排他锁时，其他会话无法并发读取表中的数据，这可能导致读取操作的延迟。

为了避免表级锁可能带来的性能问题和并发冲突，通常还会使用更细粒度的锁机制，如 行级锁、页级锁 或 乐观并发控制 等，以提高并发性能和减少锁竞争。具体使用哪种锁机制取决于数据库管理系统的支持和应用的需求。