BestHub
Sign in
Tagged articles
107 articles
Page 1 of 2
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jan 8, 2026 · Databases

Understanding MySQL Transaction Isolation Levels with Practical Examples

This article explains MySQL's four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—by creating a simple table, running paired transactions, and showing how each level affects data visibility, concurrency, and potential anomalies such as dirty reads, non‑repeatable reads, and phantom reads.

Database ConcurrencyInnoDBSQL
0 likes · 10 min read
Understanding MySQL Transaction Isolation Levels with Practical Examples
Ray's Galactic Tech
Ray's Galactic Tech
Jan 2, 2026 · Databases

How to Choose the Right PostgreSQL Isolation Level and Avoid Concurrency Bugs

PostgreSQL offers four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—each preventing specific concurrency anomalies like dirty reads, non‑repeatable reads, and phantom reads, and the article explains their behavior, configuration commands, practical usage, performance trade‑offs, and production best practices.

PostgreSQLSQLconcurrency
0 likes · 9 min read
How to Choose the Right PostgreSQL Isolation Level and Avoid Concurrency Bugs
Java Tech Enthusiast
Java Tech Enthusiast
Dec 6, 2025 · Databases

6 Critical MySQL Pitfalls and How to Avoid Them

Learn how to prevent six frequent MySQL problems—including index misuse, transaction isolation anomalies, inefficient pagination, charset and collation errors, risky foreign-key cascades, and misconfigured connection pools—through detailed explanations, code examples, and practical mitigation strategies.

CharsetConnection PoolDatabase Performance
0 likes · 22 min read
6 Critical MySQL Pitfalls and How to Avoid Them
IT Services Circle
IT Services Circle
Dec 1, 2025 · Databases

Master MySQL MVCC: Unlocking Concurrency, Locks, and Isolation Levels

This article explains why MySQL uses Multi-Version Concurrency Control, how it replaces traditional locking, the inner workings of hidden fields, undo logs, and read views, and details each transaction isolation level with practical SQL examples and common anomalies such as dirty, non‑repeatable, and phantom reads.

InnoDBMVCCRead View
0 likes · 17 min read
Master MySQL MVCC: Unlocking Concurrency, Locks, and Isolation Levels
Sohu Tech Products
Sohu Tech Products
Nov 26, 2025 · Databases

Master MySQL Transaction Isolation: From Dirty Reads to Serializable

This article deeply explores MySQL transaction isolation levels, explaining ACID principles, the three concurrency problems (dirty read, non‑repeatable read, phantom read), the SQL standard isolation tiers, InnoDB's MVCC and locking mechanisms, practical implementation details for each level, and guidance on choosing and configuring the appropriate isolation level for real‑world applications.

MVCCdatabaselocking
0 likes · 20 min read
Master MySQL Transaction Isolation: From Dirty Reads to Serializable
Sanyou's Java Diary
Sanyou's Java Diary
Aug 25, 2025 · Databases

Understanding MySQL Next-Key Locks: Preventing Phantom Reads and Deadlocks

This article explains MySQL InnoDB's Next-Key Lock mechanism, how it combines row and gap locks to prevent phantom reads under the REPEATABLE READ isolation level, illustrates lock ranges with examples, discusses when it degrades to row or gap locks, and highlights its advantages and potential deadlock risks.

InnoDBNext-key Lockdeadlock
0 likes · 8 min read
Understanding MySQL Next-Key Locks: Preventing Phantom Reads and Deadlocks
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jun 11, 2025 · Databases

Understanding MySQL Transaction Isolation Levels with Real‑World Examples

This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—through step‑by‑step examples, demonstrating their effects on dirty reads, non‑repeatable reads, phantom reads, and performance considerations, helping developers choose the appropriate level.

Database ConcurrencyInnoDBSQL
0 likes · 10 min read
Understanding MySQL Transaction Isolation Levels with Real‑World Examples
Cognitive Technology Team
Cognitive Technology Team
Jun 7, 2025 · Databases

Mastering MySQL Locks: Types, Mechanisms, and Best Practices

This article explains MySQL’s locking mechanisms, categorizing locks by performance, operation type, data granularity, and finer‑grained levels such as gap and next‑key locks, and offers guidance on selecting the appropriate lock strategy for reliable concurrent database operations.

Concurrency ControlDatabase LocksInnoDB
0 likes · 9 min read
Mastering MySQL Locks: Types, Mechanisms, and Best Practices
Pan Zhi's Tech Notes
Pan Zhi's Tech Notes
Feb 20, 2025 · Databases

Mastering Database Concurrency: Dirty Read, Non‑Repeatable Read, and Phantom Read

The article explains ACID properties, details how inadequate isolation levels cause dirty reads, non‑repeatable reads, and phantom reads, illustrates each with transaction scenarios and diagrams, and shows how MySQL’s lock types and isolation settings prevent these anomalies, noting the trade‑off between isolation and concurrency.

databasedirty readlocking
0 likes · 9 min read
Mastering Database Concurrency: Dirty Read, Non‑Repeatable Read, and Phantom Read
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 13, 2024 · Databases

InnoDB Locking Analysis for INSERT … ON DUPLICATE KEY under REPEATABLE‑READ and READ‑COMMITTED

This article examines how InnoDB acquires row‑level locks during INSERT … ON DUPLICATE KEY operations under REPEATABLE‑READ and READ‑COMMITTED isolation levels, explains the lock types on primary and unique indexes, and shows the rollback and lock‑conversion process with concrete SQL examples.

Duplicate KeyInnoDBmysql
0 likes · 11 min read
InnoDB Locking Analysis for INSERT … ON DUPLICATE KEY under REPEATABLE‑READ and READ‑COMMITTED
Top Architect
Top Architect
Oct 12, 2024 · Databases

Understanding MySQL Index Structures, Execution Plans, Transaction Isolation Levels, MVCC, and Buffer Pool Mechanisms

This article provides a comprehensive technical guide on MySQL internals, covering the underlying data structures of indexes (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the four transaction isolation levels, MVCC implementation, and the InnoDB buffer‑pool architecture.

MVCCSQL Optimizationbuffer pool
0 likes · 22 min read
Understanding MySQL Index Structures, Execution Plans, Transaction Isolation Levels, MVCC, and Buffer Pool Mechanisms
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 9, 2024 · Databases

Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels

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.

InnoDBRead CommittedRepeatable Read
0 likes · 9 min read
Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels
Architect
Architect
Sep 19, 2024 · Databases

Phantom Read Verification and Analysis in InnoDB REPEATABLE‑READ Isolation

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.

InnoDBMVCCRepeatable Read
0 likes · 19 min read
Phantom Read Verification and Analysis in InnoDB REPEATABLE‑READ Isolation
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 27, 2024 · Databases

Understanding Repeatable Read in OceanBase Oracle Mode and the Correct JDBC Configuration

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
Understanding Repeatable Read in OceanBase Oracle Mode and the Correct JDBC Configuration
Top Architect
Top Architect
Jul 10, 2024 · Databases

Understanding MySQL Index Structures, Execution Plans, Transactions, and MVCC

This article provides a comprehensive overview of MySQL internals, covering index data structures such as B‑tree and B+‑tree, the differences between MyISAM and InnoDB indexes, SQL execution plan analysis, transaction isolation levels, lock mechanisms, MVCC implementation, and the InnoDB buffer‑pool caching process.

MVCCbuffer poolexecution plan
0 likes · 19 min read
Understanding MySQL Index Structures, Execution Plans, Transactions, and MVCC
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 25, 2024 · Databases

Analyzing a BenchmarkSQL‑Induced Infinite Loop in MySQL under REPEATABLE‑READ Isolation

This article investigates why BenchmarkSQL stalls during MySQL performance testing, tracing the issue to REPEATABLE‑READ isolation causing a dead‑loop in delete‑select transactions, and demonstrates how switching to READ‑COMMITTED resolves the problem through detailed code inspection and experimental verification.

BenchmarkSQLPerformance Testingmysql
0 likes · 8 min read
Analyzing a BenchmarkSQL‑Induced Infinite Loop in MySQL under REPEATABLE‑READ Isolation
Top Architect
Top Architect
May 28, 2024 · Databases

Lessons Learned from Using INSERT INTO SELECT for MySQL Data Migration

This article recounts a real-world MySQL data migration incident where using INSERT INTO SELECT caused out‑of‑memory errors and data loss, analyzes why full table scans and transaction locking led to failures, and offers practical recommendations such as indexing and avoiding bulk inserts for large tables.

Data MigrationDatabase PerformanceFull Table Scan
0 likes · 8 min read
Lessons Learned from Using INSERT INTO SELECT for MySQL Data Migration
Architect
Architect
May 17, 2024 · Databases

Why ‘INSERT INTO SELECT’ Can Crash Your MySQL Migration and How to Fix It

A large‑scale MySQL table migration using INSERT INTO SELECT caused hidden full‑table scans and lock contention, leading to data loss and a costly OOM incident, but adding the proper index and understanding transaction isolation prevented the failure.

Data MigrationDatabase PerformanceINSERT INTO SELECT
0 likes · 8 min read
Why ‘INSERT INTO SELECT’ Can Crash Your MySQL Migration and How to Fix It
Liangxu Linux
Liangxu Linux
May 13, 2024 · Databases

Master MySQL Transaction Isolation: From Dirty Reads to Serializable

This article explains MySQL's transaction concepts, the four isolation levels defined by the SQL standard, how each level prevents dirty reads, non‑repeatable reads, and phantom reads, and provides practical commands and example experiments to configure and observe their behavior in InnoDB.

InnoDBMVCCSQL
0 likes · 19 min read
Master MySQL Transaction Isolation: From Dirty Reads to Serializable
Java Tech Enthusiast
Java Tech Enthusiast
May 9, 2024 · Databases

Risks of Using INSERT INTO SELECT in MySQL: A Postmortem

Using MySQL’s INSERT INTO SELECT on a large, unindexed table caused full‑table locks that timed out concurrent transactions, leading to lost payment rows; the post‑mortem shows that adding appropriate indexes to the WHERE clause and understanding locking behavior prevents such intermittent failures.

INSERT INTO SELECTdatabase migrationmysql
0 likes · 6 min read
Risks of Using INSERT INTO SELECT in MySQL: A Postmortem
Architecture Digest
Architecture Digest
Dec 25, 2023 · Databases

Lessons Learned from Misusing INSERT INTO SELECT in MySQL: OOM, Full Table Scan, and Transaction Issues

This article recounts a real‑world MySQL data‑migration failure caused by an unguarded INSERT INTO SELECT that triggered OOM, full‑table scans, and row‑level locking, explains why testing missed the problem, and offers indexing and transaction‑level safeguards to prevent similar incidents.

Full Table ScanINSERT INTO SELECTdatabase migration
0 likes · 6 min read
Lessons Learned from Misusing INSERT INTO SELECT in MySQL: OOM, Full Table Scan, and Transaction Issues
Wukong Talks Architecture
Wukong Talks Architecture
Dec 24, 2023 · Databases

MySQL Indexes, Lock Types, Transaction Isolation Levels, and Deadlock Analysis

This article explains MySQL index structures, covers covered and back‑table queries, details InnoDB transaction isolation levels, classifies various lock types (table, row, gap, next‑key, intention, auto‑inc, metadata), analyzes lock behavior in multiple scenarios, discusses deadlock causes, and provides DDL and online schema‑change recommendations.

DDLOnline Schema Changedeadlock
0 likes · 61 min read
MySQL Indexes, Lock Types, Transaction Isolation Levels, and Deadlock Analysis
Architect's Guide
Architect's Guide
Dec 8, 2023 · Databases

Why Transaction Isolation Is Needed and Understanding MySQL Isolation Levels

Transaction isolation prevents concurrency issues such as dirty writes, dirty reads, non‑repeatable reads, and phantom reads in MySQL, and the article explains these problems, the ACID properties of transactions, and the four isolation levels—read uncommitted, read committed, repeatable read, and serializable.

ACIDdatabasesmysql
0 likes · 9 min read
Why Transaction Isolation Is Needed and Understanding MySQL Isolation Levels
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Nov 21, 2023 · Databases

Demonstrating MySQL Transaction Isolation Levels with Practical Examples

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.

InnoDBREAD UNCOMMITTEDRead Committed
0 likes · 11 min read
Demonstrating MySQL Transaction Isolation Levels with Practical Examples
ITPUB
ITPUB
Nov 9, 2023 · Databases

Understanding MySQL Locks: When to Use Them, How They Work, and When to Avoid Them

This article explains the fundamentals of MySQL locking—including explicit and implicit locks, transaction isolation levels, next‑key and gap locks, and practical session‑based examples—so developers can decide when to apply pessimistic locks and when to prefer safer alternatives.

Database PerformanceInnoDBLocks
0 likes · 17 min read
Understanding MySQL Locks: When to Use Them, How They Work, and When to Avoid Them
Tencent Cloud Developer
Tencent Cloud Developer
Nov 6, 2023 · Databases

MySQL Lock Mechanisms: When, How, and When Not to Use

The article explains MySQL’s explicit and implicit lock mechanisms—including SELECT … FOR UPDATE, metadata, row, gap, and next‑key locks—how isolation levels affect them, and offers practical guidelines such as using optimistic locking, indexing locked columns, and avoiding long transactions to prevent phantom reads, deadlocks, and performance issues.

Database PerformanceInnoDBSQL
0 likes · 15 min read
MySQL Lock Mechanisms: When, How, and When Not to Use
JD Retail Technology
JD Retail Technology
Sep 10, 2023 · Databases

Understanding MySQL InnoDB MVCC and Transaction Isolation Levels

This article explains MySQL InnoDB's multi-version concurrency control (MVCC), detailing how transaction isolation levels such as read‑committed and repeatable‑read create snapshots, how undo logs and hidden columns form version chains, and walks through concrete examples to show which row values are visible to concurrent transactions.

InnoDBMVCCmysql
0 likes · 12 min read
Understanding MySQL InnoDB MVCC and Transaction Isolation Levels
IT Services Circle
IT Services Circle
Aug 20, 2023 · Databases

Understanding MySQL Transaction Isolation Levels and Their Practical Implications

This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their definitions, associated phenomena such as dirty reads, non‑repeatable reads, and phantom reads, and demonstrates how to query and set isolation levels with practical SQL examples.

Database ConcurrencySQLdirty read
0 likes · 12 min read
Understanding MySQL Transaction Isolation Levels and Their Practical Implications
Sanyou's Java Diary
Sanyou's Java Diary
Aug 7, 2023 · Databases

What Locks Does SELECT FOR UPDATE Use? 20 MySQL Scenarios Revealed

This article experimentally verifies how SELECT FOR UPDATE behaves under MySQL 5.7 and 8.0 with both REPEATABLE‑READ and READ‑COMMITTED isolation levels, covering primary keys, unique indexes, ordinary indexes, no indexes and range queries, and summarizes when row‑level, gap‑level or table‑level locks are taken.

Database PerformanceSELECT FOR UPDATElocking
0 likes · 23 min read
What Locks Does SELECT FOR UPDATE Use? 20 MySQL Scenarios Revealed
ITPUB
ITPUB
Mar 12, 2023 · Databases

Why MySQL Defaults to Repeatable Read – Inside Transaction Isolation

This article explains what a database transaction is, details the ACID properties, compares MySQL's four isolation levels, clarifies why Repeatable Read is the default due to binlog formats and consistency, examines concurrency anomalies of each level, and provides practical guidance on handling large transactions, optimizing performance, and understanding InnoDB's MVCC implementation.

InnoDBMVCCmysql
0 likes · 22 min read
Why MySQL Defaults to Repeatable Read – Inside Transaction Isolation
Sanyou's Java Diary
Sanyou's Java Diary
Jan 12, 2023 · Databases

Master MySQL Transaction Isolation: From READ UNCOMMITTED to SERIALIZABLE

This article explains MySQL's transaction concepts, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their effects on dirty reads, non‑repeatable reads and phantom reads, how they are implemented with locks and MVCC, and provides practical SQL examples and diagrams.

InnoDBMVCCSQL
0 likes · 20 min read
Master MySQL Transaction Isolation: From READ UNCOMMITTED to SERIALIZABLE
dbaplus Community
dbaplus Community
Jan 8, 2023 · Databases

Mastering MySQL Row‑Level Locks: Rules, Types, and How to Inspect Them

This article explains how InnoDB implements row‑level locking in MySQL, detailing the lock types (record, gap, next‑key), the scenarios that trigger each lock, how unique and non‑unique indexes affect locking, and provides step‑by‑step commands and examples for analyzing locks with performance_schema.

Database ConcurrencyInnoDBLock Types
0 likes · 45 min read
Mastering MySQL Row‑Level Locks: Rules, Types, and How to Inspect Them
ITPUB
ITPUB
Dec 23, 2022 · Databases

Understanding Database Transaction Isolation: From ACID Basics to TDSQL Implementation

This article explains the ACID properties of database transactions, clarifies the often‑confused concepts of consistency and isolation, details the four SQL standard isolation levels, compares lock‑based and MVCC concurrency controls, and examines concrete implementations in MySQL, PostgreSQL, and the distributed TDSQL system.

ACIDMVCCPostgreSQL
0 likes · 28 min read
Understanding Database Transaction Isolation: From ACID Basics to TDSQL Implementation
FunTester
FunTester
Oct 28, 2022 · Databases

Understanding MySQL’s Logical Architecture and Transaction Isolation Levels

This article explains MySQL’s three‑tier logical architecture—from client services to core query processing and storage engines—and details the four ANSI SQL transaction isolation levels, highlighting their behavior, advantages, and trade‑offs in MySQL.

Database ArchitectureSQLStorage Engine
0 likes · 7 min read
Understanding MySQL’s Logical Architecture and Transaction Isolation Levels
IT Services Circle
IT Services Circle
Oct 27, 2022 · Databases

Understanding MVCC (Multiversion Concurrency Control) in MySQL InnoDB

MVCC (Multiversion Concurrency Control) in MySQL InnoDB uses undo logs and read views to provide non‑locking snapshot reads, managing read‑write, write‑write, and read‑read concurrency, with implicit row fields and transaction IDs determining visibility across isolation levels.

InnoDBMVCCRead View
0 likes · 9 min read
Understanding MVCC (Multiversion Concurrency Control) in MySQL InnoDB
Top Architect
Top Architect
Oct 13, 2022 · Databases

Understanding Transaction Isolation Levels and Concurrency Issues in MySQL

The article explains why transaction isolation is needed, describes common concurrency problems such as lost updates, dirty reads, non‑repeatable reads and phantom reads, outlines the ACID properties of a transaction, and details MySQL's four isolation levels with their effects and default settings.

ACIDLost Updateconcurrency
0 likes · 8 min read
Understanding Transaction Isolation Levels and Concurrency Issues in MySQL
Practical DevOps Architecture
Practical DevOps Architecture
Sep 21, 2022 · Databases

Understanding MySQL Transaction Isolation Levels: Dirty Read, Non-Repeatable Read, and Phantom Read

This article explains MySQL's four transaction isolation levels, illustrating how concurrent transactions can cause dirty reads, non‑repeatable reads, and phantom reads through detailed examples of two transactions interacting via SELECT, UPDATE, and INSERT operations, and summarizes the consistency issues each scenario creates.

databasesdirty readmysql
0 likes · 4 min read
Understanding MySQL Transaction Isolation Levels: Dirty Read, Non-Repeatable Read, and Phantom Read
JavaEdge
JavaEdge
Aug 28, 2022 · Databases

Why Serializability Matters: Single‑Threaded Transactions and Stored Procedures

The article explains how RC and snapshot isolation fail to prevent write skew and phantom reads, advocates serializable isolation, and examines three implementation techniques—strict serial execution, two‑phase locking, and optimistic concurrency—detailing their trade‑offs, stored‑procedure usage, partitioning, and practical constraints for achieving true serializable isolation.

PartitioningSingle‑threadedStored Procedures
0 likes · 11 min read
Why Serializability Matters: Single‑Threaded Transactions and Stored Procedures
JavaEdge
JavaEdge
Jul 31, 2022 · Databases

Understanding Dirty Reads and Writes: How Transaction Isolation Keeps Your Data Safe

When two transactions access the same data without proper coordination, concurrency bugs such as dirty reads and dirty writes can arise; the article explains these problems, the guarantees of isolation levels, and how row locks and snapshot techniques prevent them in modern databases.

Database Concurrencydirty readdirty write
0 likes · 7 min read
Understanding Dirty Reads and Writes: How Transaction Isolation Keeps Your Data Safe
Architect's Journey
Architect's Journey
Mar 30, 2022 · Databases

How Does MySQL InnoDB Ensure ACID Guarantees?

This article breaks down how MySQL's InnoDB engine guarantees the four ACID properties—atomicity, consistency, isolation, and durability—by leveraging undo and redo logs, the binary log, hidden transaction metadata, and MVCC with ReadView, illustrating each mechanism with concrete examples.

ACIDInnoDBMVCC
0 likes · 9 min read
How Does MySQL InnoDB Ensure ACID Guarantees?
Code Ape Tech Column
Code Ape Tech Column
Dec 29, 2021 · Backend Development

Comprehensive Interview Questions and Answers on Backend Development Topics

This article presents a collection of interview questions covering backend fundamentals such as linked‑list sorting, encryption differences, TCP reliability, IO models, Hystrix, delayed tasks, HTTPS flow, transaction isolation, index pitfalls, virtual memory, Redis leaderboards, distributed locks, zero‑copy techniques, Java synchronized, and Snowflake ID generation.

Distributed LocksHystrixIO models
0 likes · 30 min read
Comprehensive Interview Questions and Answers on Backend Development Topics
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Oct 11, 2021 · Databases

Understanding MVCC and Transaction Isolation Levels in InnoDB

This article explains the fundamentals of MVCC, transaction isolation levels, and related concepts such as ACID properties, dirty reads, non‑repeatable reads, phantom reads, hidden columns, undo logs, version chains, read views, and demonstrates how InnoDB implements these mechanisms with practical SQL examples and diagrams.

InnoDBMVCCRead View
0 likes · 21 min read
Understanding MVCC and Transaction Isolation Levels in InnoDB
Architect's Alchemy Furnace
Architect's Alchemy Furnace
May 9, 2021 · Databases

Master MySQL Transaction Isolation: From Dirty Reads to Phantom Reads with Live Examples

This article explains MySQL InnoDB’s four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—detailing the phenomena of dirty reads, non‑repeatable reads, and phantom reads, and provides step‑by‑step SQL demonstrations to illustrate each level’s behavior and practical implications.

InnoDBmysqltransaction isolation
0 likes · 13 min read
Master MySQL Transaction Isolation: From Dirty Reads to Phantom Reads with Live Examples
Programmer DD
Programmer DD
Apr 22, 2021 · Databases

How MySQL Implements Repeatable Read and Prevents Phantom Reads

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.

MVCCRepeatable Readmysql
0 likes · 11 min read
How MySQL Implements Repeatable Read and Prevents Phantom Reads
Java Interview Crash Guide
Java Interview Crash Guide
Mar 26, 2021 · Databases

Understanding MySQL InnoDB Locks: Types, Isolation, and MVCC Explained

This article explains the concept of locks in computing and databases, compares InnoDB and MyISAM storage engines, details transaction isolation phenomena such as dirty reads, non‑repeatable reads and phantom reads, and explores InnoDB lock types, lock algorithms, intention locks, auto‑increment lock modes, and MVCC mechanisms.

Database LocksInnoDBMVCC
0 likes · 13 min read
Understanding MySQL InnoDB Locks: Types, Isolation, and MVCC Explained
Code Ape Tech Column
Code Ape Tech Column
Mar 3, 2021 · Databases

When INSERT INTO SELECT Breaks MySQL: A Cautionary Data‑Migration Story

An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.

Data MigrationFull Table ScanINSERT INTO SELECT
0 likes · 7 min read
When INSERT INTO SELECT Breaks MySQL: A Cautionary Data‑Migration Story
Su San Talks Tech
Su San Talks Tech
Dec 24, 2020 · Databases

Master MySQL: Deep Dive into Execution Flow, Logs, Indexes, Transactions, and Optimization

This comprehensive guide explains MySQL's server and storage engine layers, SQL execution order, BinLog/RedoLog/UndoLog mechanisms, index structures and design principles, transaction isolation levels, lock types, MVCC, buffer pool behavior, table slimming, join strategies, statistics, random queries, and practical optimization techniques for high‑performance database operations.

Database InternalsSQLindexes
0 likes · 43 min read
Master MySQL: Deep Dive into Execution Flow, Logs, Indexes, Transactions, and Optimization
MaGe Linux Operations
MaGe Linux Operations
Oct 9, 2020 · Databases

How MySQL Implements Transaction Isolation: Locks, MVCC, and Gap Locks Explained

This article explores the underlying mechanisms MySQL uses to enforce transaction isolation levels, detailing how InnoDB leverages locking, multi-version concurrency control, gap locks, and explicit versus implicit locks to prevent dirty reads, non‑repeatable reads, and phantom reads across the four standard isolation levels.

Database ConcurrencyInnoDBMVCC
0 likes · 11 min read
How MySQL Implements Transaction Isolation: Locks, MVCC, and Gap Locks Explained
Laravel Tech Community
Laravel Tech Community
Aug 7, 2020 · Databases

Understanding MySQL Locks, Isolation Levels, and Concurrency Control

This article explains MySQL's implicit and explicit locking mechanisms—including table, row, and intention locks—covers lock modes, MVCC, transaction isolation levels, optimistic and pessimistic locking, gap locks, and deadlock causes and solutions, providing practical SQL examples for each concept.

LocksMVCCconcurrency
0 likes · 12 min read
Understanding MySQL Locks, Isolation Levels, and Concurrency Control
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 23, 2020 · Databases

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

This article explains the concept of half‑consistent reads in MySQL InnoDB, describes the conditions under which they occur, presents two test cases with detailed lock‑waiting behavior, analyzes why certain sessions are blocked or not, and summarizes the optimization benefits for RC isolation level.

Database PerformanceHalf-Consistent ReadInnoDB
0 likes · 20 min read
Understanding Half-Consistent Reads in InnoDB and Their Impact on Transactions
macrozheng
macrozheng
Jun 12, 2020 · Databases

Why Does MySQL Show Negative Balances? Unraveling MVCC and Isolation Levels

This article analyzes a production incident where concurrent MySQL transactions caused an over‑deduction of account balance, explains the underlying MVCC mechanism, consistency view rules, and how different isolation levels (RR vs RC) affect data visibility and lead to negative balances.

InnoDBMVCCconcurrency
0 likes · 12 min read
Why Does MySQL Show Negative Balances? Unraveling MVCC and Isolation Levels
Programmer DD
Programmer DD
Apr 26, 2020 · Databases

Understanding MySQL Table and Row Locks: Mechanisms, Modes, and Types

This article explains the differences between MySQL table locks and row locks, how they are implemented by the server and storage engines, the lock modes and types (including intention, auto‑increment, gap, next‑key, and insert‑intention locks), and provides practical SQL examples with diagrams.

Gap LockInnoDBlocking modes
0 likes · 13 min read
Understanding MySQL Table and Row Locks: Mechanisms, Modes, and Types
Architecture Digest
Architecture Digest
Apr 24, 2020 · Databases

Detailed Explanation of MySQL InnoDB Lock Mechanisms

This article provides a comprehensive overview of MySQL InnoDB locking, covering shared/exclusive locks, intention locks, record locks, gap locks, next‑key locks, insert‑intention locks, and auto‑increment locks, with examples, compatibility tables, and practical SQL snippets to illustrate each mechanism.

Database ConcurrencyInnoDBLocks
0 likes · 12 min read
Detailed Explanation of MySQL InnoDB Lock Mechanisms
Selected Java Interview Questions
Selected Java Interview Questions
Mar 13, 2020 · Databases

Can Repeatable Read Prevent Phantom Reads? A Deep Dive into MySQL Transaction Isolation Levels

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.

MVCCRepeatable Readlocking
0 likes · 14 min read
Can Repeatable Read Prevent Phantom Reads? A Deep Dive into MySQL Transaction Isolation Levels
Efficient Ops
Efficient Ops
May 29, 2019 · Databases

Understanding MySQL Locks: From Table Locks to InnoDB Row Locks

This article explains MySQL's lock mechanisms, comparing MyISAM's table‑level locks with InnoDB's row‑level locks, covering lock types, compatibility, transaction isolation levels, deadlock scenarios, and practical tips to avoid lock contention and improve database concurrency.

Database ConcurrencyInnoDBLocks
0 likes · 22 min read
Understanding MySQL Locks: From Table Locks to InnoDB Row Locks
dbaplus Community
dbaplus Community
Mar 17, 2019 · Databases

Understanding InnoDB Locks: From Record Locks to Deadlocks

This article explains MySQL InnoDB’s transaction locking mechanisms—including shared, exclusive, intention, gap, and next‑key locks—covers lock compatibility, demonstrates lock behavior with practical examples, explores phantom reads, lost updates, optimistic vs. pessimistic locking, and analyzes common deadlock scenarios.

Gap LockInnoDBLocks
0 likes · 28 min read
Understanding InnoDB Locks: From Record Locks to Deadlocks
Java High-Performance Architecture
Java High-Performance Architecture
Jan 12, 2019 · Databases

Why Does Transaction A See 1 While Transaction B Sees 3? Understanding MySQL Repeatable 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.

InnoDBMVCCRepeatable Read
0 likes · 10 min read
Why Does Transaction A See 1 While Transaction B Sees 3? Understanding MySQL Repeatable Read
Java High-Performance Architecture
Java High-Performance Architecture
Jan 3, 2019 · Databases

Understanding Transaction Isolation Levels: Dirty, Non‑Repeatable, and Phantom Reads Explained

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 ConcurrencyRepeatable ReadSQL
0 likes · 6 min read
Understanding Transaction Isolation Levels: Dirty, Non‑Repeatable, and Phantom Reads Explained
Architect's Tech Stack
Architect's Tech Stack
Aug 12, 2018 · Backend Development

Investigating MyBatis SqlSession.clearCache() Ineffectiveness and Transaction Isolation Level Solution

Through detailed debugging and source code analysis, the author discovers that MyBatis's SqlSession.clearCache() does clear the first‑level cache, but the observed stale query results are caused by MySQL's default REPEATABLE‑READ isolation level, which can be resolved by setting the transaction isolation to READ COMMITTED.

CacheDebuggingJava
0 likes · 8 min read
Investigating MyBatis SqlSession.clearCache() Ineffectiveness and Transaction Isolation Level Solution
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Jul 5, 2018 · Databases

Understanding InnoDB Transaction Isolation Levels and Their Impact on Locks and Performance

This article explains the importance of transaction isolation in ACID, describes the four InnoDB isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—covers their locking behavior, effects on replication, and performance trade‑offs, and offers guidance on choosing the appropriate level.

InnoDBLocksSQL
0 likes · 7 min read
Understanding InnoDB Transaction Isolation Levels and Their Impact on Locks and Performance
Ctrip Technology
Ctrip Technology
Mar 14, 2018 · Databases

Understanding MySQL Metadata Locks and InnoDB Lock Mechanisms

This article explains MySQL's architecture, details the various metadata lock types, their acquisition and release processes, lock levels, and relationships, and then examines InnoDB's transaction isolation levels, lock types, lock levels, gap locks, and relevant source‑code structures.

Database LocksInnoDBLock Levels
0 likes · 15 min read
Understanding MySQL Metadata Locks and InnoDB Lock Mechanisms