Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

8.2. 事務處理和鎖定語句

Transactional and Locking Statements

開始事務 begin、start transaction 或者 set autocommit=0

事務的特徵:原子性(Atomiocity)、一致性(Consistency)、隔離性(Isolation)和持久性(Durability),這四個特性簡稱ACID特性。
    原子性:事務是資料庫的邏輯工作單位,事務中包括的所有操作要麼都做,要麼都不做。
    一致性:事務執行的結果必須是使資料庫從一個一致性的狀態變到另外一個一致性狀態。
    隔離性:一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的數據對其他事務是隔離的,並發執行的各個事務之間互相不干擾。
    持久性:一個事務一旦成功提交,對資料庫中數據的修改就是持久性的。接下來其他的其他操作或故障不應該對其執行結果有任何影響。
	

8.2.1. 事務隔離級別

事務隔離模式

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;
		

8.2.2. 事務所用到的表

information_schema

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

8.2.3. 解決更新衝突

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表,我來模擬一個返點場景,例如電商網站經常會用到“返點”,購買一定數量的商品贈送一定的點數,可以通過點數買東西,這樣涉及到點的加于減操作。

表 8.1. 更新丟失演示

Session ASession B
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

表 8.2. 防止更新丟失加鎖演示

Session ASession B
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';
		

8.2.4. SAVEPOINT

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 ;