'WSREP detected deadlock/conflict and aborted the transaction' on isolated table

MySQL & MariaDB Percona XtraDB Cluster 5.x
,
1

Hi,

I am getting a WSREP detected deadlock/conflict and aborted the transaction. error when running a DML like:

            INSERT INTO `reporting__sales`(
                ...
            )
            SELECT ...
            FROM ...
                LEFT JOIN ...
                LEFT JOIN ...
            WHERE
                ...
            ON DUPLICATE KEY UPDATE
                ...

It is a single DML and there is no other process using that table, so I’m somewhat confused about how this can be happening.

Any ideas about this?

Tkx

2

if you insert data into isolated table, then I expect you have locking on tables in SELECT statement, as I see you have 3 tables here.
You can read more about INSERT … SELECT locking here:
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
or to quote:
If the transaction isolation level is READ COMMITTED , InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S . InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.

3

Hi, thank you for your reply.

I tried that, and it did help.

However, when I execute the SQL it returns me 200 rows, but when I put that same SQL in the INSERT, it stores 45000 rows…

This is quite confusing.

I am gonna try using a SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... REPLACE INTO TABLE ...(...) and see what happens.

If you have any other suggestions, I would appreciate it.

4

For posterity:

I had a bug in my code, the dates were not being calculated correctly, hence the difference in results.

The deadlock disappeared since I started using the intermediate filedump.

I did however had to give FILE permissions to the user the application uses.

In any case, again, tkx for you reply :slight_smile: