8.2. 事务处理和锁定语句

事务隔离模式

1) READ UNCOMMITED
SELECT的时候允许脏读，即SELECT会读取其他事务修改而还没有提交的数据。
2)READ COMMITED
SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致。
3) REPEATABLE READ
SELECT的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的。实现的原理是，在一个事务对数据行执行读取或写入操作时锁定了这些数据行。
但是这种方式又引发了幻想读的问题。因为只能锁定读取或写入的行，不能阻止另一个事务插入数据，后期执行同样的查询会产生更多的结果。
4)SERIALIZABLE
与可重复读的唯一区别是，默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。
serializable模式中，事务被强制为依次执行。这是SQL标准建议的默认行为。

可以通过下列语句查询全局和当前会话的事务隔离级别：

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

查看InnoDB系统级别的事务隔离级别：
mysql> SELECT @@global.tx_isolation;
查看InnoDB会话级别的事务隔离级别：
mysql> SELECT @@tx_isolation;
修改InnoDB系统级别的事务隔离级别：
mysql> set global transaction isolation level read committed;
修改InnoDB会话级别的事务隔离级别：
mysql> set session transaction isolation level read committed;

information_schema

select * from innodb_trx;
select * from innodb_lock_waits;
select * from innodb_locks;

CREATE TABLE `account` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user` VARCHAR(50) NOT NULL DEFAULT '0',
`cash` FLOAT NOT NULL DEFAULT '0',
`point` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `user` (`user`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `test`.`account` (`user`, `cash`,`point`) VALUES ('neo', 10,10);

下面通过account表，我来模拟一个返点场景，例如电商网站经常会用到“返点”，购买一定数量的商品赠送一定的点数，可以通过点数买东西，这样涉及到点的加于减操作。

select point into @point from account where user='neo';

select point into @point from account where user='neo';

update account set point=@point+20 where user='neo';

update account set point=@point+50 where user='neo';

看看最后用户有多少点？

mysql> select point from account where user='neo';
+-------+
| point |
+-------+
|
30 |
+-------+
1 row in set (0.00 sec)

傻了吧，老板发火，测试不能重现，运维说这是程序计算错误，程序员说程序没有错误，这样的场景国内很多公司都出现过吧？

问题出在哪里呢？出在并发上，很多web程序员很少考虑并发是产生的问题，怎么解决？很多方案，在我的职业生涯过程就见过很多奇葩方案，都能解决问题但不太完美。

如果更新语句改为 update account set point=@point+50 where user='neo' and point=@point; 会更保险,但仍然不能解决同意时间所产生的更新操作

下面是通过事务与锁彻底解决上面的问题。

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation
|
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

检查事务隔离级别为：REPEATABLE-READ

begin;
select point into @point from account where user='neo' for update;

begin;
select point into @point from account where user='neo' for update;

update account set point=@point+20 where user='neo';
commit;

update account set point=@point+50 where user='neo';
commit;

上面解决更新覆盖问题，但从数据库设计角度是不应该这样设计表的。仅供参考

CREATE TABLE `account` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user` VARCHAR(50) NOT NULL DEFAULT '0',
`cash` FLOAT NOT NULL DEFAULT '0',
`point` INT(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

每一次数据变化新增一条数据

INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', -10);
INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', -5);
INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', 30);
INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', -20);

计算剩余点数

select sum(point) as point from account where user='neo';

DROP PROCEDURE IF EXISTS doOrder;
DELIMITER $$
CREATE PROCEDURE doOrder(IN orderUUID VARCHAR(40))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_order;
START TRANSACTION;
SAVEPOINT sp_order;
-- doing my updates and selects here...
COMMIT;
END $$
DELIMITER ;