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

Allow statement-based replication for REPLACE and INSERT…ON DUPLICATE KEY UPDATE

    XMLWordPrintable

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.

      The documentation at https://mariadb.com/kb/en/binary-log-formats/ states that even if one is using Statement based logging, it's assumed that the tables are the same on master and slave and both are using the same storage engine. In this case Statement logging should work.
      In mixed mode, we prefer security over speed and in this case statements using ON DUPLICATE KEY UPDATE should use binary logging to ensure it works even if the slave is using different storage engines or different index definitions than the master.

      Attachments

        Issue Links

          Activity

            People

              sachin.setiya.007 Sachin Setiya (Inactive)
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.