Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17603

REPLACE and INSERT…ON DUPLICATE KEY UPDATE are deadlock-prone in statement-based replication

    Details

      Description

      MySQL 5.7.4 changed the behaviour of REPLACE and INSERT…ON DUPLICATE KEY UPDATE in the InnoDB storage engine. Upon encountering a duplicate key, it would no longer directly fall back to UPDATE, but instead it would proceed to acquire an exclusive lock on every index record for the row on which the INSERT failed.

      The extra locking was motivated by a public bug report: MySQL Bug#50413 insert on duplicate key update sometimes writes binlog position incorrectly (Oracle internal BUG#11758237). The fix was followed up by a couple of regression fixes.

      For one MariaDB user, reverting these changes significantly reduced the deadlock rate of INSERT…ON DUPLICATE KEY UPDATE.

      MDEV-17073 disabled the changes except when statement-based replication is being used. Even with statement-based replication, the locking could be replaced with additional logging: extending the replication event with information on which index records were locked when the duplicate was detected. (Note that the dict_table_t::indexes may be sorted differently on master and slave, especially if CREATE UNIQUE INDEX was executed with ALGORITHM=INPLACE.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                sachin.setiya.007 Sachin Setiya
                Reporter:
                marko Marko Mäkelä
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: