System data synchronization between systems, recently when changes occurred, the data volume of a certain interface increased, frequent deadlocks happened. After colleagues investigated, they found that it was caused by gap locks. MySQL is not used much, so I’ll record it down.

mysql gap locks, background, principle, how to disable gap locks, what problems will occur if disabled; you are a senior database DBA, provide an extended analysis and supplement for my problem. What is phantom read? Which RC or RR mode do mainstream large factories choose? I am in the financial industry, securities system, which scheme do you recommend me to use? Compile the above questions and output documentation.

In MySQL, especially in high-concurrency scenarios, “locks” are a topic that cannot be avoided. Gap Locks are a core feature of the InnoDB engine, but they are also often the root cause of performance bottlenecks and deadlocks.

This article will help you quickly understand the core, pros and cons of gap locks, and provide you (especially those in the financial industry) with clear architectural selection recommendations.

What are Phantom Reads and Gap Locks?

• Phantom Read: In a transaction, you initially read a range of data (e.g., ID > 100) returning 10 records. After another transaction inserts a record with ID=101 and commits, when you re-read the same range, it now returns 11 records. This “extra” new record is like an illusion, hence the name “Phantom Read.”

• Gap Lock: MySQL introduces the Gap Lock in the RR (Repeatable Read) isolation level to address the issue of “Phantom Reads.” It doesn’t lock individual data rows; instead, it locks the “gaps” between data.

  • Purpose: To prevent other transactions from inserting new data into these “gaps.”
  • Cost: The scope of the lock expands, reducing concurrency and increasing the risk of deadlocks.

How to “Close” Gap Locks?

The most direct method is to lower the database isolation level from RR (Repeatable Read) to RC (Read Committed).

-- Set the current session isolation level to RC
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- Permanent modification (requires modifying my.cnf and restarting)
-- [mysqld]
-- transaction-isolation = READ-COMMITTED

⚠️ Consequences of Closure (i.e., RC-level Features)

1. Performance Improvement: Without gap locks, the granularity of locks is smaller, resulting in a significant increase in concurrency throughput.

2. Reduced Deadlocks: Most deadlocks caused by gap locks will disappear.

3. Phantom Reads/Non-Repeatable Reads: This is a “feature” of RC-level features – results of two queries within a transaction may be inconsistent.

4. [Core Requirement] Must Be Used with ROW Format Binlog:

   If using RC-level, it’s mandatory to set binlog_format to ROW. Otherwise, due to the lack of gap lock protection during master-slave replication, data inconsistency will occur between the master and slave.

Mainstream Factory Choices: RC or RR?

Answer: The vast majority of leading internet companies choose RC (Read Committed) + ROW Binlog mode.

• Reason: Internet businesses (such as e-commerce and social media) pursue extreme high concurrency. They cannot tolerate the frequent lock waiting and deadlocks caused by RR gap locks.
• Trade-off: They choose to abandon database-level “repeatable read,” instead relying on optimistic locking (such as version numbers, CAS) at the application layer to ensure the logical consistency of key business areas (such as inventory and balance).

How Should the Financial Industry (Brokerage Firms) Choose?

For brokerage systems with extremely high data consistency requirements, it is recommended to implement scenario-based governance:

Option 1: Core Trading System (High Concurrency, Low Latency)

Recommendation: RC (Read Committed) + ROW Binlog + Application-Level Optimistic Locking

• Scenario: Order Matching, Placing Orders, Funds Deduction.
• Reasoning: The concurrency pressure of trading systems is comparable to that of flash sales on the internet. RR’s gap locking will become a performance bottleneck, leading to severe lock competition and even deadlocks – something a trading system cannot tolerate.
• Countermeasures: Use RC to guarantee high performance while ensuring financial security within application code (e.g., in Java) through UPDATE ... WHERE balance > ? or using CAS version number mechanisms to prevent overselling.

Option Two: Clearing and Reconciliation System (Batch Processing, High Consistency)

Recommendation: RR (Repeatable Read)

• Scenario: Post-settlement bulk reconciliation, report generation, daily final settlement.
• Reasoning: Batch processing tasks require a “consistent snapshot” to run on. It needs the capabilities provided by RR level, ensuring that data does not experience “phantom reads” throughout the entire statistical process, guaranteeing the general ledger is accurate.

Summary

Summary

Summary

For securities broker core systems, RC + ROW Binlog is the mainstream architecture solution that balances performance and consistency; however, it requires the development team to take on more responsibility for ensuring data consistency at the application layer.