Conflict detected -

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

I am running 3 node cluster without LB, I got below error saying 2 threads are conflict with each other and resulting in query failed? Below is the mysqld log for the conflict, can anyone help me understand what the issue is and how can I resolve it?

================
TRANSACTION 3659919, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
MySQL thread id 10, OS thread handle 140590323529472, query id 1635403 wsrep: writing row for write-set (1584077)
TRANSACTION 3659920, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
MySQL thread id 12, OS thread handle 140590322472704, query id 1635404 wsrep: deleting row for write-set (1584078)
TRANSACTION 3677465, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT
MySQL thread id 2, OS thread handle 140593696847616, query id 1769159 Applying batch of row changes (update)
TRANSACTION 3677455, ACTIVE 0 sec rollback
mysql tables in use 1, locked 1
ROLLING BACK
MySQL thread id 540, OS thread handle 140590321415936, query id 1768920 192.168.1.41 gms wsrep: replicating and certifying write set(-1)
update orchestrationtasks set status = 0,lastrun = UNIX_TIMESTAMP(NOW()), priority=next_priority, next_priority=0 where id='66'
2023-09-18T19:03:05.695720Z 2 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2023-09-18T19:03:05.695776Z 2 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2023-09-18T19:03:05.696146Z 2 [Note] [MY-000000] [WSREP] Winning thread:
   THD: 2, mode: high priority, state: exec, conflict: executing, seqno: 1591737
   SQL: (null)

2023-09-18T19:03:05.697647Z 2 [Note] [MY-000000] [WSREP] Victim thread:
   THD: 540, mode: local, state: exec, conflict: aborting, seqno: 1591738
   SQL: update orchestrationtasks set status = 0,lastrun = UNIX_TIMESTAMP(NOW()), priority=next_priority, next_priority=0 where id='66'
2

When this happens, look at global status for:

  • wsrep_local_cert_failures
  • wsrep_local_bf_aborts

Look at the values now, and then after you see one of these notes. If the numbers increase, then you have two transactions on the same node that are trying to update the same row and they are conflicting.