# 配置锁参数
# 设置死锁检测时间
gs_guc set -D /opengauss/fgdata -c “deadlock_timeout = ‘1s'”

# 设置锁等待超时时间
gs_guc set -D /opengauss/fgdata -c “lock_timeout = ‘5s'”

# 设置最大锁数量
gs_guc set -D /opengauss/fgdata -c “max_locks_per_transaction = 64”

# 重启数据库使参数生效
gs_ctl restart -D /opengauss/fgdata

# 查看锁情况
SELECT * FROM pg_locks;

# 查看锁等待情况
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.GRANTED;

# 手动释放锁
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);
学习交流加群风哥微信: itpux-com

# 优化查询语句，减少锁竞争
# 优化前：全表扫描
SELECT * FROM fgedu.fgedu_test WHERE name = ‘test’;

# 优化后：使用索引
CREATE INDEX idx_fgedu_test_name ON fgedu.fgedu_test(name);
SELECT * FROM fgedu.fgedu_test WHERE name = ‘test’;

# 减少事务持有锁的时间
# 优化前：长事务
BEGIN;
UPDATE fgedu.fgedu_test SET name = ‘updated’ WHERE id = 1;
SELECT pg_sleep(10);
COMMIT;

# 优化后：短事务
BEGIN;
UPDATE fgedu.fgedu_test SET name = ‘updated’ WHERE id = 1;
COMMIT;

# 避免死锁
# 优化前：循环依赖
# 事务1
BEGIN;
UPDATE fgedu.fgedu_test SET name = ‘updated1’ WHERE id = 1;
SELECT pg_sleep(1);
UPDATE fgedu.fgedu_test SET name = ‘updated2’ WHERE id = 2;
COMMIT;

# 事务2
BEGIN;
UPDATE fgedu.fgedu_test SET name = ‘updated2’ WHERE id = 2;
SELECT pg_sleep(1);
UPDATE fgedu.fgedu_test SET name = ‘updated1’ WHERE id = 1;
COMMIT;
学习交流加群风哥QQ113257174
# 优化后：按顺序更新
# 事务1和事务2都按id顺序更新
BEGIN;
UPDATE fgedu.fgedu_test SET name = ‘updated1’ WHERE id = 1;
UPDATE fgedu.fgedu_test SET name = ‘updated2’ WHERE id = 2;
COMMIT;