Understanding Half-Consistent Reads in InnoDB and Their Impact on Transactions

What is a half‑consistent read?

It is an optimization used in UPDATE statements that combines a current read with the regular consistent read under the RC (read‑committed) isolation level.

When the WHERE clause of an UPDATE matches rows that are already locked, InnoDB reads the latest committed version of those rows again to decide whether the lock is really needed.

This behavior only occurs under RC isolation or when the deprecated innodb_locks_unsafe_for_binlog=1 parameter is set.

The innodb_locks_unsafe_for_binlog parameter was removed in MySQL 8.0 because it can cause data inconsistency.

Test Cases

InnoDB’s strength lies in its transaction support; consistency is guaranteed by isolation levels and row locks. Two test cases illustrate the effect of half‑consistent reads.

Case 1

RC isolation, three sessions execute transactions.

-- Create test table
create table zlm.t(id int, sal int) engine=innodb default charset=utf8mb4;

-- Insert test data via stored procedure
drop procedure if exists zlm.proc_t;
create procedure zlm.proc_t()
begin
  declare i int default 1;
  declare j int default 100;
  while i<11 do
    insert into t(id,sal) values(i,j);
    set i=i+1;
    set j=j+100;
  end while;
end $$

-- Verify data
select * from t;

-- Enable RC isolation
set @@global.tx_isolation='read-committed';

Note: From MySQL 8.0.3 the tx_isolation variable was removed; use transaction_isolation instead.

-- Open two new sessions (global parameters affect only new connections)
show variables like 'tx_isolation';
select connection_id();

-- Session 1 executes a current‑read SELECT
begin; select * from t where id>3 and id<6 for update;

-- Session 2 executes a current‑read SELECT on id=7
begin; select * from t where id=7 for update;

-- Session 3 executes an UPDATE on id=7
begin; update t set sal=sal+1 where id=7;

Session 1 locks rows id=4 and id=5; Session 2 waits for the lock on id=4 and times out; Session 3 succeeds because the half‑consistent read releases the lock on id=7 early.

Case 2

RC isolation, Session 1 runs a SELECT without a WHERE clause, causing a full‑table scan.

-- Session 1 current‑read SELECT (full table)
begin; select * from t for update;

-- Session 2 current‑read SELECT on id=7 (blocked)
begin; select * from t where id=7 for update;

-- Session 3 UPDATE on id=7 (blocked)
begin; update t set sal=sal+1 where id=7;

Because the table has no index, the full‑table scan locks all rows under RC, so Sessions 2 and 3 are blocked by Session 1.

Analysis

Without indexes, all statements perform full‑table scans; RC isolation issues current reads that acquire row locks for every accessed record.

In Case 1, Session 1 only locks rows it reads (id=4,5); Session 3 can update id=7 because its lock does not conflict, demonstrating the half‑consistent read optimization.

In Case 2, Session 1 locks the entire table, preventing other sessions from acquiring any row locks, leading to lock‑wait timeouts.

Conclusion

Under RC isolation, UPDATE statements can leverage half‑consistent reads to perform an extra check; when the WHERE clause matches rows that do not conflict with existing locks, InnoDB releases the lock early, reducing lock contention and improving concurrency, albeit deviating from the strict two‑phase locking protocol.

References

InnoDB Consistent Read

InnoDB Locks Set

Original Blog Post (Chinese)