Deadlock found when trying to get lock; try restarting transaction?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

2020-12-15T03:03:15.997431-05:00 11 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------

2020-12-15T03:03:15.997438-05:00 11 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2020-12-15T03:03:15.997443-05:00 11 [Note] [MY-000000] [WSREP] Winning thread:

THD: 11, mode: high priority, state: exec, conflict: executing, seqno: 390433

SQL: (null)

2020-12-15T03:03:15.997448-05:00 11 [Note] [MY-000000] [WSREP] Victim thread:

THD: 83852, mode: local, state: exec, conflict: executing, seqno: -1

SQL:

INSERT INTO tb.alt_n_nt(PaymentFileID,PaymentID,ClientID,EventID,AlertTypeID,NotificationTypeID,IsGenerated,CreatedAt,TemplateID,FileName,ErrorDetails)VALUES (PaymentFileID,p_PaymentID,p_ClientID,13,1,4,0,now(),5,CONCAT('Remittance File: ‘,p_ClientID,’‘,p_PaymentID),CONCAT(‘Remittance Creation Failed -’,’ ClientID: ‘,p_ClientID,’ PaymentID: ',p_PaymentID))TRANSACTION 1030660, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

MySQL thread id 13, OS thread handle 140115793000192, query id 14035220 wsrep: writing row for write-set (390541)TRANSACTION 1030659, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

, undo log entries 1

MySQL thread id 83852, OS thread handle 140115122792192, query id 14035219 prod-db-01.ibtechciti.com 172.31.0.216 root update

INSERT INTO tb.alt_n_nt (PaymentFileID,PaymentID,ClientID,EventID,AlertTypeID,NotificationTypeID,IsGenerated,CreatedAt,TemplateID,FileName,ErrorDetails)VALUES(PaymentFileID,p_PaymentID,p_ClientID,13,1,4,0,now(),5,CONCAT('Remittance File: ‘,p_ClientID,’‘,p_PaymentID),CONCAT(‘Remittance Creation Failed -’,’ ClientID: ‘,p_ClientID,’ PaymentID: ',p_PaymentID))

2

Hi lokesh123,

2020-12-15T03:03:15.997431-05:00 11 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------

2020-12-15T03:03:15.997438-05:00 11 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2020-12-15T03:03:15.997443-05:00 11 [Note] [MY-000000] [WSREP] Winning thread:

THD: 11, mode: high priority, state: exec, conflict: executing, seqno: 390433

Top 2 reasons for conflicts in PXC/Galera are:

  1. You are executing a DDL which conflicts with some other DML that will be aborted. Please refer to this blogpost https://www.percona.com/blog/2019/03/25/how-to-perform-compatible-schema-changes-in-percona-xtradb-cluster-advanced-alternative/

  2. You are doing multi mater writes. Even if PXC allows for multi master writes, due to the nature of certification process, if there is a race condition in which one DML was already certified by the majority of the nodes, another DML which conflict with the already certified DML will be aborted and rolled back. Please do check this other blogpost https://www.percona.com/blog/2012/11/20/understanding-multi-node-writing-conflict-metrics-in-percona-xtradb-cluster-and-galera/ , and check for wsrep_local_commits to identify if there are local commits on the node, and for wsrep_local_cert_failures and wsrep_local_bf_aborts for replication aborts.

Regards