wsrep conflict detected.

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi.

I have 3 nodes (4 CPUs, 12GB RAM each) running XtraDB 5.7.20 (Centos 7.4) and HAPROXY (Centos 7.4) woking as load balancing.

The cluster have a low use yet and sometimes I see in mysql log file notes about conflicts detected (see log bellow):

→ I can consider these notes under certain circumstances a normal condition, due the way the replication work ?
→ is there any way to avoid or decrease this conflict situations ?
→ I will lost data ?

I will appreciate any explanation about this situation.
BR

-----------mysqld.log--------------
*** Priority TRANSACTION:
TRANSACTION 34519027, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
MySQL thread id 8, OS thread handle 140498179241728, query id 7341 System lock

*** Victim TRANSACTION:
TRANSACTION 34519026, ACTIVE 0 sec
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 737, OS thread handle 140497776350976, query id 7339 fd00:faca:0:a0::17 piwik3 wsrep: initiating replication for write set (-1)
INSERT INTO piwik3_option (option_name, option_value, autoload) VALUES (‘lastTrackerCronRun’, ‘1519995375’, ‘0’) ON DUPLICATE KEY UPDATE option_value = ‘1519995375’
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 14 n bits 192 index PRIMARY of table piwik3.piwik3_option trx id 34519026 lock_mode X
2018-03-02T09:56:15.289850-03:00 8 [Note] WSREP: --------- CONFLICT DETECTED --------
2018-03-02T09:56:15.289868-03:00 8 [Note] WSREP: cluster conflict due to high priority abort for threads:

2018-03-02T09:56:15.289878-03:00 8 [Note] WSREP: Winning thread:
THD: 8, mode: applier, state: executing, conflict: no conflict, seqno: 3563224
SQL: (null)

2018-03-02T09:56:15.289887-03:00 8 [Note] WSREP: Victim thread:
THD: 737, mode: local, state: committing, conflict: no conflict, seqno: -1
SQL: INSERT INTO piwik3_option (option_name, option_value, autoload) VALUES (‘lastTrackerCronRun’, ‘1519995375’, ‘0’) ON DUPLICATE KEY UPDATE option_value = ‘1519995375’

2

PXC being multi-master product conflicts are natural.

Say your workload is modifying the same set of data from 2 different nodes then only one of the transaction can succeed and other has to fail.
Conflict represents the said condition. Thumb rule is the first committer to group channel wins.

How to reduce conflict ?

  • Conflicts are inherent to workload but if you can tune your workload so that work distribution among the node is disjoint then conflict can be reduced.

We haven’t encountered a case wherein conflict can cause loss of the data. So they are safe.

3

Check this example:

*** Priority TRANSACTION:
TRANSACTION 247907, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
MySQL thread id 4, OS thread handle 140108412753664, query id 245978 System lock

*** Victim TRANSACTION:
TRANSACTION 247678, ACTIVE 1 sec rollback
mysql tables in use 2, locked 2
ROLLING BACK , undo log entries 2565
MySQL thread id 5, OS thread handle 140108412487424, query id 245438 localhost root query end
insert into sbtest100 select * from sbtest1
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 66 n bits 144 index PRIMARY of table test.sbtest1 trx id 247678 lock mode S
2018-03-06T05:29:13.461659Z 4 [Note] WSREP: --------- CONFLICT DETECTED --------
2018-03-06T05:29:13.461669Z 4 [Note] WSREP: cluster conflict due to high priority abort for threads:

2018-03-06T05:29:13.461677Z 4 [Note] WSREP: Winning thread:
THD: 4, mode: applier, state: executing, conflict: no conflict, seqno: 122974
SQL: (null)

2018-03-06T05:29:13.461956Z 4 [Note] WSREP: Victim thread:
THD: 5, mode: local, state: executing, conflict: must abort, seqno: -1
SQL: insert into sbtest100 select * from sbtest1

I have created 10 tables and running a sysbench oltp workload on these 10 tables.

Then on another node, I am trying to use one of the pre-created tables to feed the data to the new table. Check below.

mysql> create table sbtest100 like sbtest1;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into sbtest100 select * from sbtest1;
ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction
mysql>

Conflict arises as sbtest1 is getting changed and applier or background thread (which has high priority transaction) needs write-lock on the sbtest1 but existing thread has read/shared lock on the sbtest1.

This is just one use-case.

4

Hi Krunalbauskar.

Thank you for the explanations and examples.