SAVEPOINT point1; -- 从转账账户扣钱 UPDATE tb_user_act SET balance = balance - iMoney WHERE userid = iFrom;
-- 如果转账账户扣钱成功 IF ( ROW_COUNT() = 1 ) THEN -- 向转入账户加钱 UPDATE tb_user_act SET balance = balance + iMoney WHERE userid = iTo;
IF ( ROW_COUNT() = 1 ) THEN COMMIT; SET error_code = 0; ELSE ROLLBACK TO SAVEPOINT point1; SET error_code = 9999; END IF; ELSE SET error_code = 9999; END IF;
commit
(from_account int, to_account int, tfer_amount numeric(10,2))
BEGIN
START TRANSACTION;
UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;
UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;
COMMIT;
END; 参考:http://hideto.iteye.com/blog/195275
他会自动回滚吗,不用我写rollback?
他会自动回滚吗,不用我写rollback?
START TRANSACTION;
SAVEPOINT point1;
-- 从转账账户扣钱
UPDATE tb_user_act
SET balance = balance - iMoney
WHERE userid = iFrom;
-- 如果转账账户扣钱成功
IF ( ROW_COUNT() = 1 ) THEN
-- 向转入账户加钱
UPDATE tb_user_act
SET balance = balance + iMoney
WHERE userid = iTo;
IF ( ROW_COUNT() = 1 ) THEN
COMMIT;
SET error_code = 0;
ELSE
ROLLBACK TO SAVEPOINT point1;
SET error_code = 9999;
END IF;
ELSE
SET error_code = 9999;
END IF;
推荐一篇帖子