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.
The article explains what a database transaction is, breaks down the ACID properties, details MySQL’s four isolation levels with their appropriate use‑cases and pitfalls, and provides step‑by‑step SQL and SpringBoot code to reproduce and resolve dirty reads, non‑repeatable reads, and phantom reads.
The article shows why a simple @Transactional approach fails under 300,000 concurrent users, explains MVCC behavior, demonstrates how to use row‑level pessimistic locks, sorting, FOR NO KEY UPDATE, short transactions, optimistic locking, and proper isolation levels, and outlines a production‑ready architecture with read/write splitting, Redis rate limiting and connection‑pool tuning.
This article explains the differences between MySQL's RR and RC isolation levels, details master‑slave replication and binlog formats, shows why RR is the default, and why many internet companies now prefer RC for higher concurrency despite its phantom‑read trade‑offs.
This article examines how MySQL's lock mechanisms and transaction isolation levels can cause deadlocks during high‑concurrency inserts, presents a real‑world case study, walks through log analysis and testing, and offers practical strategies to diagnose, prevent, and resolve such deadlocks.
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.
This article explains the differences between MySQL’s default REPEATABLE READ (RR) and READ COMMITTED (RC) isolation levels, why large internet firms switch to RC, the mechanics of master‑slave replication, the three binlog formats, and how gap locks affect consistency and performance.
This article provides a comprehensive overview of database transactions, explaining ACID properties, transaction states, explicit and implicit transaction control, isolation levels, and how to configure and use them in MySQL with practical SQL examples and code snippets.
This comprehensive guide explores MySQL transaction fundamentals, the ACID properties, various isolation levels, concurrency issues like dirty, non‑repeatable, and phantom reads, and dives deep into MVCC implementation, hidden fields, undo logs, and read views to help developers write reliable, consistent database code.
The article examines a MySQL InnoDB deadlock that occurs when concurrent inventory‑write transactions in a pre‑release environment contend for row and gap locks, explains InnoDB lock types, isolation‑level effects, and proposes solutions such as distributed locks and isolation level adjustments.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause Next‑Key lock deadlocks in MySQL under repeatable‑read isolation, demonstrates the locking behavior with practical SQL examples, and proposes strategies such as post‑locking and primary‑key updates to prevent such deadlocks.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—shows how they interact, and provides practical guidance for avoiding deadlocks and safely updating rows.
This article explains database isolation levels, including read committed, repeatable read, serializable, and snapshot isolation, discusses dirty reads, phantom reads, and how various systems such as SQL Server, PostgreSQL, MySQL, Oracle, MongoDB, and others implement or differ in these isolation semantics.
This article explains how MySQL handles concurrent transactions using MVCC, lock mechanisms, and various isolation levels, detailing dirty writes, dirty reads, non‑repeatable reads, phantom reads, and the internal structures such as ReadView that enable reliable data consistency.
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.
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.
This article explains the concept of database transactions, the ACID properties, MySQL's four isolation levels, the phenomena they cause such as dirty reads, non‑repeatable reads and phantom reads, and provides step‑by‑step experiments demonstrating each isolation level in practice.
This article explains MySQL transaction fundamentals, covering ACID concepts, isolation levels, MVCC implementation, transaction start methods, and practical examples that illustrate how InnoDB ensures atomicity, consistency, isolation, and durability while avoiding common pitfalls such as dirty reads and long‑running transactions.
This article explains how to configure Spring transaction isolation levels and propagation behaviors, illustrates the effects of dirty read, non‑repeatable read, and phantom read with examples, and provides practical code solutions for handling rollback of failed sub‑tasks and self‑invocation issues.
This article explains the four transaction isolation levels, defines phantom reads, and details how MySQL's InnoDB engine uses MVCC snapshot reads and next‑key locking (including the next‑key principle and lock composition) to prevent phantom reads under the REPEATABLE READ isolation level.
This article explains why phantom reads occur under MySQL's REPEATABLE READ isolation, how InnoDB uses MVCC for snapshot reads and next‑key locks for current reads, and demonstrates the mechanisms with code examples and version‑tracking diagrams.
This article explains MySQL transaction isolation concepts, the four standard isolation levels, their effects on dirty, non‑repeatable, and phantom reads, practical configuration steps, when to use repeatable read, how InnoDB implements isolation, and tips for avoiding long transactions and planning backups.
This article explains what database transactions are, demonstrates how to use Spring Boot’s @Transactional annotation with unit tests, and dives into transaction isolation levels and propagation behaviors, providing code examples and guidance for effective transaction management in Java backend applications.
This article explains the concepts of isolation levels, dirty reads, and phantom reads across various relational and NoSQL databases, describing how each system implements or lacks transaction isolation and the practical impact on data consistency and performance.
This article explains the concept of database transactions, the ACID properties, details MySQL’s four isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—and provides step‑by‑step command‑line experiments demonstrating phenomena such as dirty reads, non‑repeatable reads, phantom reads, and serialization effects.
A detailed case study investigates why DBLE middleware achieves only about 70% of MyCat's performance, revealing that excessive sorting CPU usage, a cmp‑function inefficiency, and a delete‑loop bug under REPEATABLE_READ isolation cause the slowdown, and demonstrates how adjusting isolation levels and fixing the cmp implementation restores expected performance.
This article examines the challenges of using transactions with database read/write splitting, including potential master‑slave switching errors, connection‑pool deadlocks caused by isolation levels and propagation, and presents Spring‑based dynamic data source and transaction management solutions.
This article explains what the NOLOCK hint does in SQL Server, why it is used, how it relates to transaction isolation levels, presents practical tests, discusses the relevance of NOLOCK after newer SQL Server versions, and shows why MySQL does not need an equivalent hint, offering guidance on when to apply it.
This article explains the fundamental ACID properties of database transactions, details the four isolation levels—READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE—illustrates common concurrency issues like dirty, non‑repeatable, and phantom reads with examples, and shows how Spring’s transaction propagation and additional features can be leveraged to manage them effectively.
This article explains the four MySQL isolation levels, their effects such as dirty reads, non‑repeatable reads and phantom reads, shows how to view and change the isolation level, and reviews the ACID principles of atomicity, consistency, isolation, and durability.
A production MySQL 5.5 instance suffered intermittent latency and bursts of slow queries, which were traced to an oversized enabled query cache that caused mutex contention during frequent DML operations, and disabling the cache resolved the issue.
This article explains Spring's declarative transaction mechanism, detailing transaction attributes such as propagation behavior, isolation levels, timeout and read‑only flags, and illustrates each propagation type with Java code examples to show how transactions are started, joined, suspended, or nested.
This article provides an in‑depth analysis of MySQL/InnoDB locking mechanisms, covering MVCC snapshot vs current reads, cluster indexes, two‑phase locking, isolation levels, lock behavior for simple and complex SQL statements, and practical deadlock diagnosis techniques.
