当前位置：首页 > article >正文

数据库【锁】的分类

article 2024/11/24 19:14:51

数据库锁的分类与SQL实现详解

锁是数据库并发控制的核心工具，通过控制事务对数据的访问来确保数据的一致性和隔离性。以下是常见锁的分类及其详细解释，并附带关键代码标识的 SQL 示例。

1. 行锁（Row-level Lock）

定义：只锁定表中的特定行，其他行不受影响。适用于高并发场景。
适用场景：修改或查询特定记录时，确保其他事务不会同时操作这些行。

关键代码

-- 开始事务
START TRANSACTION;

-- 查询并加锁特定行（关键代码）
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- 修改被锁定的行
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 提交事务，释放锁
COMMIT;

2. 表锁（Table-level Lock）

定义：锁定整个表，防止其他事务对该表进行任何操作。适用于全表操作。
适用场景：对全表进行批量操作或需要防止其他事务访问整个表时使用。

关键代码

-- 加表锁（关键代码）
LOCK TABLE accounts WRITE;

-- 全表操作
UPDATE accounts SET balance = balance + 100;

-- 释放表锁
UNLOCK TABLES;

3. 共享锁（Shared Lock, S锁）

定义：允许多个事务同时读取数据，但禁止写入，确保数据在读取时不会被修改。
适用场景：报表生成或分析场景，防止数据被修改。

关键代码

-- 开始事务
START TRANSACTION;

-- 加共享锁（关键代码）
SELECT * FROM accounts WHERE account_id = 1 LOCK IN SHARE MODE;

-- 提交事务，释放共享锁
COMMIT;

4. 排他锁（Exclusive Lock, X锁）

定义：一个事务加排他锁后，禁止其他事务对该数据进行读写。适用于修改数据的场景。
适用场景：确保数据在修改过程中不会被其他事务读取或写入。

关键代码

-- 开始事务
START TRANSACTION;

-- 查询并加排他锁（关键代码）
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- 修改被锁定的数据
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 提交事务，释放排他锁
COMMIT;

5. 间隙锁（Gap Lock）

定义：锁定查询范围的间隙，防止其他事务在该范围内插入新数据。用于避免幻读。
适用场景：范围查询的事务，特别是在可重复读（REPEATABLE READ）隔离级别下。

关键代码

-- 开始事务
START TRANSACTION;

-- 查询范围并加间隙锁（关键代码）
SELECT * FROM accounts WHERE account_id BETWEEN 1 AND 10 FOR UPDATE;

-- 在范围内的插入操作会被阻塞
COMMIT;

锁的分类与实现总结表

锁类型	定义	使用场景	关键代码
行锁	锁定表中的某些特定行	修改或查询特定记录时	`SELECT ... WHERE ... FOR UPDATE;`
表锁	锁定整张表，防止其他事务访问	全表操作或批量更新	`LOCK TABLE table_name WRITE;`
共享锁 (S锁)	允许多个事务同时读取，但禁止写入	报表生成、数据分析	`SELECT ... LOCK IN SHARE MODE;`
排他锁 (X锁)	禁止其他事务对加锁数据进行读写	修改数据时，防止并发冲突	`SELECT ... WHERE ... FOR UPDATE;`
间隙锁	锁定查询范围的间隙，防止插入新数据	避免幻读问题	`SELECT ... WHERE ... BETWEEN ... FOR UPDATE;`