#!/usr/bin/env bash
# MySQL 死锁复现与读日志演示脚本 v1.0（surinch.com）
# 仅限测试实例！会创建/删除库 deadlock_lab。不要对生产库运行。
# 用法：MYSQL="mysql -uroot -h127.0.0.1" bash mysql-deadlock-demo-v1.0.sh
set -u
MYSQL=${MYSQL:-mysql}
q() { $MYSQL -N -e "$1"; }
now() { date +%s.%N; }
el() { awk -v a="$1" -v b="$2" 'BEGIN{printf "%.3f", b-a}'; }

echo "== 版本 / 隔离级别 / 死锁相关参数"
q "SELECT VERSION(), @@transaction_isolation, @@innodb_deadlock_detect, @@innodb_lock_wait_timeout, @@innodb_print_all_deadlocks"

$MYSQL -e "DROP DATABASE IF EXISTS deadlock_lab; CREATE DATABASE deadlock_lab;
USE deadlock_lab;
CREATE TABLE accounts(id INT PRIMARY KEY, balance DECIMAL(12,2) NOT NULL) ENGINE=InnoDB;
CREATE TABLE orders(id INT AUTO_INCREMENT PRIMARY KEY, uid INT NOT NULL, order_no INT NOT NULL, KEY idx_order_no(order_no)) ENGINE=InnoDB;"
# 合成数据：账户 1000 个；订单 order_no 取偶数（留出空隙用于间隙锁场景）
$MYSQL deadlock_lab -e "INSERT INTO accounts SELECT n, 1000 FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<1000) SELECT n FROM s) t;
SET SESSION cte_max_recursion_depth=100000;
INSERT INTO orders(uid, order_no) SELECT n%1000, n*2 FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) SELECT n FROM s) t;"
q "SELECT 'accounts', COUNT(*) FROM deadlock_lab.accounts UNION ALL SELECT 'orders', COUNT(*) FROM deadlock_lab.orders"

before=$(q "SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME='lock_deadlocks'")

echo; echo "== 场景 1：两个事务按相反顺序更新两行（转账 1→2 与 2→1）"
t0=$(now)
( $MYSQL deadlock_lab -e "BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=1; DO SLEEP(1); UPDATE accounts SET balance=balance+10 WHERE id=2; DO SLEEP(1); COMMIT;" >/tmp/dl_a.out 2>&1; echo "A_exit=$? A_s=$(el $t0 $(now))" >>/tmp/dl_a.out ) &
sleep 0.3
( $MYSQL deadlock_lab -e "BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=2; DO SLEEP(1); UPDATE accounts SET balance=balance+10 WHERE id=1; COMMIT;" >/tmp/dl_b.out 2>&1; echo "B_exit=$? B_s=$(el $t0 $(now))" >>/tmp/dl_b.out ) &
wait
echo "-- 会话 A:"; cat /tmp/dl_a.out; echo "-- 会话 B:"; cat /tmp/dl_b.out
q "SELECT id, balance FROM deadlock_lab.accounts WHERE id IN (1,2)"
echo "-- LATEST DETECTED DEADLOCK（节选）"
$MYSQL -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/^TRANSACTIONS$/p' | grep -E "TRANSACTION|ACTIVE|LOCK WAIT|UPDATE|HOLDS|WAITING|lock_mode|index PRIMARY|WE ROLL BACK" | head -30

echo; echo "== 场景 2：两个事务 SELECT ... FOR UPDATE 同一个不存在的值，再各自 INSERT（间隙锁 + 插入意向锁）"
t0=$(now)
( $MYSQL deadlock_lab -e "BEGIN; SELECT id FROM orders WHERE order_no=50001 FOR UPDATE; DO SLEEP(1); INSERT INTO orders(uid,order_no) VALUES(7,50001); DO SLEEP(1); COMMIT;" >/tmp/dl_c.out 2>&1; echo "C_exit=$? C_s=$(el $t0 $(now))" >>/tmp/dl_c.out ) &
sleep 0.3
( $MYSQL deadlock_lab -e "BEGIN; SELECT id FROM orders WHERE order_no=50001 FOR UPDATE; DO SLEEP(1); INSERT INTO orders(uid,order_no) VALUES(8,50001); COMMIT;" >/tmp/dl_d.out 2>&1; echo "D_exit=$? D_s=$(el $t0 $(now))" >>/tmp/dl_d.out ) &
wait
echo "-- 会话 C:"; cat /tmp/dl_c.out; echo "-- 会话 D:"; cat /tmp/dl_d.out
q "SELECT COUNT(*) FROM deadlock_lab.orders WHERE order_no=50001"
echo "-- LATEST DETECTED DEADLOCK（节选）"
$MYSQL -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/^TRANSACTIONS$/p' | grep -E "TRANSACTION|LOCK WAIT|SELECT|INSERT|HOLDS|WAITING|lock_mode|index idx|WE ROLL BACK" | head -30

echo; echo "== 场景 3：关闭死锁检测（innodb_deadlock_detect=OFF, lock_wait_timeout=5）重放场景 1"
q "SET GLOBAL innodb_deadlock_detect=OFF; SET GLOBAL innodb_lock_wait_timeout=5;"
t0=$(now)
( $MYSQL deadlock_lab -e "BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=3; DO SLEEP(1); UPDATE accounts SET balance=balance+10 WHERE id=4; COMMIT;" >/tmp/dl_e.out 2>&1; echo "E_exit=$? E_s=$(el $t0 $(now))" >>/tmp/dl_e.out ) &
sleep 0.3
( $MYSQL deadlock_lab -e "BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=4; DO SLEEP(1); UPDATE accounts SET balance=balance+10 WHERE id=3; COMMIT;" >/tmp/dl_f.out 2>&1; echo "F_exit=$? F_s=$(el $t0 $(now))" >>/tmp/dl_f.out ) &
wait
q "SET GLOBAL innodb_deadlock_detect=ON; SET GLOBAL innodb_lock_wait_timeout=50;"
echo "-- 会话 E:"; cat /tmp/dl_e.out; echo "-- 会话 F:"; cat /tmp/dl_f.out

echo; echo "== 场景 4：修复——两边都按 id 升序加锁"
t0=$(now)
( $MYSQL deadlock_lab -e "BEGIN; SELECT id FROM accounts WHERE id IN (5,6) ORDER BY id FOR UPDATE; DO SLEEP(1); UPDATE accounts SET balance=balance-10 WHERE id=5; UPDATE accounts SET balance=balance+10 WHERE id=6; COMMIT;" >/tmp/dl_g.out 2>&1; echo "G_exit=$? G_s=$(el $t0 $(now))" >>/tmp/dl_g.out ) &
sleep 0.3
( $MYSQL deadlock_lab -e "BEGIN; SELECT id FROM accounts WHERE id IN (5,6) ORDER BY id FOR UPDATE; DO SLEEP(1); UPDATE accounts SET balance=balance-10 WHERE id=6; UPDATE accounts SET balance=balance+10 WHERE id=5; COMMIT;" >/tmp/dl_h.out 2>&1; echo "H_exit=$? H_s=$(el $t0 $(now))" >>/tmp/dl_h.out ) &
wait
echo "-- 会话 G:"; cat /tmp/dl_g.out; echo "-- 会话 H:"; cat /tmp/dl_h.out
q "SELECT id, balance FROM deadlock_lab.accounts WHERE id IN (5,6)"

echo; echo "== 场景 5：order_no 加唯一索引 + READ COMMITTED，再重放场景 2"
$MYSQL deadlock_lab -e "CREATE TABLE orders_u(id INT AUTO_INCREMENT PRIMARY KEY, uid INT NOT NULL, order_no INT NOT NULL, UNIQUE KEY uk_order_no(order_no)) ENGINE=InnoDB; INSERT INTO orders_u(uid,order_no) SELECT uid, order_no FROM orders WHERE order_no<>50001;"
t0=$(now)
( $MYSQL deadlock_lab -e "SET SESSION transaction_isolation='READ-COMMITTED'; BEGIN; SELECT id FROM orders_u WHERE order_no=50001 FOR UPDATE; DO SLEEP(1); INSERT INTO orders_u(uid,order_no) VALUES(7,50001); DO SLEEP(1); COMMIT;" >/tmp/dl_i.out 2>&1; echo "I_exit=$? I_s=$(el $t0 $(now))" >>/tmp/dl_i.out ) &
sleep 0.3
( $MYSQL deadlock_lab -e "SET SESSION transaction_isolation='READ-COMMITTED'; BEGIN; SELECT id FROM orders_u WHERE order_no=50001 FOR UPDATE; DO SLEEP(1); INSERT INTO orders_u(uid,order_no) VALUES(8,50001); COMMIT;" >/tmp/dl_j.out 2>&1; echo "J_exit=$? J_s=$(el $t0 $(now))" >>/tmp/dl_j.out ) &
wait
echo "-- 会话 I:"; cat /tmp/dl_i.out; echo "-- 会话 J:"; cat /tmp/dl_j.out
q "SELECT COUNT(*) FROM deadlock_lab.orders_u WHERE order_no=50001"

after=$(q "SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME='lock_deadlocks'")
echo; echo "== INNODB_METRICS lock_deadlocks：$before -> $after"
echo "（清理：DROP DATABASE deadlock_lab;）"