事務隔離模式

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 ;