Tagged articles
Repeatable Read
26 articles · Page 1 of 1
This comprehensive guide explains MySQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and lock mechanisms (table, row, gap, next-key locks), demonstrating how dirty reads, non-repeatable reads, and phantom reads occur, with practical deadlock prevention strategies and enterprise-level isolation level selection guidelines.
ACIDLock MechanismsMySQL
0 likes · 12 min read

This article explains how MySQL's Repeatable Read isolation level prevents phantom reads by combining MVCC snapshot reads for consistent views and Next-Key Locks (record + gap locks) to block inserts during current reads like UPDATE, detailing the ReadView mechanism, version chains, and a concrete phantom read scenario.
Isolation LevelMVCCMySQL
0 likes · 8 min read

The article explains why MySQL's InnoDB engine uses REPEATABLE READ by default, clarifies the differences between snapshot and current reads, compares RC and RR isolation levels, details MVCC mechanics, and provides a concise interview answer template with practical SQL examples.
InnoDBMVCCMySQL
0 likes · 10 min read

The article explains that MVCC in MySQL InnoDB prevents phantom reads for snapshot (ordinary SELECT) queries by using ReadView, while write‑conflict scenarios that appear as phantom reads are handled by unique index checks and gap locks, highlighting the distinction between snapshot and current reads.
InnoDBMVCCPhantom Read
0 likes · 5 min read

This article walks through a concrete MySQL InnoDB deadlock scenario caused by Gap Locks, showing the exact SQL statements, lock states at each step, log excerpts, and practical tips to avoid similar deadlocks in repeatable‑read transactions.
InnoDBMySQLRepeatable Read
0 likes · 11 min read


ITPUBOct 17, 2025 · Databases
The article explains MySQL's default Repeatable Read isolation level, how its lock and binlog mechanisms affect consistency and replication, and why many high‑traffic internet firms change to Read Committed to boost concurrency and reduce deadlocks despite the need to handle phantom reads.
ConcurrencyIsolation LevelMySQL
0 likes · 8 min read

This article explains MySQL’s transaction isolation mechanisms, compares the four isolation levels with concrete examples, shows how they affect phenomena like dirty reads, non‑repeatable reads and phantom reads, and provides practical guidance on configuring and avoiding long transactions.
MVCCMySQLRead Committed
0 likes · 15 min read

This article demonstrates how InnoDB handles row, next‑key, and gap locks for a SELECT … FOR SHARE query under REPEATABLE‑READ and READ‑COMMITTED isolation levels, showing the preparation steps, SQL examples, lock inspection queries, and a concise summary of the differences.
InnoDBLocksMySQL
0 likes · 8 min read

This article demonstrates how InnoDB acquires different types of row and gap locks under REPEATABLE-READ and READ-COMMITTED isolation levels by creating a test table, inserting data, setting transaction isolation, executing SELECT ... FOR SHARE statements, and inspecting lock information from performance_schema.
InnoDBMySQLRead Committed
0 likes · 9 min read

This article investigates whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting a series of current‑read and snapshot‑read experiments, analyzing transaction behavior, MVCC mechanisms, and offering recommendations to avoid phantom anomalies.
InnoDBMVCCMySQL
0 likes · 19 min read

This article examines whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting systematic current‑read and snapshot‑read experiments, analyzing lock mechanisms, MVCC behavior, and presenting detailed SQL scripts, results, and recommendations for avoiding phantom reads.
InnoDBMVCCMySQL
0 likes · 18 min read

This article explains why the OBOracle "set transaction read only" command provides repeatable‑read semantics, evaluates the behavior of the JDBC conn.setReadOnly(true) setting, and shows how to achieve true repeatable‑read isolation by enabling the oracleChangeReadOnlyToRepeatableRead option in the OceanBase client.
JDBCOceanBaseOracle Mode
0 likes · 8 min read

This comprehensive guide explains MySQL transaction fundamentals, defines dirty reads, non‑repeatable reads and phantom reads, compares the four isolation levels, shows how to query and set them, and details the underlying locking mechanisms such as MVCC and next‑key locks.
ACIDMySQLREAD UNCOMMITTED
0 likes · 20 min read

This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—by creating a simple table, inserting test data, and running step‑by‑step SQL scripts that illustrate the behavior of each level, including phenomena such as dirty reads, non‑repeatable reads, phantom reads, and locking.
InnoDBMySQLREAD UNCOMMITTED
0 likes · 11 min read

This article reproduces a MySQL 8.0.32 InnoDB scenario where inserting a row that violates a unique index causes a next‑key exclusive lock on the primary‑key supremum record, explains the underlying implicit‑lock mechanism, walks through the relevant source‑code stack, and details the lock‑conversion and rollback steps under REPEATABLE‑READ isolation.
Database InternalsInnoDBMySQL
0 likes · 25 min read


ITPUBApr 19, 2023 · Databases
This article explains how InnoDB’s REPEATABLE READ isolation level uses MVCC and gap locks to prevent most phantom reads, illustrates the mechanisms with SQL examples and diagrams, and outlines the remaining edge cases and best practices for fully avoiding phantom reads.
InnoDBMVCCMySQL
0 likes · 9 min read

When trying to change a table’s primary key from int to bigint, a 500‑million‑row MySQL table required data archiving and a bulk MODIFY, but parallel DELETE statements caused lock‑wait timeouts; experiments reveal that under REPEATABLE READ the range scan locks the boundary row, leading to contention.
DELETELarge TablesLock
0 likes · 11 min read

This article explains the concept of phantom reads, distinguishes them from non‑repeatable reads, critiques the ANSI SQL isolation‑level definitions, and shows how MySQL implements repeatable‑read using locks and MVCC to prevent both strict and loosely defined phantom anomalies.
ANSI SQLIsolation LevelMySQL
0 likes · 14 min read

This article explains why MySQL chooses Repeatable Read as its default transaction isolation level, contrasting it with Oracle’s default Read Committed, and discusses how binlog formats, GAP locks, and historical bugs influence this design decision.
Isolation LevelMySQLRead Committed
0 likes · 9 min read

This article explains MySQL's four transaction isolation levels, focusing on the default Repeatable Read, how MVCC enables snapshot reads to avoid phantom reads, the differences between snapshot and current reads, and practical techniques such as serializable isolation and next‑key locking to fully prevent phantom anomalies.
MVCCMySQLPhantom Read
0 likes · 11 min read

This article presents a hands‑on experiment that measures the performance impact of using pt‑table‑checksum on a MySQL master‑slave setup, explains the tool’s design choices such as reduced lock‑wait time and RR isolation, and concludes that its effect on production traffic is minimal when configured properly.
Database ReplicationInnoDBMySQL
0 likes · 4 min read

This article explains MySQL’s transaction isolation levels, clarifies the differences between dirty reads, non‑repeatable reads and phantom reads, and demonstrates through experiments why the REPEATABLE READ level can prevent phantom reads, while also discussing its limitations, MVCC, and locking mechanisms.
MVCCMySQLPhantom Read
0 likes · 14 min read

This article explains the four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—detailing how each level prevents or allows dirty reads, non‑repeatable reads, and phantom reads, and describes the underlying lock mechanisms.
ACIDDirty ReadRepeatable Read
0 likes · 14 min read

This article demonstrates how MySQL's repeatable-read isolation with consistent snapshots causes transaction A to read the original value 1 while transaction B reads the updated value 3, explaining InnoDB's MVCC versioning, active transaction arrays, and the distinction between consistent and current reads.
InnoDBMVCCMySQL
0 likes · 10 min read

This article explains the three classic concurrency anomalies—dirty read, non‑repeatable read, and phantom read—describes the four standard isolation levels (read uncommitted, read committed, repeatable read, serializable), and shows how databases implement isolation using rollback logs and read‑views.
Database ConcurrencyDirty ReadRepeatable Read
0 likes · 6 min read


ITPUBApr 26, 2016 · Databases
This article analyzes a puzzling MySQL deadlock scenario where concurrent DELETE statements on a uniquely indexed row lead to a deadlock, explaining the lock modes, InnoDB's deadlock‑prevention strategy, and the conditions that make such deadlocks possible.
ConcurrencyInnoDBMySQL
0 likes · 13 min read
