MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

PostgreSQL死锁避免的最佳实践

2022-06-167.6k 阅读

一、理解死锁

1.1 死锁的定义

在 PostgreSQL 数据库中，死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，这些事务都将无法推进下去。例如，事务 A 持有资源 R1 并请求资源 R2，而事务 B 持有资源 R2 并请求资源 R1，此时 A 和 B 就陷入了死锁。

1.2 死锁产生的条件

  • 互斥条件：资源在某一时刻只能被一个事务占用。例如，一个数据库表中的一行数据，在被一个事务以排他锁的方式锁住时，其他事务不能同时以排他锁的方式获取。
  • 占有并等待条件：事务已经占有了至少一个资源，但又请求新的资源，而新资源被其他事务占有，从而导致事务等待。例如，事务 A 已经锁住了表 table1 的一行数据，现在又尝试锁住 table2 的一行数据，而 table2 的那行数据被事务 B 锁住。
  • 不可剥夺条件：事务所获得的资源在未使用完之前，不能被其他事务强行剥夺，只能由获得资源的事务自己释放。在 PostgreSQL 中，一旦事务获取了锁，其他事务不能强制将其释放。
  • 循环等待条件：存在一个事务链，链中的每个事务都在等待下一个事务所占有的资源，形成一个循环等待的局面。比如，事务 T1 等待 T2 持有的资源，T2 等待 T3 持有的资源，而 T3 又等待 T1 持有的资源。

二、PostgreSQL 死锁检测机制

2.1 自动死锁检测

PostgreSQL 具备自动死锁检测机制。当一个事务请求锁时，如果等待时间超过了内部设定的阈值（默认为 1 秒），数据库会启动死锁检测。检测过程中，系统会检查事务等待图，如果发现图中存在环，就判定发生了死锁。

2.2 死锁日志记录

一旦检测到死锁，PostgreSQL 会选择一个事务作为牺牲品（victim）回滚。同时，在日志文件（通常是 postgresql.log）中会记录死锁相关信息，包括死锁发生的时间、涉及的事务 ID、锁请求等内容。以下是死锁日志的示例：

2023-10-01 12:34:56 UTC [12345] ERROR:  deadlock detected
2023-10-01 12:34:56 UTC [12345] DETAIL:  Process 12346 waits for ShareLock on transaction 56789; blocked by process 12347.
Process 12347 waits for ShareLock on transaction 56790; blocked by process 12346.
2023-10-01 12:34:56 UTC [12345] HINT:  See server log for query details.
2023-10-01 12:34:56 UTC [12345] CONTEXT:  while waiting for ShareLock on transaction 56789
2023-10-01 12:34:56 UTC [12345] STATEMENT:  UPDATE table1 SET column1 = 'value' WHERE id = 1;

从日志中可以清晰看到死锁涉及的进程、等待的锁类型以及相关事务 ID，还能看到导致死锁的具体 SQL 语句。

三、死锁避免的最佳实践

3.1 优化事务设计

3.1.1 减少事务持有锁的时间

尽可能缩短事务的执行时间，减少其持有锁的时长。例如，将大事务拆分成多个小事务。假设我们有一个业务需求，要对一个大型订单表 orders 进行更新，同时更新相关的订单详情表 order_details。

-- 长事务示例
BEGIN;
UPDATE orders SET status = 'processed' WHERE order_id = 12345;
-- 可能有一些复杂的业务逻辑计算
UPDATE order_details SET processed = true WHERE order_id = 12345;
COMMIT;

可以将其拆分成两个小事务：

-- 第一个小事务
BEGIN;
UPDATE orders SET status = 'processed' WHERE order_id = 12345;
COMMIT;

-- 第二个小事务
BEGIN;
UPDATE order_details SET processed = true WHERE order_id = 12345;
COMMIT;

这样每个小事务持有锁的时间大大缩短，降低了死锁发生的概率。

3.1.2 按相同顺序访问资源

确保所有事务按照相同的顺序访问共享资源。例如，在涉及多个表操作时，所有事务都先访问表 A，再访问表 B，以此类推。假设我们有两个表 customers 和 orders，并且有两个事务：

-- 事务 1
BEGIN;
UPDATE customers SET last_login = current_timestamp WHERE customer_id = 1;
UPDATE orders SET status = 'new' WHERE customer_id = 1;
COMMIT;

-- 事务 2
BEGIN;
UPDATE customers SET last_login = current_timestamp WHERE customer_id = 2;
UPDATE orders SET status = 'new' WHERE customer_id = 2;
COMMIT;

如果事务 2 先更新 orders 表，再更新 customers 表，就可能导致死锁。通过统一资源访问顺序，可以有效避免这种情况。

3.2 合理使用锁

3.2.1 选择合适的锁模式

PostgreSQL 提供了多种锁模式，如共享锁（SHARE）、排他锁（EXCLUSIVE）等。根据业务需求，选择合适的锁模式。如果只是读取数据，并且允许其他事务同时读取，可以使用共享锁。例如：

BEGIN;
SELECT * FROM products WITH (SHARE) WHERE category = 'electronics';
-- 进行一些只读操作
COMMIT;

如果需要修改数据，就需要使用排他锁，以防止其他事务同时修改：

BEGIN;
UPDATE products SET price = price * 1.1 WHERE category = 'electronics' WITH (EXCLUSIVE);
COMMIT;

3.2.2 尽量使用行级锁而非表级锁

行级锁只锁定需要操作的行，而表级锁会锁定整个表。显然，行级锁的粒度更细，并发性能更好。例如，在更新用户表 users 中的某一行数据时：

BEGIN;
UPDATE users SET email = 'new_email@example.com' WHERE user_id = 1;
COMMIT;

PostgreSQL 默认使用行级锁，这种情况下不会影响表中其他行的并发操作。而如果使用表级锁：

BEGIN;
LOCK TABLE users IN EXCLUSIVE MODE;
UPDATE users SET email = 'new_email@example.com' WHERE user_id = 1;
COMMIT;

此时整个 users 表被锁住，其他事务对该表的任何操作都要等待锁释放，大大降低了并发性能，同时增加了死锁的风险。

3.3 配置参数优化

3.3.1 调整死锁检测阈值

可以通过修改 postgresql.conf 中的 deadlock_timeout 参数来调整死锁检测的阈值。默认值是 1000 毫秒（1 秒）。如果业务场景中，事务等待时间通常较短，且不希望频繁触发死锁检测，可以适当降低该值；如果事务执行时间较长，且不希望过早判定死锁，可以适当提高该值。

修改 postgresql.conf 文件：

deadlock_timeout = 500  # 降低到 500 毫秒

修改后，需要重启 PostgreSQL 服务使配置生效。

3.3.2 优化共享内存参数

共享内存参数对于 PostgreSQL 的并发性能有重要影响。例如，shared_buffers 参数决定了 PostgreSQL 用于缓存数据库页面的共享内存大小。适当增加 shared_buffers 的值，可以提高数据的读取性能，减少磁盘 I/O，从而降低事务执行时间，间接减少死锁发生的可能性。

在 postgresql.conf 文件中修改：

shared_buffers = '2GB'  # 根据服务器内存情况调整

同样，修改后需要重启服务。

3.4 监控与调优

3.4.1 使用 PostgreSQL 自带工具监控锁

可以使用 pg_locks 视图来监控当前数据库中的锁信息。例如，查询当前持有锁的事务：

SELECT
    locktype,
    database,
    relation::regclass,
    page,
    tuple,
    virtualxid,
    transactionid,
    pid,
    mode,
    granted
FROM
    pg_locks
WHERE
    granted = true;

通过定期查询这个视图，可以及时发现长时间持有锁的事务，进而分析是否存在潜在的死锁风险。

3.4.2 性能分析工具

使用 pg_stat_statements 扩展来分析 SQL 语句的执行性能。安装扩展：

CREATE EXTENSION pg_stat_statements;

然后可以查询执行频率高、执行时间长的 SQL 语句：

SELECT
    calls,
    total_time,
    mean_time,
    query
FROM
    pg_stat_statements
ORDER BY
    total_time DESC;

对这些语句进行优化，如添加合适的索引、优化查询逻辑等，有助于减少事务执行时间，降低死锁发生概率。

四、死锁问题排查与解决

4.1 从日志分析入手

当死锁发生后，首先查看 postgresql.log 文件中的死锁记录。根据日志中提供的事务 ID、进程 ID、锁请求等信息，分析死锁发生的原因。例如，从以下日志片段：

2023-10-02 09:15:30 UTC [23456] ERROR:  deadlock detected
2023-10-02 09:15:30 UTC [23456] DETAIL:  Process 23457 waits for ExclusiveLock on relation 1663/12345/56789; blocked by process 23458.
Process 23458 waits for ExclusiveLock on relation 1663/12345/56790; blocked by process 23457.
2023-10-02 09:15:30 UTC [23456] HINT:  See server log for query details.
2023-10-02 09:15:30 UTC [23456] CONTEXT:  while waiting for ExclusiveLock on relation 1663/12345/56789
2023-10-02 09:15:30 UTC [23456] STATEMENT:  UPDATE products SET stock = stock - 1 WHERE product_id = 100;

可以看出，进程 23457 和 23458 因为争夺不同关系（表）上的排他锁而陷入死锁，并且导致死锁的 SQL 语句是更新 products 表的库存。

4.2 模拟死锁场景

在测试环境中，尝试模拟死锁场景，以便更好地理解和解决问题。例如，编写两个并发的事务脚本：

-- 事务脚本 1
BEGIN;
UPDATE table1 SET column1 = 'value1' WHERE id = 1;
-- 模拟一些延迟
SELECT pg_sleep(5);
UPDATE table2 SET column2 = 'value2' WHERE id = 1;
COMMIT;

-- 事务脚本 2
BEGIN;
UPDATE table2 SET column2 = 'value3' WHERE id = 1;
-- 模拟一些延迟
SELECT pg_sleep(5);
UPDATE table1 SET column1 = 'value4' WHERE id = 1;
COMMIT;

通过同时执行这两个脚本，观察死锁是否发生，并分析发生死锁的原因。可以根据模拟结果调整事务逻辑，如调整资源访问顺序、缩短事务执行时间等。

4.3 优化与验证

根据分析结果，对事务逻辑、锁使用、配置参数等进行优化。优化完成后，在测试环境中进行全面的测试，包括并发测试，验证死锁问题是否得到解决。可以使用工具如 pgbench 进行并发性能测试。例如，使用 pgbench 对一个简单的事务进行 100 个并发用户的测试：

pgbench -i -s 10 mydb  # 初始化测试数据
pgbench -c 100 -T 60 mydb  # 进行 60 秒的 100 并发测试

观察测试过程中是否还会出现死锁情况，以及系统的性能指标是否有所提升。

五、高并发场景下的特殊考虑

5.1 乐观锁与悲观锁

5.1.1 悲观锁

在高并发场景下，悲观锁是一种常用的锁策略。它假设数据冲突频繁发生，在操作数据前就获取锁，以防止其他事务同时修改。例如，在更新账户余额时：

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 假设获取到余额为 100，进行一些计算，新余额为 120
UPDATE accounts SET balance = 120 WHERE account_id = 1;
COMMIT;

这里的 FOR UPDATE 子句就是使用了悲观锁，在查询时就锁定了 accounts 表中 account_id 为 1 的行，其他事务不能同时修改该行数据。

5.1.2 乐观锁

乐观锁则假设数据冲突很少发生，在读取数据时不获取锁，直到提交事务时才检查数据是否被其他事务修改。在 PostgreSQL 中，可以通过版本号机制实现乐观锁。例如，在 products 表中添加一个 version 列：

-- 创建表时添加 version 列
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    version INT DEFAULT 0
);

-- 事务操作
BEGIN;
SELECT product_name, price, version FROM products WHERE product_id = 1;
-- 假设获取到 version 为 0，进行一些操作，修改价格
UPDATE products SET price = price * 1.1, version = version + 1 WHERE product_id = 1 AND version = 0;
IF NOT FOUND THEN
    -- 说明数据已被其他事务修改，处理冲突，例如重新读取数据并操作
    ROLLBACK;
ELSE
    COMMIT;
END IF;

在高并发场景下，乐观锁适合读多写少的场景，而悲观锁适合写多读少的场景。需要根据具体业务特点选择合适的锁策略。

5.2 分布式事务

在分布式系统中，多个 PostgreSQL 实例可能参与同一个事务，此时需要使用分布式事务管理。PostgreSQL 提供了 PREPARE TRANSACTION 语句来支持两阶段提交（2PC）。

假设我们有两个 PostgreSQL 实例，分别为 db1 和 db2，要在两个实例上同时更新数据：

-- 在 db1 上
BEGIN;
UPDATE table1 ON db1 SET column1 = 'value1' WHERE id = 1;
PREPARE TRANSACTION 'tx1';

-- 在 db2 上
BEGIN;
UPDATE table2 ON db2 SET column2 = 'value2' WHERE id = 1;
PREPARE TRANSACTION 'tx1';

-- 协调者（可以是另一个服务）
COMMIT PREPARED 'tx1';

在分布式事务中，死锁的处理更为复杂。需要在各个节点之间同步锁信息，确保分布式环境下的死锁检测和避免。可以通过引入分布式锁管理器（如 Redis 等）来辅助处理分布式死锁问题。

5.3 缓存与异步处理

5.3.1 缓存的应用

在高并发场景下，合理使用缓存可以减轻数据库的压力，减少事务冲突。例如，使用 Redis 缓存经常读取的数据。假设我们有一个新闻网站，新闻内容存储在 PostgreSQL 数据库中，并且访问频率很高。

import redis
import psycopg2

redis_client = redis.Redis(host='localhost', port=6379, db=0)

def get_news(news_id):
    news = redis_client.get(f'news:{news_id}')
    if news is None:
        conn = psycopg2.connect(database="news_db", user="user", password="password", host="localhost", port="5432")
        cur = conn.cursor()
        cur.execute("SELECT * FROM news WHERE news_id = %s", (news_id,))
        news = cur.fetchone()
        conn.close()
        if news:
            redis_client.setex(f'news:{news_id}', 3600, str(news))  # 缓存 1 小时
    return news

这样，大部分读请求可以从缓存中获取数据，减少了对数据库的读事务，降低了死锁风险。

5.3.2 异步处理

将一些非关键的业务操作异步化，通过消息队列（如 RabbitMQ、Kafka 等）来处理。例如，在一个电商系统中，用户下单后，除了更新订单状态等关键操作，还可能需要发送通知邮件、生成报表等操作。可以将这些操作放入消息队列，由专门的消费者异步处理。

import pika
import psycopg2

# 连接消息队列
connection = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
channel = connection.channel()
channel.queue_declare(queue='order_notification')

def process_order(order_id):
    # 处理订单核心事务
    conn = psycopg2.connect(database="ecommerce_db", user="user", password="password", host="localhost", port="5432")
    cur = conn.cursor()
    cur.execute("UPDATE orders SET status = 'processed' WHERE order_id = %s", (order_id,))
    conn.commit()
    conn.close()

    # 发送通知邮件等异步操作
    channel.basic_publish(exchange='', routing_key='order_notification', body=str(order_id))

process_order(12345)

通过异步处理，将复杂的业务逻辑分离，减少了事务的复杂度和执行时间，从而降低了死锁发生的概率。