锁机制与死锁

什么是锁

锁（Lock）是数据库用来控制并发访问的机制，确保多个事务不会相互干扰。

两种基本锁

共享锁（Shared Lock，S锁）：读锁

• 多个事务可以同时持有
• 允许读取，禁止修改

排他锁（Exclusive Lock，X锁）：写锁

• 只有一个事务可以持有
• 独占访问，禁止其他读写

显式加锁

SELECT FOR UPDATE：排他锁

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 其他事务无法读取或修改这行（等待）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

应用场景：秒杀、库存扣减

SELECT FOR SHARE：共享锁

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 其他事务可以读取，但无法修改这行
COMMIT;

PostgreSQL 语法：FOR SHARE
MySQL 语法：LOCK IN SHARE MODE

锁的粒度

行锁（Row Lock）

锁定单行数据，并发度最高。

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 只锁定 id=1 这一行

表锁（Table Lock）

锁定整张表，并发度最低。

-- PostgreSQL
LOCK TABLE accounts IN EXCLUSIVE MODE;

-- MySQL
LOCK TABLES accounts WRITE;
-- 操作...
UNLOCK TABLES;

意向锁（Intention Lock）

表级锁，标识事务即将在表的某些行上加锁。

• IS（Intention Shared）：即将加 S 锁
• IX（Intention Exclusive）：即将加 X 锁

自动加上，用户无需手动操作。

死锁

死锁：两个或多个事务互相等待对方释放锁，导致永久等待。

死锁示例

-- 会话 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁定 id=1

-- 会话 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- 锁定 id=2

-- 会话 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2

-- 会话 2
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- 等待 id=1（死锁！）

数据库检测到死锁，自动回滚一个事务：

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678;
         Process 5678 waits for ShareLock on transaction 1234.

死锁的原因

1. 资源竞争：多个事务以不同顺序访问资源
2. 持有并等待：持有锁的同时等待其他锁
3. 不可剥夺：锁不能被强制释放
4. 循环等待：形成等待环路

避免死锁

1. 按相同顺序访问资源

-- 总是先锁定 id 小的行
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

2. 减少事务持有时间

-- 尽快提交
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 立即释放锁

3. 使用较低隔离级别

READ COMMITTED 比 SERIALIZABLE 更少死锁。

4. 设置锁超时

-- PostgreSQL
SET lock_timeout = '5s';

-- MySQL
SET innodb_lock_wait_timeout = 5;

处理死锁

import psycopg2

def transfer_with_retry(from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            conn = psycopg2.connect("dbname=mydb")
            cur = conn.cursor()
            cur.execute("BEGIN")
            cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
            cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))
            conn.commit()
            return True
        except psycopg2.extensions.TransactionRollbackError:
            # 死锁，重试
            conn.rollback()
            if attempt == max_retries - 1:
                raise
        finally:
            conn.close()

锁的查看

PostgreSQL

-- 查看当前锁
SELECT * FROM pg_locks;

-- 查看阻塞
SELECT blocked.pid, blocked.query, blocking.pid AS blocking_pid
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

MySQL

-- 查看锁等待
SELECT * FROM performance_schema.data_locks;

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

乐观锁 vs 悲观锁

悲观锁：先锁后操作

BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;  -- 加锁
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

适用于冲突频繁的场景。

乐观锁：版本号

-- 添加版本列
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

-- 应用层实现
BEGIN;
SELECT stock, version FROM products WHERE id = 1;  -- version = 5

UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;  -- 只有版本匹配才更新

-- 检查影响行数
IF affected_rows = 0 THEN
    ROLLBACK;  -- 版本冲突，重试
ELSE
    COMMIT;
END IF;

适用于冲突较少的场景。

性能优化

1. 缩短事务时间：减少锁持有时间
2. 使用合适的索引：减少锁定的行数
3. 避免大事务：分批处理
4. 选择合适的隔离级别：不要过度隔离
5. 使用乐观锁：减少锁竞争

练习

1. 显式加锁：使用 FOR UPDATE 锁定一行，观察其他会话的阻塞。
2. 死锁复现：按上述示例复现死锁场景。
3. 死锁避免：修改访问顺序避免死锁。
4. 锁超时：设置锁超时，观察超时行为。
5. 查看锁：查询当前数据库的锁信息。
6. 乐观锁实现：使用版本号实现乐观锁。
7. 性能对比：对比悲观锁和乐观锁的性能。
8. 实际场景：设计秒杀系统，使用悲观锁防止超卖。

完成练习后，打开本章答案页核对。

下一步

学会了锁机制。下一节学习 MVCC：理解多版本并发控制如何在不加锁的情况下实现高并发。

回到第五章概览 · 返回SQL 学习路线