DB_IP=”192.168.1.10″
DB_PORT=”5432″
DB_NAME=”fgedudb”
DB_USER=”fgedu”
LOG_FILE=”/opengauss/log/lock_monitor.log”
ALERT_THRESHOLD=300 # 锁等待告警阈值(秒)

log() {
echo “[$(date ‘+%Y-%m-%d %H:%M:%S’)] $1” | tee -a $LOG_FILE
}

# 检查锁等待
check_lock_waits() {
log “=== 检查锁等待情况 ===”

gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c “
SELECT
w.pid AS waiting_pid,
w.usename AS waiting_user,
b.pid AS blocking_pid,
b.usename AS blocking_user,
l.relation::regclass AS table_name,
EXTRACT(EPOCH FROM (now() – w.query_start))::int AS wait_seconds,
w.query AS waiting_query
FROM pg_stat_activity w
JOIN pg_locks l ON w.pid = l.pid
JOIN pg_locks l2 ON l.locktype = l2.locktype
AND l.relation = l2.relation
AND l.pid != l2.pid
JOIN pg_stat_activity b ON l2.pid = b.pid
WHERE w.waiting = true
AND l.granted = false
AND l2.granted = true
ORDER BY wait_seconds DESC;
” | while read line; do
if [ -n “$line” ]; then
log “发现锁等待: $line”

# 提取等待时间
wait_seconds=$(echo $line | awk -F’|’ ‘{print $6}’ | tr -d ‘ ‘)

# 超过阈值则告警
if [ “$wait_seconds” -gt “$ALERT_THRESHOLD” ]; then
blocking_pid=$(echo $line | awk -F’|’ ‘{print $3}’ | tr -d ‘ ‘)
log “告警: 锁等待超过${ALERT_THRESHOLD}秒,阻塞会话PID: $blocking_pid”
fi
fi
done
}

# 检查长时间未提交的事务
check_long_transactions() {
log “=== 检查长时间未提交的事务 ===”

gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c “
SELECT
pid,
usename,
application_name,
EXTRACT(EPOCH FROM (now() – xact_start))::int AS xact_seconds,
query
FROM pg_stat_activity
WHERE state = ‘idle in transaction’
AND now() – xact_start > interval ’10 minutes’
ORDER BY xact_start;
” | while read line; do
if [ -n “$line” ]; then
log “发现长时间未提交事务: $line”
fi
done
}

# 检查死锁
check_deadlocks() {
log “=== 检查死锁统计 ===”

deadlock_count=$(gsql -h $DB_IP -p $DB_PORT -d $DB_NAME -U $DB_USER -t -c “
SELECT deadlocks FROM pg_stat_database WHERE datname = ‘$DB_NAME’;

” | tr -d ‘ ‘)

log “数据库死锁总数: $deadlock_count”
}

# 主函数
main() {
log “开始锁监控检查…”

check_lock_waits
check_long_transactions
check_deadlocks

log “锁监控检查完成”
}

main