-- 按事務(wù)開始時間排序，查看運(yùn)行時間最長的事務(wù)
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMEDIFF(NOW(), trx_started) as running_time,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 查找正在等待鎖的事務(wù)
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'LOCK WAIT';

-- 查找運(yùn)行超過30秒的事務(wù)
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'RUNNING' 
  AND trx_started < NOW() - INTERVAL 30 SECOND;

-- 更精確地查找誰阻塞了誰
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id, 
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- 典型的長事務(wù)模式
BEGIN;
-- 執(zhí)行復(fù)雜的業(yè)務(wù)邏輯
UPDATE large_table SET ... WHERE ...; -- 耗時操作
-- 中間可能包含業(yè)務(wù)邏輯、外部API調(diào)用等
COMMIT; -- 很久之后才提交

// 代碼中忘記提交或回滾
@Transactional
public void processData() {
    // 執(zhí)行更新操作
    updateTableA(...);
    // 如果這里發(fā)生異常，事務(wù)可能一直掛起
    if (someCondition) {
        return; // 忘記提交或回滾
    }
    // ... 其他操作
}

-- 沒有索引的更新操作
UPDATE tb_contract_quantities_process 
SET del_flag = 1 
WHERE contract_name LIKE '%某合同%';  -- 全表掃描，鎖住大量行

-- 有索引的高效更新
UPDATE tb_contract_quantities_process 
SET del_flag = 1 
WHERE id IN (1, 2, 3);  -- 使用主鍵索引，只鎖特定行

-- 導(dǎo)致鎖表的操作
UPDATE table_a SET status = 1 WHERE unindexed_column = 'value';
DELETE FROM large_table WHERE create_time < '2023-01-01';

-- 事務(wù)A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事務(wù)B (同時執(zhí)行)
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

// 在事務(wù)中處理大量數(shù)據(jù)
@Transactional
public void batchProcessContracts(List<Long> contractIds) {
    for (Long id : contractIds) {  // 循環(huán)處理，事務(wù)時間很長
        updateContractStatus(id);
        insertProcessLog(id);
        // ... 其他操作
    }
}

@Transactional
public void mainProcess() {
    // 主事務(wù)開始
    updateMainTable();
    
    // 調(diào)用另一個事務(wù)方法
    subProcess();  // 如果subProcess有@Transactional(propagation=REQUIRES_NEW)
    
    // 主事務(wù)繼續(xù)...
}

@Transactional
public void approveContract(Long contractId) {
    // 開始事務(wù)
    updateContractStatus(contractId, "APPROVING");
    // 等待用戶確認(rèn)（事務(wù)一直打開?。?
    waitForUserConfirmation();  // 錯誤做法！
    updateContractStatus(contractId, "APPROVED");
}

-- 事務(wù)A
UPDATE table_a SET status = (SELECT count FROM table_b WHERE ...);
-- 事務(wù)B  
UPDATE table_b SET count = (SELECT status FROM table_a WHERE ...);

# 連接池配置問題
spring.datasource.hikari.maximum-pool-size=200  # 連接數(shù)過多，競爭激烈
spring.datasource.hikari.connection-timeout=30000  # 連接超時時間過長

-- 查看當(dāng)前執(zhí)行的所有SQL
SHOW PROCESSLIST;

-- 查看詳細(xì)的鎖信息
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_query blocking_query,
    b.trx_started blocking_started,
    TIMEDIFF(NOW(), b.trx_started) blocking_duration
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

// 正確的做法：短事務(wù)
public void processInShortTransactions(List<Long> ids) {
    for (Long id : ids) {
        transactionTemplate.execute(status -> {
            // 每個ID在獨(dú)立事務(wù)中處理
            updateContract(id);
            insertLog(id);
            return null;
        });
    }
}

-- 為查詢和更新條件添加索引
CREATE INDEX idx_contract_process ON tb_contract_quantities_process(id);
CREATE INDEX idx_process_details ON tb_contract_quantities_process_details(contract_process_id);
CREATE INDEX idx_arrival_file ON tb_contract_arrival_file(contract_process_id);

// 使用版本號避免悲觀鎖
@Transactional
public boolean updateWithOptimisticLock(Long id, String newValue) {
    Entity entity = entityDao.findById(id);
    int version = entity.getVersion();
    
    int affected = entityDao.updateWithVersion(id, newValue, version, version + 1);
    return affected > 0;  // 如果失敗可以重試
}