## 1. 查看死锁日志
$ tail -n 100 /yashanb/app/yasdb/log/yasdb.log
2023-10-01 12:00:00.000 CST [12345] ERROR: deadlock detected
2023-10-01 12:00:00.000 CST [12345] DETAIL: Process 12345 waits for ShareLock on transaction 12346; blocked by process 12346.
Process 12346 waits for ShareLock on transaction 12345; blocked by process 12345.
2023-10-01 12:00:00.000 CST [12345] HINT: See server log for query details.
2023-10-01 12:00:00.000 CST [12345] CONTEXT: while updating tuple (0,1) in relation “fgedu_inventory”
2023-10-01 12:00:00.000 CST [12345] STATEMENT: UPDATE fgedu_inventory SET quantity = quantity – 1 WHERE product_id = 1;

## 2. 查看相关会话
$ /yashanb/app/yasdb/bin/psql -U yasdb -d fgedudb -c “SELECT pid, usename, datname, state, xact_start, query FROM pg_stat_activity WHERE pid IN (12345, 12346);”
pid | usename | datname | state | xact_start | query
——-+———+———-+——–+——————————-+——————————————–
12345 | fgedu | fgedudb | active | 2023-10-01 11:59:50.000000+08 | BEGIN; UPDATE fgedu_inventory SET quantity = quantity – 1 WHERE product_id = 1; UPDATE fgedu_inventory SET quantity = quantity + 1 WHERE product_id = 2;
12346 | fgedu | fgedudb | active | 2023-10-01 11:59:55.000000+08 | BEGIN; UPDATE fgedu_inventory SET quantity = quantity – 1 WHERE product_id = 2; UPDATE fgedu_inventory SET quantity = quantity + 1 WHERE product_id = 1;

## 3. 分析死锁原因
– 会话12345持有product_id=1的锁，等待product_id=2的锁
– 会话12346持有product_id=2的锁，等待product_id=1的锁
– 形成循环等待，导致死锁

## 4. 死锁分析脚本
$ cat > /usr/local/bin/deadlock_analysis.sh << 'EOF' #!/bin/bash # deadlock_analysis.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # 数据库连接信息 HOST="localhost" PORT="5432" DATABASE="fgedudb" USER="fgedu" PASSWORD="fgedu123" # 输出文件 OUTPUT_FILE="/var/log/yashanb/deadlock_analysis.log" # 确保输出目录存在 mkdir -p /var/log/yashanb # 记录时间 echo "[$(date '+%Y-%m-%d %H:%M:%S')] Deadlock analysis started" >> $OUTPUT_FILE

# 查看死锁日志
echo “Deadlock logs:” >> $OUTPUT_FILE
tail -n 50 /yashanb/app/yasdb/log/yasdb.log | grep -A 10 -B 2 “deadlock detected” >> $OUTPUT_FILE

# 查看当前锁情况
echo “\nCurrent locks:” >> $OUTPUT_FILE
psql -h $HOST -p $PORT -U $USER -d $DATABASE -c “SELECT * FROM pg_locks WHERE NOT granted;” >> $OUTPUT_FILE

# 查看活跃会话
echo “\nActive sessions:” >> $OUTPUT_FILE
psql -h $HOST -p $PORT -U $USER -d $DATABASE -c “SELECT pid, usename, datname, state, xact_start, query FROM pg_stat_activity WHERE state = ‘active’;” >> $OUTPUT_FILE
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] Deadlock analysis completed” >> $OUTPUT_FILE
echo “—————————————-” >> $OUTPUT_FILE
EOF

## 1. 自动解决
– YashanDB会自动检测死锁并选择一个事务回滚
– 被回滚的事务会收到死锁错误，需要重新执行

## 2. 手动解决
– 识别死锁的源头：查看死锁日志和相关会话
– 终止阻塞会话：使用pg_terminate_backend终止阻塞的会话
– 优化应用代码：修改应用代码，避免死锁的发生

## 3. 死锁解决步骤

### 3.1 识别死锁
– 查看死锁日志：tail -n 100 /yashanb/app/yasdb/log/yasdb.log
– 查看相关会话：SELECT * FROM pg_stat_activity WHERE pid IN (blocked_pid, blocking_pid);

### 3.2 终止阻塞会话
$ /yashanb/app/yasdb/bin/psql -U yasdb -d fgedudb -c “SELECT pg_terminate_backend(12346);”

### 3.3 分析死锁原因
– 分析死锁相关的SQL语句
– 找出死锁的根本原因
– 制定解决方案

### 3.4 优化应用代码
– 统一锁定顺序
– 减少事务长度
– 优化SQL语句

### 3.5 验证解决方案
– 测试修改后的代码
– 监控死锁是否不再发生

## 1. 日志监控
– 监控数据库日志中的死锁信息
– 使用logwatch或其他日志分析工具
– 配置死锁告警

## 2. 系统视图监控
– 定期查询pg_locks视图，查看锁等待情况
– 定期查询pg_stat_activity视图，查看会话状态
– 配置监控脚本，定期检查死锁情况

## 3. 第三方监控工具
– 使用Zabbix监控死锁发生的频率
– 使用Grafana展示死锁趋势
– 配置死锁告警通知

## 4. 死锁监控脚本
$ cat > /usr/local/bin/deadlock_monitor.sh << 'EOF' #!/bin/bash # deadlock_monitor.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: `http://www.fgedu.net.cn` # 数据库连接信息 HOST="localhost" PORT="5432" DATABASE="fgedudb" USER="fgedu" PASSWORD="fgedu123" # 输出文件 OUTPUT_FILE="/var/log/yashanb/deadlock_monitor.log" # 确保输出目录存在 mkdir -p /var/log/yashanb # 记录时间 echo "[$(date '+%Y-%m-%d %H:%M:%S')] Deadlock monitoring started" >> $OUTPUT_FILE

# 检查死锁日志
DEADLOCK_COUNT=$(grep “deadlock detected” /yashanb/app/yasdb/log/yasdb.log | wc -l)
echo “Deadlock count in logs: $DEADLOCK_COUNT” >> $OUTPUT_FILE

# 检查锁等待情况
LOCK_WAIT_COUNT=$(psql -h $HOST -p $PORT -U $USER -d $DATABASE -c “SELECT count(*) FROM pg_locks WHERE NOT granted;” -t | tr -d ‘ ‘)
echo “Lock wait count: $LOCK_WAIT_COUNT” >> $OUTPUT_FILE

# 检查长时间运行的事务
LONG_TRANSACTION_COUNT=$(psql -h $HOST -p $PORT -U $USER -d $DATABASE -c “SELECT count(*) FROM pg_stat_activity WHERE state = ‘idle in transaction’ AND now() – xact_start > interval ‘5 minutes’;” -t | tr -d ‘ ‘)
echo “Long transaction count: $LONG_TRANSACTION_COUNT” >> $OUTPUT_FILE

# 发送告警
if [ $DEADLOCK_COUNT -gt 0 ] || [ $LOCK_WAIT_COUNT -gt 5 ] || [ $LONG_TRANSACTION_COUNT -gt 5 ]; then
echo “Deadlock or lock wait detected!” | mail -s “YashanDB Deadlock Alert” admin@example.com
fi
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] Deadlock monitoring completed” >> $OUTPUT_FILE
echo “—————————————-” >> $OUTPUT_FILE
EOF

## 5. 设置定时任务
$ crontab -e
# 添加以下定时任务
*/5 * * * * /usr/local/bin/deadlock_monitor.sh