测试事务实现转账

测试事务实现转账

-- ====================测试事务实现转账================================
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop
CREATE TABLE `account` (
`id` INT(8) AUTO_INCREMENT NOT NULL,
`name` VARCHAR(20) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `account` (`name`,`money`) VALUES ( 'a', 1000.00),('b',500);
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE `account`  SET `money` = `money`-200 WHERE `name`='a';
UPDATE `account`  SET `money` = `money`+200 WHERE `name`='b';
ROLLBACK;
COMMIT;
SET autocommit = 1; -- 关闭自动提交

语法

SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE `account`  SET `money` = `money`-200 WHERE `name`='a'; -- 步骤一
UPDATE `account`  SET `money` = `money`+200 WHERE `name`='b'; -- 步骤二
ROLLBACK; -- 回滚 以上步骤一和步骤二都会回滚
COMMIT; -- 提交 以上步骤一和步骤二都会提交到数据库并持久化
SET autocommit = 1; -- 关闭自动提交