MySQL：表级锁

表级锁

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

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

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

表级锁的类型

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

表锁（Table Lock）
- 读锁（Read Lock）：也称为共享锁（Shared Lock）（S锁），允许其他事务读取该表，但不允许写入。
- 写锁（Write Lock）：也称为排他锁（Exclusive Lock）（X锁），不允许其他事务读取或写入该表。
元数据锁（Metadata Lock, MDL）
- 主要用于避免 DML（数据操纵语言）与 DDL（数据定义语言）之间的冲突。当对表进行增删改查操作时，会自动加上 MDL 读锁；当要对表结构进行变更时，会加上 MDL 写锁。
意向锁（Intention Lock）
- 包括意向共享锁（IS）和意向排他锁（IX）。它们主要用于表明事务将来可能需要的锁类型，以减少表锁的判断成本。

加锁语句

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

sql">LOCK TABLES table_name [AS alias] lock_type [, table_name [AS alias] lock_type]...

sql">-- 为单个表设置表锁
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 将表锁解除：

sql">UNLOCK TABLES;

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

共享锁

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

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

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

特点

多个对话可以在同一时间获取一个表的共享锁，其他会话无需获取共享锁也可以读取该表数据
只能读取持有共享锁的表中的数据，不能对其写入，只有共享锁被解除后，才能写入；写入操作会被放入等待队列中，当锁解除后才能执行。可以通过 SHOW PROCESSLIST 指令查看
其他会话若插入数据，将会报错：

sql">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 语句，可以在读取数据的同时对返回的数据集加上共享锁。

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

示例

sql">-- 会话 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 语句时，数据库会为查询结果集中的每一行都加上排他锁，以防止其他会话对这些行进行修改操作，直到当前事务提交或回滚为止。

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

示例

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