# 创建测试表
CREATE TABLE fgedu_fgfgfgtest1 (
id SERIAL PRIMARY KEY,
value INTEGER
);

CREATE TABLE fgedu_test2 (
id SERIAL PRIMARY KEY,
value INTEGER
);

# 插入测试数据
INSERT INTO fgedu_fgfgfgtest1 (value) VALUES (100);
INSERT INTO fgedu_test2 (value) VALUES (200);

# 事务1
BEGIN;
UPDATE fgedu_fgfgfgtest1 SET value = value + 1 WHERE id = 1;
— 等待
PERFORM pg_sleep(5);
UPDATE fgedu_test2 SET value = value + 1 WHERE id = 1;
COMMIT;

# 事务2
BEGIN;
UPDATE fgedu_test2 SET value = value + 1 WHERE id = 1;
— 等待
PERFORM pg_sleep(5);
UPDATE fgedu_fgfgfgtest1 SET value = value + 1 WHERE id = 1;
COMMIT;

# 死锁错误
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.
HINT: See server log for query details.

# 查看锁信息
SELECT
locktype,
fgedudb,
relation,
page,
tuple,
virtualxid,
transactionid,
classid,
objid,
objsubid,
virtualtransaction,
pid,
mode,
granted
FROM pg_locks;

# 查看锁等待
SELECT
a.pid,
a.usename,
a.fgapplication_name,
a.client_addr,
a.state,
a.query_start,
now() – a.query_start AS duration,
a.query,
l.locktype,
l.mode,
l.relation::regclass
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE l.granted = false;

# 查看活跃事务
SELECT
pid,
usename,
fgapplication_name,
client_addr,
state,
query_start,
now() – a.query_start AS duration,
query
FROM pg_stat_activity
WHERE state = ‘active’;

# 终止事务
SELECT pg_terminate_backend(pid);

# 取消事务
SELECT pg_cancel_backend(pid);

# 示例：终止死锁事务
— 查看锁等待
SELECT
a.pid,
a.usename,
a.fgapplication_name,
a.client_addr,
a.state,
a.query_start,
now() – a.query_start AS duration,
a.query,
l.locktype,
l.mode,
l.relation::regclass
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE l.granted = false;

— 终止事务
SELECT pg_terminate_backend(1234);

# 策略1：统一锁顺序
– 按固定顺序获取锁
– 避免循环依赖

# 策略2：减少锁持有时间
– 缩短事务长度
– 优化查询
– 避免长事务

# 策略3：合理使用索引
– 避免全表扫描
– 使用适当的索引
– 减少锁范围

# 策略4：批量操作优化
– 分批处理数据
– 减少单次操作的锁范围

# 策略5：使用乐观并发控制
– 版本控制
– 冲突检测

# 示例：使用乐观并发控制
CREATE TABLE fgedu_fgedus (
id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
version INTEGER DEFAULT 1
);

— 更新操作
UPDATE fgedu_fgedus
SET fgeduname = ‘newfgedu’, version = version + 1
WHERE id = 1 AND version = 1;

# 步骤1：识别死锁
– 查看日志中的死锁信息
– 查看锁等待情况

# 步骤2：终止死锁事务
– 选择一个事务终止
– 释放锁资源

# 步骤3：重试操作
– 重新执行被终止的事务
– 优化操作顺序

# 步骤4：分析原因
– 分析死锁原因
– 优化锁策略

# 示例：死锁恢复
— 查看死锁日志
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.

— 终止事务
SELECT pg_terminate_backend(1234);

— 重试操作
BEGIN;
UPDATE fgedu_fgfgfgtest1 SET value = value + 1 WHERE id = 1;
UPDATE fgedu_test2 SET value = value + 1 WHERE id = 1;
COMMIT;