Mariadb
MariaDB Galera 集群上的死鎖觸發器
當更新具有相同數據的行時,我可以在我們的(測試)Galera 集群上觸發死鎖。
INSERT INTO test (id, val) VALUES (1, 42), (2, 47); -- Query OK, 2 rows affected (0.01 sec) -- Records: 2 Duplicates: 0 Warnings: 0
將值更新為不同的值不會導致錯誤:
UPDATE test SET val=43 WHERE id=1; -- Query OK, 1 row affected (0.00 sec) -- Rows matched: 1 Changed: 1 Warnings: 0
當您將其設置為相同的值時:
UPDATE test SET val=47 WHERE id=2; -- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
這似乎是一種被錯誤轉發的警告(來自
/var/log/mysql
):140106 13:52:04 [Warning] WSREP: SQL statement was ineffective, THD: 12, buf: 103 QUERY: UPDATE test SET val=47 WHERE id=2 => Skipping replication 140106 13:52:04 [Warning] WSREP: SQL statement was ineffective, THD: 12, buf: 103 QUERY: UPDATE test SET val=47 WHERE id=2 => Skipping replication
注意:表的創建方式如下
CREATE TABLE IF NOT EXISTS `test` ( `id` int(3) NOT NULL PRIMARY KEY, `val` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf-8;
如果伺服器上的二進制日誌格式是“STATEMENT”(通過 SELECT @@binlog_format 進行檢查;)您應該使用 –binlog-format=row 重新啟動伺服器,然後重試查詢。通常這會解決你的問題!