某電商平臺，某天線上使用者報bug說賬戶餘額資訊與交易流水對不上。可以認為是資料庫併發更新問題，由此定位出具體原因，並給出解決方案。
問題現象
場景描述
線上賬務系統，在定時結算給賣家錢時，且高併發量的情況下，出現提現x元（假設當前使用者餘額為x元）餘額為0後，再轉入該賬戶一筆錢（假設為y元），結果賬戶餘額變為了x+y 元，導致使用者餘額錯誤。 ps：賬戶餘額的變更都是在事務中update的
環境說明
mysql5.7 + innodb，事務隔離級別是REPEATABLE-READ
場景模擬
我們簡化下線上的資料結構，進行場景模擬。 資料表如下： ‘賬戶主表’
|
1 2 3 4 5 |
CREATE TABLE user ( uid int(11) NOT NULL COMMENT '型別id+自增序列', name varchar(32) DEFAULT NULL, PRIMARY KEY (uid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='賬戶主表' |
‘賬戶餘額明細表’
|
1 2 3 4 5 |
CREATE TABLE user_account ( uid int(11) NOT NULL, amount decimal(19,4) DEFAULT 0 COMMENT '賬戶餘額', PRIMARY KEY (uid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='賬戶餘額明細表' |
賬戶型別配置
|
1 2 |
CREATE TABLE user_conf ( type_id int(11) NOT NULL, description varchar(32) DEFAULT NULL COMMENT '型別描述', PRIMARY KEY (type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='賬戶型別配置' |
具體資料為：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select * from user; +-------+------+ | uid | name | +-------+------+ | 10001 | a | | 10002 | b | select * from user_account; +-------+----------+ | uid | amount | +-------+----------+ | 10001 | 10.0000 | | 10002 | 108.9900 | select * from user_conf; +---------+--------------+ | type_id | description | +---------+--------------+ | 100 | 外部賬戶 | | 200 | 內部賬戶 | |
模擬提現（即餘額減）和入賬（即餘額加）併發操作的事務如下：
| session1-提現10元 | session2-入賬20元 |
|---|---|
| begin； | |
| select description from user_conf where type_id = 100; | |
| select * from user where uid = 10001 for update; // user表用來做互斥 | |
| select amount from user_account where uid = 10001; // 10.00 | |
| begin; | |
| select description from user_conf where type_id = 100; | |
| select * from user where uid = 10001 for update; // wating | |
| //wating | |
| update user_account set amount = 0.00 where uid = 10001; | |
| commit; | |
| 拿到鎖 | |
| select amount from user_account where uid = 10001; //10.00 | |
| 入賬20元，程式碼中計算後應該為30元 | |
| update user_account set amount = 30.00 where uid = 10001; | |
| commit; |
問題出現了，後面再查詢該使用者餘額為30元，即使用者提現的10元未反映在餘額中
原因定位
熟悉mysql的同學或許已經知道問題是由REPEATABLE-READ隔離級別下快照讀導致。
具體解釋：
RR級別下，第一次讀操作會生成快照，對於可見性來說，只有當第一次讀之前其他事務提交的修改和自己的修改可見，其他的均不可見。
官網文件：https://dev.mysql.com/doc/refman/5.7/en/glossary.html snapshot A representation of data at a particular time, which remains the same even as changes are committed by other transactions.
With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed.
可見性原理
可參考文章：http://hedengcheng.com/?p=148
回到上述模擬場景中，session2在sql語句select description from user_conf where type_id = 100; 時已生成快照，雖然session1提交了，但仍然不可見，導致併發更新問題。
另外，開啟事務後，SELECT … FOR UPDATE 是不會生成快照的，大家可自行實驗
解決方案
方案一
將REPEATABLE-READ隔離級別改為READ-COMMITTED，這樣即能看到最新提交的資料。
方案二
在讀’賬戶餘額明細表’user_account 的時候加 for update，這樣會 1.強制讀該行記錄的最新版本資料，2.且若其他事務未commit，本事務將阻塞，保證序列更新
方案三
延時生成快照。開啟事務後，首先就通過user表做互斥，直接for update加鎖，針對多個事務併發更新即變為序列。
附：定位過程
- 針對上報bug使用者，查詢其交易流水明細與餘額變更明細，確認賬務存在問題
- 查詢賬務系統近幾天是否有上線變更，檢查無
- 拉取賬務資料庫mysql general log，找到併發更新的兩個事務session
- 查詢資料庫設定的隔離級別為RR，查詢應用資料庫連線池配置即session的隔離級別未配置，採用資料庫配置
- 確認由RR級別導致（當然也可以認為是程式碼問題導致）
- 確認是一個月前賬務系統分庫分表上線，改用其他連線池且未設定session隔離級別。而之前是有配置session的隔離級別為READ-COMMITTED。
延伸思考
mysql RR級別適用的業務場景是什麼，應該怎麼選擇？ 有興趣或有見解的同學可以留言回覆或私信~~
參考
http://blog.csdn.net/chen77716/article/details/6742128#comments