START TRANSACTION; -- 或 BEGIN
-- SQL 操作
COMMIT; -- 全部成功提交
ROLLBACK; -- 任一失敗回滾
前提:表引擎必須為 InnoDB(或 BDB),MyISAM 不支援事務。
INSERT 遞增,DELETE/ROLLBACK 不遞減SELECT MAX(id) FOR UPDATE 重新載入這是 InnoDB 設計權衡:為高併發插入性能犧牲連續性。業務若依賴連續 ID,請改用號段模式或單獨序列表。
| 類型 | 用途 | 存儲位置 | 特點 |
|---|---|---|---|
| Redo Log | 崩潰恢復(WAL) | ib_logfile* | 順序寫、崩潰後重放 |
| Undo Log | 事務回滾/MVCC | 共享表空間/Undo Tablespace | 邏輯反操作、空間不自動回收、需 Purge 線程清理 |
CREATE PROCEDURE sp_transfer()
BEGIN
DECLARE t_error INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION;
INSERT INTO `mytest`.`test2` VALUES (1, '22', '33');
INSERT INTO `mytest`.`test2` VALUES (NULL, '22', '33'); -- 故意報錯
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END
function create_order() {
$db->query("START TRANSACTION");
$res = true;
// 1. 生成唯一訂單號
$order_sn = create_sn();
$user_id = $_SESSION['user']['user_id'];
$add_time = time();
$goods_amount = $cart->getPrice();
$sql = "INSERT INTO order_info (order_sn,user_id,goods_amount,add_time)
VALUES ('$order_sn','$user_id','$goods_amount','$add_time')";
$res = $res && $db->query($sql);
$order_id = $db->insert_id();
// 2. 批量寫入訂單商品 & 扣減庫存
$cartlist = $cart->enumItems();
foreach ($cartlist as $k => $v) {
$goods = $db->getRow("SELECT goods_id,goods_sn,goods_name,shop_price FROM goods WHERE goods_id='$k'");
$cartlist[$k] = array_merge($v, $goods);
}
$sqls = [];
foreach ($cartlist as $k => $v) {
$sqls[] = "INSERT INTO order_goods (order_id,goods_id,goods_name,goods_sn,goods_number,goods_price)
VALUES ('$order_id','$k','{$v['goods_name']}','{$v['goods_sn']}','{$v['num']}','{$v['shop_price'] * $v['num']}')";
$sqls[] = "UPDATE goods SET goods_number = goods_number - '{$v['num']}' WHERE goods_id = '$k'";
}
foreach ($sqls as $sql) {
$res = $res && $db->query($sql);
}
// 3. 統一提交/回滾
if (!$res) {
$db->query('ROLLBACK');
show_message('訂單創建失敗', '返回購物車', 'flow.php?act=cartinfo');
exit();
} else {
$db->query('COMMIT');
$cart->emptyItems();
// 顯示成功頁...
}
}
SHOW CREATE TABLE goods\G 確認 ENGINE=InnoDBPDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION + try/catchSQLSTATE[HY000]: 1213 重試 3 次,指數退避