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集群意义不大，因为它本身是一个多主集群，所有的修改操作都是多节点并发执行的。

​