innodb-release-locks-early

The following option tells XtraDB to release its row locks earlier during COMMIT:

• Variable Name: innodb_release_locks_early
• Scope: Global
• Access Type: Read only (only in config file)
• Data Type: bool
• Default Value: OFF

This option is meant to be used when the binary log is enabled, and both sync_binlog=1 and innodb_flush_logs_at_trx_commit=1. In this case, during COMMIT, the two-phase commit protocol between XtraDB and the binary log needs to sync data to disk tree times: once in XtraDB for preparing the transaction, once for the binary log, and once again in XtraDB for committing the transaction. The syncs to disk are shared between multiple commits due to group commit, but it still causes some per-transaction delay.

Normally, any row locks held by a transaction are only released near the end of the commit phase, after all three syncs to disk have taken place. If many transactions are competing for the same locks, this can reduce the ability to share the syncs among transactions and significantly reduce throughput.

In this case, one solution is to enable this option. It will cause the row locks to be released already during the XtraDB prepare phase, after just one of the three syncs.

However, there are some caveats with enabling this option that must be carefully considered for the application at hand:

• When the row locks are released, the committing transaction is also made visible to other transactions. In effect, the transaction is committed to memory in the prepare step. This means that if there is an error later during the COMMIT step (typically writing to the binary log or innodb transaction log), a rollback is no longer possible. In such error cases, the server will be deliberately crashed to avoid inconsistent data.
• When a committed transaction is made visible to other transactions in the prepare step, it is not yet durably written to disk. This means that if the server crashes later during the COMMIT, after crash recovery the transaction may no longer exist in the database. In this case, an application may have seen a change in the database that does not exist after the crash (this is similar to the behaviour with innodb_flush_logs_at_trx_commit=2). If these caveats are acceptable, enabling the option may increse throughput in some cases. Note that the option does nothing if the binary log is not enabled and only XtraDB tables participate in a transaction. Also, if sync_binlog is not set to 1 and/or innodb_flush_logs_at_trx_commit is not set to 1, there is little or no performance improvement from using this option.

This feature is a port from the corresponding Facebook patch.

See MWL#163 for more information about the design and implementation of this feature.