MySQL PXC叢集多個節點同時大量併發update同一行

likingzi發表於2024-07-15
MySql

如本文標題，MySQL PXC叢集多個節點同時大量併發update同一行資料，會怎樣？
為此，本人做了一個測試，來驗證到底會怎樣！

一、生成測試資料

mysql> CREATE TABLE test (
    ->  `a` int(11) NOT NULL DEFAULT 0,
    ->  `b` int(11) DEFAULT NULL,
    ->  `c` int(11) DEFAULT NULL,
    ->  `d` int(11) DEFAULT NULL,
    ->  PRIMARY KEY (`a`),
    ->  UNIQUE KEY `uk_bc` (`b`,`c`)
    -> );
Query OK, 0 rows affected, 4 warnings (0.01 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> INSERT INTO test VALUES(1, 1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    1 |    1 |    1 |
+---+------+------+------+
1 row in set (0.00 sec)

二、在不同節點測試可能的死鎖情況

在node1、node2同時批次執行更新最後一條記錄，採用Secure CRT的"Send Commands to All Sessions"操作技巧同時發起操作，同時觀察兩個節點的日誌資訊。

for i in {1..100}
do
mysql -uroot -p'passwd' -e "use test;select max(a) + 1 into @i from test;update test set a = @i where a = @i - 1;" >> temp.log 2>&1
done

node1的日誌資訊99行，如下：

TRANSACTION 13054, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 
MySQL thread id 16, OS thread handle 140344638252800, query id 24180 Applying batch of row changes (update)
TRANSACTION 13053, ACTIVE 0 sec
mysql tables in use 1, locked 1
, undo log entries 2
MySQL thread id 3092, OS thread handle 140344301782784, query id 24178 localhost root wsrep: replicating and certifying write set
update test set a = @i where a = @i - 1
2024-05-13T11:30:41.089484Z 16 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2024-05-13T11:30:41.089494Z 16 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2024-05-13T11:30:41.089500Z 16 [Note] [MY-000000] [WSREP] Winning thread: 
   THD: 16, mode: high priority, state: exec, conflict: executing, seqno: 5913
   SQL: (null)

2024-05-13T11:30:41.089505Z 16 [Note] [MY-000000] [WSREP] Victim thread: 
   THD: 3092, mode: local, state: exec, conflict: certifying, seqno: -1
   SQL: update test set a = @i where a = @i - 1

node2日誌資訊139行，與node1類似，此處忽略。
分析所有日誌資訊，node1 5個報錯，5個Victim thread；node2 7個報錯，7個Victim thread。
可見，同時批次更新同一行資料有可能導致衝突的發生，總有一部分失敗的情況，但不會造成叢集異常。
理論上如果沒有衝突的發生，更新後a值應該是200以上，但是實際上最後的結果是142：

mysql> select * from test;
+-----+------+------+------+
| a   | b    | c    | d    |
+-----+------+------+------+
| 142 |    1 |    1 |    1 |
+-----+------+------+------+
1 row in set (0.00 sec)

三、結論

可見PXC叢集應對這種同時對同一行資料的大批次更新，是有固定策略的，部分失敗在所難免，可以從應用實現方面解決這個問題，如提前顯式鎖定、單執行緒順序執行、變數標識等。
另外筆者測試了在同一節點同時批次更新的情況，結果與不同節點是完全一樣的，這也印證了對於PXC叢集的節點使用，是可以採用負載均衡機制連線不同節點的。當然負載均衡方式對於PXC叢集意義不大，因為它本身是一個多主叢集，所有的修改操作都是多節點併發執行的。

相關文章