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

Have a configuration variable to enable/disable lock release at XA PREPARE

    XMLWordPrintable

Details

    Description

      Background: Innodb could acquire S and GAP locks other than what is required for isolation, typically during unique secondary index insert and purge. It is also possible to acquire X locks for records that are not modified finally. Such locks could impact replica while trying to replay independent transactions in parallel. Specifically for XA transaction replayed up to XA PREPARE stage.

      Following projects were done to address the issue by releasing some locks early at XA Prepare.
      MDEV-26682 slave lock timeout with xa and gap locks: 10.5.13
      MDEV-33454 release row locks for non-modified rows at XA PREPARE: 10.6.18

      Theory: It is not possible to do any read or write by a transaction after XA prepare and it doesn't break 2PL and it doesn't break conflict serializability in theory. The locks that we must hold are the X locks taken for which actual modification has taken place. The locks that are possible to release are the S locks, GAP locks and X locks for which we haven't actually made any modifications. The last case could happen, for example, for update and delete when some condition in SQL mismatches after the records are scanned and locked in Innodb. One fine point to note here is that when we release such locks at XA PREPARE in primary(master) the serializability order shifts from commit to prepare for XA transactions and for statement based replication to work the transactions must be replayed in replica strictly maintaining this order.

      Once we ensure functional correctness, we need to evaluate the performance impact. Releasing S(and GAP) locks are straight forward while releasing X locks for unmodified records could be very expensive. More so for secondary index when there are other concurrent operations mainly because we don't have trx_id mark for modification of secondary index records.

      Another point to note here is the extra X locks are acquired during scan phase and is generally not required in replica for row based replication where we already have pre-identified record to be modified. The only case where it helps is when a table ha no primary key and no unique secondary key. So the user case should be rather rare and we could actually be slowing down by trying to release those X locks.

      Based on above analysis and discussion with marko we think it makes sense to allow user control this early lock release via a configuration variable.

      Here is the tentative specification I propose.

      1. We should attempt such early release of lock only in replica.
      2. We should have a configuration to control the locks released.
      3. By default release only shared locks (S and GAP)

      Configuration: replica_early_lock_release
      Type: Enum
      Values: NONE, SHARED, EXCLUSIVE
      Default: SHARED

      Attachments

        Issue Links

          Activity

            People

              debarun Debarun Banerjee
              debarun Debarun Banerjee
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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