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

**URL:** <https://forums.percona.com/t/wsrep-detected-deadlock-conflict-and-aborted-the-transaction-on-isolated-table/11414>\
**Category:** Percona XtraDB Cluster 5.x\
**Tags:** mysql, percona\
**Created:** [July 16, 2021, 2:25pm UTC](https://forums.percona.com/t/wsrep-detected-deadlock-conflict-and-aborted-the-transaction-on-isolated-table/11414 "2021-07-16T14:25:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![hgraca](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hgraca/32/2928_2.png) [@hgraca](https://forums.percona.com/u/hgraca)\
**Post date:** [July 16, 2021, 2:25pm UTC](https://forums.percona.com/t/wsrep-detected-deadlock-conflict-and-aborted-the-transaction-on-isolated-table/11414/1 "2021-07-16T14:25:28Z")

</div>

Hi,

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

```auto
            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

---

<div class="post-metadata">

**Author:** ![vadimtk](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadimtk/32/9528_2.png) [@vadimtk](https://forums.percona.com/u/vadimtk)\
**Post date:** [July 16, 2021, 6:26pm UTC](https://forums.percona.com/t/wsrep-detected-deadlock-conflict-and-aborted-the-transaction-on-isolated-table/11414/2 "2021-07-16T18:26:55Z")

</div>

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](https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html)  
or to quote:  
If the transaction isolation level is [`READ COMMITTED`](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_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.

---

<div class="post-metadata">

**Author:** ![hgraca](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hgraca/32/2928_2.png) [@hgraca](https://forums.percona.com/u/hgraca)\
**Post date:** [July 19, 2021, 9:26pm UTC](https://forums.percona.com/t/wsrep-detected-deadlock-conflict-and-aborted-the-transaction-on-isolated-table/11414/3 "2021-07-19T21:26:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hgraca](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hgraca/32/2928_2.png) [@hgraca](https://forums.percona.com/u/hgraca)\
**Post date:** [July 20, 2021, 3:42pm UTC](https://forums.percona.com/t/wsrep-detected-deadlock-conflict-and-aborted-the-transaction-on-isolated-table/11414/4 "2021-07-20T15:42:06Z")

</div>

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 🙂