mysql互為主從的環境,更新一條語句同時送出,為什麼會出現資料不一緻?
m1:
begin;
update t1 set c2='b1' where c1=2;
commit;
m2:
update t1 set c2='b2' where c1=2;
m1和m2同時送出,複制不會報錯,但是m1和m2的資料不一緻,為什麼?
因為sql_thread線程根據主鍵更新資料,不會校驗行資料
如何避免這種問題:
隻在單節點進行寫入,如 keepalived+雙主,MGR,PXC如果多節點寫入都有這種問題發生。
例1:
表有主鍵和自增的情況:
root@localhost [testdb]>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` varchar(10) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
m1: | |
root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ccc | | 6 | ddd | | 8 | eee | | |
root@localhost [testdb]>begin; | |
root@localhost [testdb]>update t1 set c2='b1' where c1=2; | root@localhost [testdb]>update t1 set c2='b2' where c1=2; |
| 2 | b1 | | | 2 | b2 | |
root@localhost [testdb]>commit; | |
總結:update一條記錄同時送出,有主鍵的情況下,sql_thread是根據主鍵比對行記錄,不會校驗行資料,是以m1更新了m2中表的記錄,m2更新了m1中表的記錄。
例2:有沒有主鍵同時更新一行資料的情況:
root@localhost [testdb]>show create table t2\G
Table: t2
Create Table: CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
m1 | m2 |
root@localhost [testdb]>select * from t2; +------+------+ | c1 | c2 | | 1 | aaa | | 2 | bbb | | |
root@localhost [testdb]>update t2 set c2='b1' where c1=2; | root@localhost [testdb]>update t2 set c2='b2' where c1=2; |
| 2 | b1 | | | 2 | b2 | |
root@localhost [testdb]>show slave status\G Last_Errno: 1032 Last_Error: Could not execute Update_rows event on table testdb.t2; Can't find record in 't2', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000013, end_log_pos 759 | root@localhost [testdb]>show slave status\G Last_Error: Could not execute Update_rows event on table testdb.t2; Can't find record in 't2', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000026, end_log_pos 3064 |
總結:update一條記錄同時送出,有沒有主鍵的情況下,sql_thread是根據全表掃描比對行記錄,是以m1更新在m2中找不到需要更新的行,報1032錯誤,m2更新在m1中找不到需要更新的行,也報1032錯誤。