#!/bin/bash
# 盈尺（Surinch）MySQL Lock wait timeout 1205 阻塞链复现 v1.0
# 只能在测试实例上跑：会 DROP/CREATE ${DB}，改会话级 innodb_lock_wait_timeout。
# 测试：MySQL 8.0.46；自造 accounts 1 万行 + orders 5 万行，非客户数据。
# 用法：BIN=/opt/mysql/bin DB=lockwait_lab WORK=/tmp/lw ./mysql-lock-wait-1205-lab-v1.0.sh | tee result.txt
set -uo pipefail
BIN=${BIN:-mysql}
CNF=${CNF:-}
DB=${DB:-lockwait_lab}
WORK=${WORK:-/tmp/lockwait_lab_work}
case "$DB" in *_lab) ;; *) echo "DB 名必须以 _lab 结尾"; exit 1;; esac
if [[ -n "$CNF" ]]; then M="$BIN --defaults-file=$CNF"; else M="$BIN -uroot"; fi
# 若 BIN 是目录前缀
if [[ -x "${BIN}/mysql" ]]; then
  if [[ -n "$CNF" ]]; then M="$BIN/mysql --defaults-file=$CNF"; else M="$BIN/mysql -uroot"; fi
fi
mkdir -p "$WORK"
ts(){ date '+%F %T.%3N'; }
now(){ date +%s.%N; }
ms(){ awk -v a="$1" -v b="$2" 'BEGIN{printf "%.0f", (b-a)*1000}'; }

echo "[$(ts)] VERSION"
$M -N -e "SELECT @@version; SELECT @@innodb_lock_wait_timeout; SELECT @@transaction_isolation;"

echo "[$(ts)] 建库造数"
$M <<SQL
DROP DATABASE IF EXISTS $DB;
CREATE DATABASE $DB CHARACTER SET utf8mb4;
USE $DB;
CREATE TABLE accounts(
  id INT PRIMARY KEY, balance INT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE orders(
  id BIGINT PRIMARY KEY AUTO_INCREMENT, account_id INT NOT NULL, amount INT NOT NULL, status TINYINT NOT NULL,
  KEY idx_account(account_id)
) ENGINE=InnoDB;
INSERT INTO accounts(id, balance)
SELECT n, 1000 + MOD(n, 100) FROM (
  SELECT a.d + b.d*10 + c.d*100 + e.d*1000 AS n
  FROM (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e
) t WHERE n BETWEEN 1 AND 10000;
INSERT INTO orders(account_id, amount, status)
SELECT 1+MOD(n,10000), 10+MOD(n,90), 1 FROM (
  SELECT a.d + b.d*10 + c.d*100 + e.d*1000 + f.d*10000 AS n
  FROM (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e,
       (SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) f
) t WHERE n BETWEEN 1 AND 50000;
ANALYZE TABLE accounts, orders;
SELECT COUNT(*) AS accounts FROM accounts; SELECT COUNT(*) AS orders FROM orders;
SQL

run_timeout(){
  local tag=$1 timeout=$2 hold=$3
  echo "[$(ts)] === $tag timeout=$timeout hold=${hold}s ==="
  $M -e "UPDATE $DB.accounts SET balance=1100 WHERE id=1;"
  cat > "$WORK/${tag}_A.sql" <<SQA
USE $DB; BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=1; SELECT SLEEP($hold); COMMIT;
SQA
  cat > "$WORK/${tag}_B.sql" <<SQB
USE $DB;
SET SESSION innodb_lock_wait_timeout=$timeout;
SELECT SLEEP(1);
SELECT NOW(3) AS b_start;
BEGIN;
UPDATE accounts SET balance=balance+5 WHERE id=1;
SELECT NOW(3) AS b_end, balance FROM accounts WHERE id=1;
COMMIT;
SQB
  cat > "$WORK/${tag}_C.sql" <<SQC
USE $DB; SELECT SLEEP(2);
SELECT r.ENGINE_TRANSACTION_ID waiting_trx, b.ENGINE_TRANSACTION_ID blocking_trx,
       r.LOCK_MODE req, b.LOCK_MODE blk, r.LOCK_STATUS, b.LOCK_STATUS, r.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID;
SELECT waiting_trx_id, waiting_pid, LEFT(waiting_query,80) wq, blocking_trx_id, blocking_pid, LEFT(blocking_query,80) bq
FROM sys.innodb_lock_waits;
SELECT trx_id, trx_state, TIMESTAMPDIFF(SECOND,trx_started,NOW()) age_s, trx_rows_locked, LEFT(trx_query,60) q
FROM information_schema.innodb_trx;
SQC
  local t0=$(now)
  $M --table --force < "$WORK/${tag}_A.sql" > "$WORK/${tag}_A.out" 2>&1 &
  $M --table --force < "$WORK/${tag}_B.sql" > "$WORK/${tag}_B.out" 2>&1 &
  $M --table --force < "$WORK/${tag}_C.sql" > "$WORK/${tag}_C.out" 2>&1 &
  wait
  echo "RESULT|$tag|total_ms=$(ms $t0 $(now))|final=$($M -N -e "SELECT balance FROM $DB.accounts WHERE id=1;")"
  echo "--- B ---"; cat "$WORK/${tag}_B.out"
  echo "--- SNAP ---"; cat "$WORK/${tag}_C.out"
}

run_timeout T5 5 12
run_timeout T2 2 8
run_timeout T50ok 50 3

echo "[$(ts)] === B_STMT：1205 只回滚语句（客户端必须 --force）==="
$M -e "UPDATE $DB.accounts SET balance=1100 WHERE id=1; DELETE FROM $DB.orders WHERE account_id=1 AND amount=999;"
cat > "$WORK/BS_A.sql" <<SQ
USE $DB; BEGIN; UPDATE accounts SET balance=balance-1 WHERE id=1; SELECT SLEEP(8); COMMIT;
SQ
cat > "$WORK/BS_B.sql" <<SQ
USE $DB;
SET SESSION innodb_lock_wait_timeout=3;
SELECT SLEEP(1); BEGIN;
INSERT INTO orders(account_id,amount,status) VALUES (1,999,1);
SELECT 'B_INSERTED' mark, COUNT(*) cnt FROM orders WHERE account_id=1 AND amount=999;
UPDATE accounts SET balance=balance+1 WHERE id=1;
SELECT 'B_AFTER_1205' mark, COUNT(*) cnt FROM orders WHERE account_id=1 AND amount=999;
SELECT trx_id, trx_state, trx_rows_modified FROM information_schema.innodb_trx WHERE trx_mysql_thread_id=CONNECTION_ID();
ROLLBACK;
SELECT 'B_AFTER_ROLLBACK' mark, COUNT(*) cnt FROM orders WHERE account_id=1 AND amount=999;
SQ
$M --table --force < "$WORK/BS_A.sql" > "$WORK/BS_A.out" 2>&1 &
$M --table --force < "$WORK/BS_B.sql" > "$WORK/BS_B.out" 2>&1 &
wait
echo "--- B_STMT B ---"; cat "$WORK/BS_B.out"

echo "[$(ts)] === E_CHAIN：三层阻塞 ==="
$M -e "UPDATE $DB.accounts SET balance=1100 WHERE id=1;"
cat > "$WORK/E_A.sql" <<SQ
USE $DB; BEGIN; UPDATE accounts SET balance=balance-10 WHERE id=1; SELECT SLEEP(10); COMMIT;
SQ
cat > "$WORK/E_B.sql" <<SQ
USE $DB; SET SESSION innodb_lock_wait_timeout=15; SELECT SLEEP(1); BEGIN;
UPDATE accounts SET balance=balance+5 WHERE id=1; COMMIT;
SQ
cat > "$WORK/E_C.sql" <<SQ
USE $DB; SET SESSION innodb_lock_wait_timeout=15; SELECT SLEEP(2); BEGIN;
UPDATE accounts SET balance=balance+1 WHERE id=1; COMMIT;
SQ
cat > "$WORK/E_S.sql" <<SQ
USE $DB; SELECT SLEEP(4);
SELECT r.ENGINE_TRANSACTION_ID waiting_trx, b.ENGINE_TRANSACTION_ID blocking_trx,
       r.LOCK_MODE, b.LOCK_MODE, r.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID;
SELECT trx_id, trx_state, TIMESTAMPDIFF(SECOND,trx_started,NOW()) age_s, LEFT(trx_query,60) q
FROM information_schema.innodb_trx;
SQ
$M --table --force < "$WORK/E_A.sql" >/dev/null 2>&1 &
$M --table --force < "$WORK/E_B.sql" >/dev/null 2>&1 &
$M --table --force < "$WORK/E_C.sql" >/dev/null 2>&1 &
$M --table --force < "$WORK/E_S.sql" > "$WORK/E_S.out" 2>&1 &
wait
cat "$WORK/E_S.out"
echo "[$(ts)] DONE"