16. 事务机制

-- 如果要支持事务机制必须是innodb 
-- myisam不支持

DROP TABLE account;
CREATE TABLE account(
account_no INT AUTO_INCREMENT PRIMARY KEY,
account_name VARCHAR(10) NOT NULL,
account_money INT UNSIGNED
);

SHOW CREATE TABLE account;

INSERT INTO account VALUES(NULL,'A账户',1000);
INSERT INTO account VALUES(NULL,'B账户',1000);

SELECT * FROM account;

-- 1. 没有采用事务机制
DELIMITER $$
CREATE PROCEDURE tranfer_money_proc(IN from_account INT,IN to_account INT,IN money INT) 
MODIFIES SQL DATA
BEGIN
UPDATE account SET account_money = account_money + money WHERE account_no = to_account;
UPDATE account SET account_money = account_money - money WHERE account_no = from_account;
END
$$
DELIMITER ;

CALL tranfer_money_proc(1,2,2000);
SELECT * FROM account;

CALL tranfer_money_proc(1,2,2000);
SELECT * FROM account;

-- 关闭自动提交
-- 方法1:
SET autocommit = 0;
-- 方法2:
START TRANSACTION;

UPDATE account SET account_money = account_money + 800 WHERE account_no = 1;
SELECT * FROM account;
COMMIT;

-- 错误码: 1690
-- BIGINT UNSIGNED value is out of range in '(`choose`.`account`.`account_money` - money@2)'


DELIMITER $$
CREATE PROCEDURE tranfer_money2_proc(IN from_account INT,IN to_account INT,IN money INT) 
MODIFIES SQL DATA
BEGIN
DECLARE CONTINUE HANDLER FOR 1690
BEGIN
ROLLBACK; -- 回滚
END;

START TRANSACTION;
UPDATE account SET account_money = account_money + money WHERE account_no = to_account;
UPDATE account SET account_money = account_money - money WHERE account_no = from_account;
COMMIT;
END
$$
DELIMITER ;

CALL tranfer_money2_proc(1,2,200);
SELECT * FROM account;


-- 保存点
DROP PROCEDURE save_point_proc;
DELIMITER $$
CREATE PROCEDURE save_point_proc() 
MODIFIES SQL DATA
BEGIN
DECLARE CONTINUE HANDLER FOR 1062
BEGIN
ROLLBACK TO pointB;
COMMIT;

-- ROLLBACK; -- 回滚
END;

START TRANSACTION;
INSERT INTO account VALUES(NULL,'D账号',1000);
SAVEPOINT pointB;
INSERT INTO account VALUES(1,'E账号',1000);
COMMIT;
END
$$
DELIMITER ;

CALL save_point_proc();
SELECT * FROM account;