在 MySQL 中,事务(Transaction)是一组要么全部执行,要么全部不执行的SQL语句。这可以确保数据的一致性和完整性。事务管理的核心包括四个属性,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability),通常简称为ACID属性。
示例:使用事务管理
假设我们有两个账户表 accounts
,每个账户有一个唯一的ID和一个余额。我们将展示如何在两个账户之间进行资金转移,并确保这个操作是原子性的。
创建示例表并插入数据
CREATE TABLE accounts (account_id INT PRIMARY KEY,balance DECIMAL(10, 2)
);INSERT INTO accounts (account_id, balance) VALUES
(1, 1000.00),
(2, 2000.00);
使用事务进行资金转移
下面是一个示例,展示了如何在 MySQL 中使用事务进行账户之间的资金转移。
START TRANSACTION;-- 从账户1中扣款
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;-- 向账户2中存款
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;-- 检查账户1的余额是否不足以扣款
SELECT balance INTO @balance FROM accounts WHERE account_id = 1;
IF @balance < 0 THEN-- 如果余额不足,回滚事务ROLLBACK;SELECT 'Transaction failed: insufficient funds' AS message;
ELSE-- 如果余额充足,提交事务COMMIT;SELECT 'Transaction succeeded' AS message;
END IF;
解释
- START TRANSACTION:开始一个事务。
- UPDATE 语句:执行两次更新操作,分别从账户1中扣款并向账户2中存款。
- SELECT INTO 语句:检查账户1的余额是否不足以扣款。
- IF 语句:如果账户1的余额不足,回滚事务;否则,提交事务。
- ROLLBACK:如果余额不足,则回滚事务,取消所有先前执行的操作。
- COMMIT:如果余额充足,则提交事务,保存所有更改。
详细步骤
-
开始事务:
START TRANSACTION;
开始一个新的事务块。
-
执行更新操作:
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-
检查余额:
SELECT balance INTO @balance FROM accounts WHERE account_id = 1; IF @balance < 0 THEN-- 如果余额不足,回滚事务ROLLBACK;SELECT 'Transaction failed: insufficient funds' AS message; ELSE-- 如果余额充足,提交事务COMMIT;SELECT 'Transaction succeeded' AS message; END IF;
使用存储过程实现事务
将上述操作封装在存储过程中,更加清晰和模块化:
DELIMITER $$CREATE PROCEDURE transfer_funds(IN p_from_account_id INT,IN p_to_account_id INT,IN p_amount DECIMAL(10, 2)
)
BEGINDECLARE balance DECIMAL(10, 2);START TRANSACTION;-- 从源账户扣款UPDATE accountsSET balance = balance - p_amountWHERE account_id = p_from_account_id;-- 向目标账户存款UPDATE accountsSET balance = balance + p_amountWHERE account_id = p_to_account_id;-- 检查源账户余额是否充足SELECT balance INTO balance FROM accounts WHERE account_id = p_from_account_id;IF balance < 0 THEN-- 如果余额不足,回滚事务ROLLBACK;SELECT 'Transaction failed: insufficient funds' AS message;ELSE-- 如果余额充足,提交事务COMMIT;SELECT 'Transaction succeeded' AS message;END IF;
END$$DELIMITER ;
调用存储过程:
CALL transfer_funds(1, 2, 100.00);
总结
- 开始事务:使用
START TRANSACTION
。 - 提交事务:使用
COMMIT
。 - 回滚事务:使用
ROLLBACK
。
通过这些步骤,可以在 MySQL 中管理事务,确保数据一致性和完整性。