表级锁

• 特点

• 粒度最大：表级锁会对整张表进行锁定，当一个事务对表加上锁后，其他事务对该表的读写操作都会被阻塞，除非该锁被释放。

• 开销小：加锁和解锁的操作相对简单，系统开销较小，因为只需要管理一个锁对象。

• 并发度低：由于锁定了整张表，会导致同一时间只有一个事务能对该表进行写操作，或者多个事务可以同时进行读操作，但写操作和读操作不能同时进行，因此并发性能较低。

• 常见类型

• 表共享读锁（Table Read Lock）：允许多个事务同时对表进行读操作，但不允许其他事务对该表进行写操作。例如，多个事务可以同时执行 SELECT 语句查询同一张表。

-- 会话 1
-- 启动一个事务
START TRANSACTION;
-- 对表 t 加共享读锁
LOCK TABLES t READ;
-- 查询表 t 的数据
SELECT * FROM t;
-- 模拟长时间操作
SELECT SLEEP(30);
-- 释放锁并提交事务
UNLOCK TABLES;
COMMIT;

-- 会话 2
-- 尝试在会话 1 持有共享读锁期间查询表 t
SELECT * FROM t; -- 可以正常查询

-- 尝试在会话 1 持有共享读锁期间向表 t 插入数据
INSERT INTO t (column1, column2) VALUES ('value1', 'value2'); 
-- 此操作会被阻塞，直到会话 1 释放锁

表独占写锁（Table Write Lock）：允许一个事务对表进行写操作，但不允许其他事务对该表进行读操作或写操作。例如，一个事务可以执行 INSERT、UPDATE 或 DELETE 语句修改表中的数据。

-- 会话 1
-- 启动一个事务
START TRANSACTION;
-- 对表 t 加独占写锁
LOCK TABLES t WRITE;
-- 向表 t 插入数据
INSERT INTO t (column1, column2) VALUES ('value1', 'value2');
-- 模拟长时间操作
SELECT SLEEP(30);
-- 释放锁并提交事务
UNLOCK TABLES;
COMMIT;

-- 会话 2
-- 尝试在会话 1 持有独占写锁期间查询表 t
SELECT * FROM t; 
-- 此操作会被阻塞，直到会话 1 释放锁

-- 尝试在会话 1 持有独占写锁期间向表 t 插入数据
INSERT INTO t (column1, column2) VALUES ('value3', 'value4'); 
-- 此操作会被阻塞，直到会话 1 释放锁

适用场景

• 适用于以查询为主，很少有更新操作的场景，如数据仓库中的报表查询。因为查询操作可以共享读锁，不会相互阻塞，而更新操作较少，对并发性能的影响较小。
• 当需要对整张表进行批量数据操作时，如批量导入数据或批量更新表结构，使用表级锁可以避免其他事务的干扰，提高操作的效率。

行级锁

• 特点
• 粒度最小：行级锁只对表中的某一行或几行数据进行锁定，其他行的数据可以被其他事务正常访问，因此可以提供更高的并发度。
• 开销大：加锁和解锁的操作相对复杂，需要管理多个锁对象，系统开销较大。因为需要为每一行数据维护锁信息，当数据量较大时，锁的管理成本会显著增加。
• 并发度高：不同事务可以同时对不同的行进行读写操作，只要这些行之间没有锁冲突，从而大大提高了数据库的并发处理能力。 常见类型
• 共享锁（Shared Lock）：也称为读锁，多个事务可以同时对同一行数据加共享锁进行读操作，但不允许其他事务对该行数据加排他锁进行写操作。例如，多个事务可以同时读取同一行数据，但在这些共享锁释放之前，其他事务不能对该行数据进行修改。

-- 会话 1
-- 启动一个事务
START TRANSACTION;
-- 对表 t 中 id 为 1 的行加共享锁并查询
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;
-- 模拟长时间操作
SELECT SLEEP(30);
-- 提交事务
COMMIT;

-- 会话 2
-- 尝试在会话 1 持有共享锁期间对表 t 中 id 为 1 的行加共享锁并查询
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; 
-- 可以正常查询

-- 尝试在会话 1 持有共享锁期间对表 t 中 id 为 1 的行加排他锁并更新
UPDATE t SET column1 = 'new_value' WHERE id = 1; 
-- 此操作会被阻塞，直到会话 1 提交事务释放共享锁

• 排他锁（Exclusive Lock）：也称为写锁，当一个事务对某一行数据加上排他锁后，其他事务不能再对该行数据加任何类型的锁，包括共享锁和排他锁，直到该排他锁被释放。常用于 INSERT、UPDATE、DELETE 等操作。

-- 会话 1
-- 启动一个事务
START TRANSACTION;
-- 对表 t 中 id 为 1 的行加排他锁并查询
SELECT * FROM t WHERE id = 1 FOR UPDATE;
-- 模拟长时间操作
SELECT SLEEP(30);
-- 提交事务
COMMIT;

-- 会话 2
-- 尝试在会话 1 持有排他锁期间对表 t 中 id 为 1 的行加共享锁并查询
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; 
-- 此操作会被阻塞，直到会话 1 提交事务释放排他锁

-- 尝试在会话 1 持有排他锁期间对表 t 中 id 为 1 的行加排他锁并更新
UPDATE t SET column1 = 'new_value' WHERE id = 1; 
-- 此操作会被阻塞，直到会话 1 提交事务释放排他锁

适用场景 适用于并发访问频繁，对数据一致性要求较高的场景，如在线交易系统。在这种系统中，多个用户可能同时对不同的账户进行操作，行级锁可以保证不同用户的操作不会相互干扰，同时保证数据的一致性。

页级锁

• 特点
• 粒度适中：页级锁的粒度介于表级锁和行级锁之间，它会对数据库中的一个数据页进行锁定。一个数据页通常包含多行数据，因此页级锁的并发度比表级锁高，但比行级锁低。
• 开销适中：加锁和解锁的开销介于表级锁和行级锁之间。因为需要管理的数据页数量比表的数量多，但比行的数量少，所以系统开销相对适中。
• 适用场景: 页级锁在一些特定的数据库系统中使用，适用于一些对并发性能有一定要求，但又不希望像行级锁那样带来过高开销的场景。不过，由于其并发度不如行级锁，目前在实际应用中使用相对较少。

-- 会话 1
-- 启动一个事务
START TRANSACTION;
-- 对 id 为 1 的记录进行更新操作，可能会对包含该记录的页加页级锁
UPDATE page_table SET name = 'New Alice' WHERE id = 1;
-- 模拟长时间操作
SELECT SLEEP(30);
-- 提交事务
COMMIT;

-- 会话 2
-- 尝试在会话 1 持有页级锁期间对 id 为 2 的记录进行更新操作
UPDATE page_table SET name = 'New Bob' WHERE id = 2; 
-- 由于 id 为 1 和 2 的记录可能在同一页，此操作可能会被阻塞，直到会话 1 提交事务释放页级锁