MySQL半一致性讀導致語句級Binlog複製錯誤

壹頁書發表於2014-02-15
原文網址 : http://blog.itpub.net/29254281/viewspace-1081678/
MySql
MySQL事務的隔離級別為read committed(或以下)，或者設定了innodb_locks_unsafe_for_binlog引數會啟用半一致性讀特性。

半一致性讀參考:
http://hedengcheng.com/?p=220

MySQL官方文件refman-5.6-en.a4.pdf 1833頁 關於innodb_locks_unsafe_for_binlog引數

對於半一致性讀，我感覺一個是違反兩階段鎖，將不符合條件記錄的行級鎖提前釋放。另一個是Update的行如果被鎖定，則返回一個最近提交的版本。

官方文件原文如下：
Enabling innodb_locks_unsafe_for_binlog has additional effects:
? For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes.
Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.
This greatly reduces the probability of deadlocks, but they can still happen.

? For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read,
returning the latest committed version to MySQL so that MySQL can determine whether the row
matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads
the row again and this time InnoDB either locks it or waits for a lock on it.

半一致性讀本身是為了增加併發，但是對於STATEMENT格式的binlog則是致命的錯誤。
實驗環境如下:


初始化資料
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    3 |
| 3 |    2 |
| 4 |    3 |
| 5 |    2 |
+---+------+
5 rows in set (0.00 sec)

開啟一個終端A,將b=3的記錄修改為10
然後開啟另外一個終端B,將b=2的記錄修改為3並且提交,
最後提交終端A的事務。

檢視此時的結果
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    3 |
| 2 |   10 |
| 3 |    3 |
| 4 |   10 |
| 5 |    3 |
+---+------+
5 rows in set (0.00 sec)

這個結果沒有任何問題，但是檢視binlog的內容
BEGIN
/*!*/;
# at 2802
#140215  0:07:52 server id 1  end_log_pos 2906 CRC32 0x264b3682     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1392394072/*!*/;
update t set b=3 where b=2
/*!*/;
# at 2906
#140215  0:07:54 server id 1  end_log_pos 2937 CRC32 0x59a3d24d     Xid = 63
COMMIT/*!*/;
# at 2937
#140215  0:06:02 server id 1  end_log_pos 3020 CRC32 0x56a9493b     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1392393962/*!*/;
BEGIN
/*!*/;
# at 3020
#140215  0:06:02 server id 1  end_log_pos 3125 CRC32 0x0d874b5d     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1392393962/*!*/;
update t set b=10 where b=3
/*!*/;
# at 3125
#140215  0:08:01 server id 1  end_log_pos 3156 CRC32 0x8fe0dd5d     Xid = 61
COMMIT/*!*/;

如果使用binlog複製，則備庫執行的語句如下:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

update t set b=3 where b=2;
commit;
update t set b=10 where b=3;
commit;

備庫執行後的資料則是
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   10 |
| 3 |   10 |
| 4 |   10 |
| 5 |   10 |
+---+------+
5 rows in set (0.00 sec)

為了避免這個問題，可以將binlog_format設定為ROW。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1081678/，如需轉載，請註明出處，否則將追究法律責任。

相關文章