Early Lock Release (ELR) in MySQL: Principles, Implementation, and Performance Impact

Early Lock Release (ELR) is a classic database optimization that has been revisited in the multicore era to evaluate its effect on system performance. It aims to move the lock‑release step earlier in the transaction commit process.

A typical transaction in a database follows these steps:

0. begin transaction;

1. search & lock tuple for update;

2. update tuple & generate log;

3. write commit log;

4. flush logs;

5. unlock tuples;

6. commit & notify user;

Because flushing logs (step 4) incurs I/O latency, keeping the lock until after the flush blocks other transactions that need the same record. By releasing the lock in step 5 before the flush, concurrent transaction t2 can start earlier, increasing throughput, while the final commit (step 6) still waits for log persistence, so user‑visible latency is unchanged.

Advancing the unlock can cause side effects: t2 may read or modify data that t1 later rolls back, leading to cascade rollbacks and dirty reads. In some workloads (e.g., high‑frequency inventory updates) dirty reads may be acceptable, but if t2 writes the dirty data to another durable system, business‑level problems can arise.

Commit failures most often stem from log‑write failures (disk full, disk errors, network‑mounted storage). With removable media or cloud‑based disks, failures become more likely. If the log file is a single, strictly ordered LSN stream, a failure for t1 also forces t2 to fail, and rolling back an already‑committed in‑memory transaction can be complex; the simplest reaction is a crash and crash‑recovery.

MySQL, the most popular open‑source database, has experimented with ELR. At the server layer, Facebook implemented an ELR patch that released locks during the prepare() phase of the XA transaction, but the patch was later reverted in MariaDB due to a fatal bug related to transaction ordering during crash recovery.

InnoDB’s implementation can be examined in MySQL 5.7.20. The commit flow is:

trx_commit() -> trx_commit_low() -> trx_commit_in_memory()

Inside trx_commit_in_memory() the lock release occurs before log flushing:

trx_commit_in_memory() -> lock_trx_release_locks(), trx_flush_log_if_need()

The function lock_trx_release_locks() contains a comment explaining that the transaction becomes visible to other transactions before the log segment is flushed, which can lead to dirty reads if the log flush later fails.

If log flushing fails, the call chain is:

trx_flush_log_if_needed() -> trx_flush_log_if_needed_low() -> log_write_up_to() -> log_write_flush_to_disk_low() -> fil_flush() -> os_file_fsync_posix()

The final os_file_fsync_posix() wraps fsync . On Linux, a failed fsync (e.g., EIO) is retried after a sleep, and later versions of MySQL have fixed the issue where a retry could mask the failure.

Performance studies show that ELR can improve throughput, as demonstrated in the cited papers. However, in MySQL the XA protocol for binlog handling masks much of the benefit, and the server‑level patch was ultimately rolled back.

References

1 Implementation Techniques for Main Memory Database Systems. https://15721.courses.cs.cmu.edu/spring2016/papers/p1-dewitt.pdf
2 Aether: A Scalable Approach to Logging. http://www.cs.albany.edu/~jhh/courses/readings/johnson.vldb10.logging.pdf
3 Improving OLTP concurrency through Early Lock Release. https://infoscience.epfl.ch//record/152158/files/ELR-single-column.pdf
4 PostgreSQL's fsync() surprise. https://lwn.net/Articles/752063/
5 PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS. https://www.postgresql.org/message-id/flat/CAMsr%2BYHh%2B5Oq4xziwwoEfhoTZgr07vdGG%2Bhu%3D1adXx59aTeaoQ%40mail.gmail.com#CAMsr+YHh+5Oq4xziwwoEfhoTZgr07vdGG+hu=1adXx59aTeaoQ@mail.gmail.com
6 Implement release of row locks in InnoDB during prepare() phase. http://worklog.askmonty.org/worklog/Server-Sprint/?tid=163
7 –innodb-release-locks-early=1 breaks InnoDB crash recovery. https://bugs.launchpad.net/maria/+bug/798213
8 Tale of a Bug. https://kristiannielsen.livejournal.com/15893.html