PostgreSQL 锁与并发

锁与并发基础

在多用户环境中，PostgreSQL使用锁机制来控制对共享资源的访问，确保数据的一致性和完整性。理解PostgreSQL的锁机制对于编写高效、并发的应用程序至关重要。

并发控制的目标

一致性：确保数据在并发访问下保持一致
隔离性：确保每个事务看到的数据是一致的
可靠性：确保事务能够正确执行，不会因为并发而失败
性能：在保证一致性和隔离性的前提下，最大化系统的并发性能

PostgreSQL的并发控制机制

PostgreSQL使用多种并发控制机制：

MVCC（多版本并发控制）：允许读取和写入操作同时进行，互不阻塞
锁机制：用于控制对共享资源的访问
事务隔离级别：控制事务之间的可见性

锁类型

1. 表级锁

表级锁用于控制对整个表的访问，包括：

锁类型	缩写	描述	冲突锁
ACCESS SHARE	AS	允许读取表数据，阻止排他锁	EXCLUSIVE
ROW SHARE	RS	允许读取和插入数据，阻止排他锁	EXCLUSIVE
ROW EXCLUSIVE	RX	允许读取、插入、更新和删除数据，阻止排他锁	EXCLUSIVE
SHARE UPDATE EXCLUSIVE	SUX	允许读取和写入数据，阻止其他SUX锁和更高级别的锁	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE	S	允许读取数据，阻止写入操作	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE ROW EXCLUSIVE	SRX	允许读取数据，阻止写入操作和其他SHARE锁	ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, EXCLUSIVE, ACCESS EXCLUSIVE
EXCLUSIVE	X	允许读取和写入数据，阻止其他所有锁	所有锁
ACCESS EXCLUSIVE	AX	完全阻止其他所有操作	所有锁

2. 行级锁

行级锁用于控制对表中特定行的访问，包括：

FOR UPDATE：锁定行，防止其他事务修改或删除
FOR NO KEY UPDATE：锁定行，防止其他事务修改主键或唯一键
FOR SHARE：锁定行，允许其他事务读取，但阻止修改
FOR KEY SHARE：锁定行，允许其他事务读取和修改非主键列

3. 其他锁类型

页级锁：用于控制对数据页的访问
索引锁：用于控制对索引的访问
事务ID锁：用于控制事务之间的依赖关系
advisory锁：用户自定义的锁，用于应用程序级别的并发控制

锁管理

1. 查看锁信息

PostgreSQL提供了多种视图来查看锁信息：

sql

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

-- 查看锁和对应的进程信息
SELECT 
  l.locktype, l.database, l.relation, l.page, l.tuple,
  l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid,
  l.pid, l.mode, l.granted,
  a.datname, a.usename, a.application_name, a.client_addr, a.client_port, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
ORDER BY l.pid;

-- 查看阻塞的锁
SELECT 
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocking_activity.query AS blocking_query,
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocked_activity.query AS blocked_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_locks blocking_locks ON (
  blocked_locks.locktype = blocking_locks.locktype AND
  blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database AND
  blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation AND
  blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page AND
  blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple AND
  blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid AND
  blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid AND
  blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid AND
  blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid AND
  blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid AND
  blocked_locks.pid != blocking_locks.pid
)
JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

2. 锁等待超时配置

可以通过配置lock_timeout参数来设置锁等待超时时间：

sql

-- 在postgresql.conf中配置
lock_timeout = 1000  -- 1秒

-- 或在会话级别设置
SET lock_timeout = '1s';

3. 死锁检测

PostgreSQL自动检测死锁，并终止其中一个事务来解决死锁：

sql

-- 在postgresql.conf中配置死锁检测间隔
deadlock_timeout = 1000  -- 1秒

并发控制最佳实践

1. 使用合适的事务隔离级别

根据应用程序的需求选择合适的事务隔离级别：

READ COMMITTED：默认级别，允许读取已提交的数据
REPEATABLE READ：确保同一事务中的多次读取返回相同的结果
SERIALIZABLE：最高隔离级别，确保事务串行执行

sql

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. 最小化事务持有时间

减少事务持有的时间，避免长时间占用锁：

sql

-- 不好的写法
BEGIN;
-- 长时间的业务逻辑
SELECT * FROM users WHERE id = 1;
-- 长时间的业务逻辑
UPDATE users SET name = 'John' WHERE id = 1;
COMMIT;

-- 好的写法
-- 先执行业务逻辑
-- ...
BEGIN;
SELECT * FROM users WHERE id = 1;
UPDATE users SET name = 'John' WHERE id = 1;
COMMIT;

3. 避免长事务

长事务会持有锁很长时间，影响系统的并发性能：

sql

-- 查看长事务
SELECT 
  pid, usename, datname, application_name, client_addr,
  backend_start, xact_start, state,
  NOW() - xact_start AS duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - xact_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

4. 使用行级锁代替表级锁

尽量使用行级锁代替表级锁，减少锁的粒度：

sql

-- 不好的写法（表级锁）
LOCK TABLE users IN EXCLUSIVE MODE;
UPDATE users SET name = 'John' WHERE id = 1;

-- 好的写法（行级锁）
UPDATE users SET name = 'John' WHERE id = 1;

5. 合理使用锁语句

根据需求选择合适的锁语句：

sql

-- 锁定行，防止其他事务修改
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 锁定行，允许其他事务读取，但阻止修改
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- 锁定行，防止其他事务修改主键或唯一键
SELECT * FROM users WHERE id = 1 FOR NO KEY UPDATE;

-- 锁定行，允许其他事务读取和修改非主键列
SELECT * FROM users WHERE id = 1 FOR KEY SHARE;

6. 避免锁升级

锁升级是指将行级锁升级为表级锁，会显著降低系统的并发性能。避免锁升级的方法包括：

减少单次操作的行数
使用批量操作代替单次操作
调整max_locks_per_transaction参数

锁优化实战

示例1：优化长时间运行的事务

sql

-- 慢事务
BEGIN;
-- 长时间的业务逻辑
SELECT * FROM orders WHERE order_date > '2023-01-01';
-- 长时间的业务逻辑
UPDATE orders SET status = 'processed' WHERE order_date > '2023-01-01';
COMMIT;

-- 优化后的事务
-- 先执行业务逻辑，准备好要更新的数据
-- ...
BEGIN;
-- 快速锁定要更新的行
SELECT id FROM orders WHERE order_date > '2023-01-01' FOR UPDATE;
-- 快速更新数据
UPDATE orders SET status = 'processed' WHERE order_date > '2023-01-01';
COMMIT;

示例2：使用合理的锁级别

sql

-- 不好的写法（使用排他锁）
LOCK TABLE users IN EXCLUSIVE MODE;
SELECT * FROM users WHERE department = 'IT';

-- 好的写法（使用共享锁或不使用锁）
SELECT * FROM users WHERE department = 'IT';

示例3：避免死锁

死锁通常发生在两个或多个事务相互等待对方释放锁的情况下。避免死锁的方法包括：

以相同的顺序访问资源
减少事务的持有时间
使用较低的事务隔离级别
合理设置锁等待超时

sql

-- 事务1
BEGIN;
UPDATE users SET name = 'John' WHERE id = 1;
-- 延迟，模拟业务逻辑
SELECT pg_sleep(1);
UPDATE users SET name = 'Jane' WHERE id = 2;
COMMIT;

-- 事务2
BEGIN;
UPDATE users SET name = 'Jane' WHERE id = 2;
-- 延迟，模拟业务逻辑
SELECT pg_sleep(1);
UPDATE users SET name = 'John' WHERE id = 1;
COMMIT;

上述两个事务可能会发生死锁，因为它们以相反的顺序访问资源。优化方法是让它们以相同的顺序访问资源：

sql

-- 事务1
BEGIN;
UPDATE users SET name = 'John' WHERE id = 1;
UPDATE users SET name = 'Jane' WHERE id = 2;
COMMIT;

-- 事务2
BEGIN;
UPDATE users SET name = 'John' WHERE id = 1;
UPDATE users SET name = 'Jane' WHERE id = 2;
COMMIT;

版本差异

PostgreSQL 12+ 并发控制增强

并行查询增强：支持更多类型的并行操作，提高并发查询性能
增量排序：提高排序操作的效率，减少锁的持有时间
执行计划缓存改进：减少计划生成的开销，提高并发性能

PostgreSQL 13+ 并发控制增强

JIT编译：支持即时编译，加速某些查询的执行，减少锁的持有时间
分区表增强：提高分区表的并发性能，支持更多类型的分区
锁管理增强：改进锁的管理，减少锁的开销

PostgreSQL 14+ 并发控制增强

逻辑复制增强：提高逻辑复制的并发性能
索引维护增强：减少索引维护时的锁持有时间
执行计划增强：提供更详细的执行计划信息，便于分析并发问题

常见问题（FAQ）

Q1: 如何查看当前的锁信息？

A1: 可以使用pg_locks视图查看当前的锁信息，结合pg_stat_activity视图可以查看锁对应的进程和查询信息：

sql

SELECT 
  l.locktype, l.database, l.relation, l.mode, l.granted,
  a.datname, a.usename, a.application_name, a.client_addr, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
ORDER BY l.pid;

Q2: 如何处理锁等待？

A2: 可以尝试以下方法处理锁等待：

查看阻塞的进程，终止阻塞的事务
调整锁等待超时时间
优化查询，减少锁的持有时间
调整事务隔离级别

Q3: 如何避免死锁？

A3: 可以尝试以下方法避免死锁：

以相同的顺序访问资源
减少事务的持有时间
使用较低的事务隔离级别
合理设置锁等待超时
使用行级锁代替表级锁

Q4: 什么是MVCC？如何工作？

A4: MVCC（多版本并发控制）是PostgreSQL使用的并发控制机制，允许读取和写入操作同时进行，互不阻塞。MVCC通过为每行数据维护多个版本来实现，读取操作只能看到已提交的数据版本，写入操作会创建新的数据版本。

Q5: 如何查看长事务？

A5: 可以使用以下查询查看长事务：

sql

SELECT 
  pid, usename, datname, application_name, client_addr,
  backend_start, xact_start, state,
  NOW() - xact_start AS duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND NOW() - xact_start > INTERVAL '5 minutes'
ORDER BY duration DESC;

Q6: 如何优化锁性能？

A6: 可以尝试以下方法优化锁性能：

减少事务的持有时间
使用行级锁代替表级锁
合理设置事务隔离级别
避免长事务
调整max_locks_per_transaction参数
优化查询，减少锁的粒度

总结

锁与并发是PostgreSQL数据库性能优化的重要方面，理解PostgreSQL的锁机制对于编写高效、并发的应用程序至关重要。在实际生产环境中，应该合理使用锁机制，优化事务设计，减少锁的持有时间，避免死锁，以提高系统的并发性能。

锁与并发优化的关键是：

理解PostgreSQL的锁类型和锁机制
合理使用MVCC和锁机制
优化事务设计，减少锁的持有时间
避免长事务和死锁
监控锁信息，及时发现和解决锁问题

通过不断的分析和优化，可以使PostgreSQL数据库在高并发环境中获得更好的性能表现，提高系统的整体性能和稳定性。

PostgreSQL 锁与并发 ​

锁与并发基础 ​

并发控制的目标 ​

PostgreSQL的并发控制机制 ​

锁类型 ​

1. 表级锁 ​

2. 行级锁 ​

3. 其他锁类型 ​

锁管理 ​

1. 查看锁信息 ​

2. 锁等待超时配置 ​

3. 死锁检测 ​

并发控制最佳实践 ​

1. 使用合适的事务隔离级别 ​

2. 最小化事务持有时间 ​

3. 避免长事务 ​

4. 使用行级锁代替表级锁 ​

5. 合理使用锁语句 ​

6. 避免锁升级 ​

锁优化实战 ​

示例1：优化长时间运行的事务 ​

示例2：使用合理的锁级别 ​

示例3：避免死锁 ​

版本差异 ​

PostgreSQL 12+ 并发控制增强 ​

PostgreSQL 13+ 并发控制增强 ​

PostgreSQL 14+ 并发控制增强 ​

常见问题（FAQ） ​

Q1: 如何查看当前的锁信息？ ​

Q2: 如何处理锁等待？ ​

Q3: 如何避免死锁？ ​

Q4: 什么是MVCC？如何工作？ ​

Q5: 如何查看长事务？ ​

Q6: 如何优化锁性能？ ​

总结 ​