BestHub
Sign in
Tagged articles
152 articles
Page 2 of 2
Java Backend Technology
Java Backend Technology
Dec 15, 2020 · Databases

Deep Dive into MySQL: Execution Flow, Logs, Locks, MVCC, and Performance Tuning

This comprehensive guide explores MySQL’s internal architecture, covering the SQL execution process, server and storage engine layers, binlog, redo and undo logs, index structures, transaction isolation levels, lock mechanisms, MVCC implementation, buffer pool management, table optimization, query techniques, and advanced performance tuning strategies.

IndexesMVCCOptimization
0 likes · 35 min read
Deep Dive into MySQL: Execution Flow, Logs, Locks, MVCC, and Performance Tuning
Senior Brother's Insights
Senior Brother's Insights
Dec 8, 2020 · Databases

How MySQL Locks DELETE and SELECT: MVCC, Isolation Levels & Lock Types

This article explains how MySQL's InnoDB engine applies row‑level locks for DELETE and SELECT statements under various index configurations and isolation levels, covering MVCC, snapshot vs current reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.

InnoDBIsolation LevelsMVCC
0 likes · 15 min read
How MySQL Locks DELETE and SELECT: MVCC, Isolation Levels & Lock Types
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Nov 10, 2020 · Databases

MySQL Transaction Concepts, Isolation Levels, MVCC, Storage Engines, Indexes, and Optimization Techniques

This article provides a comprehensive overview of MySQL fundamentals, covering transaction properties and isolation levels, MVCC mechanics, differences between InnoDB and MyISAM, query execution flow, redo and binlog, index structures, common pitfalls, and practical optimization and scaling strategies.

Isolation LevelsMVCCOptimization
0 likes · 29 min read
MySQL Transaction Concepts, Isolation Levels, MVCC, Storage Engines, Indexes, and Optimization Techniques
Architecture Digest
Architecture Digest
Oct 30, 2020 · Databases

Understanding Multiversion Concurrency Control (MVCC) in InnoDB

This article explains the concept of Multiversion Concurrency Control (MVCC), how it solves read‑write blocking, deadlocks and consistency issues, and details InnoDB’s implementation—including transaction IDs, hidden columns, undo logs, and the behavior of snapshot and current reads under various isolation levels.

Concurrency ControlDatabaseInnoDB
0 likes · 9 min read
Understanding Multiversion Concurrency Control (MVCC) in InnoDB
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
Selected Java Interview Questions
Selected Java Interview Questions
Sep 27, 2020 · Databases

Understanding How MySQL InnoDB Solves Phantom Reads

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.

InnoDBIsolation LevelMVCC
0 likes · 2 min read
Understanding How MySQL InnoDB Solves Phantom Reads
Selected Java Interview Questions
Selected Java Interview Questions
Sep 16, 2020 · Databases

In‑Depth Analysis of MySQL/InnoDB Locking Mechanisms

This article provides a comprehensive examination of MySQL/InnoDB locking, covering MVCC, snapshot vs. current reads, cluster indexes, two‑phase locking, isolation levels, detailed lock‑behaviour for various index and isolation combinations, complex query lock analysis, and deadlock detection techniques.

DatabaseInnoDBIsolation Levels
0 likes · 23 min read
In‑Depth Analysis of MySQL/InnoDB Locking Mechanisms
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.

DatabaseLocksMVCC
0 likes · 12 min read
Understanding MySQL Locks, Isolation Levels, and Concurrency Control
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.

DatabaseInnoDBMVCC
0 likes · 12 min read
Why Does MySQL Show Negative Balances? Unraveling MVCC and Isolation Levels
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
Java Backend Technology
Java Backend Technology
Feb 26, 2020 · Databases

How Does MySQL Lock Rows for DELETE? A Deep Dive into MVCC and Isolation Levels

This article explains how MySQL's InnoDB engine applies locks for DELETE and SELECT statements, covering MVCC, snapshot and current reads, clustered indexes, left‑most prefix rules, two‑phase locking, isolation levels, gap and next‑key locks, and analyzes nine different index‑and‑isolation scenarios with detailed conclusions.

InnoDBIsolation LevelsMVCC
0 likes · 16 min read
How Does MySQL Lock Rows for DELETE? A Deep Dive into MVCC and Isolation Levels
Architects Research Society
Architects Research Society
Jan 27, 2020 · Databases

CouchDB Final Consistency and Distributed System Design

This article explains CouchDB’s eventual consistency model, its use of MVCC, CAP theorem trade‑offs, incremental replication, and document validation, illustrating how these mechanisms enable scalable, high‑availability distributed databases without locking, and includes a practical case study of syncing Songbird playlists.

CouchDBDistributed SystemsMVCC
0 likes · 15 min read
CouchDB Final Consistency and Distributed System Design
Programmer DD
Programmer DD
Dec 25, 2019 · Databases

How MySQL Locks DELETE Rows: MVCC, Isolation Levels, and Index Types Explained

This article analyzes how MySQL/InnoDB applies row-level locks for DELETE and SELECT statements under various index configurations and isolation levels, explaining MVCC, current vs. snapshot reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.

IndexesInnoDBIsolation Levels
0 likes · 15 min read
How MySQL Locks DELETE Rows: MVCC, Isolation Levels, and Index Types Explained
Programmer DD
Programmer DD
Oct 26, 2019 · Databases

How JDTX Redefines Distributed Transactions with ACID Guarantees

This article introduces JDTX, a novel distributed transaction middleware that combines ACID guarantees, high performance, and 1PC architecture, explains its design principles, compares two‑phase commit with flexible transactions, details its MVCC and WAL implementation, and outlines integration with Apache ShardingSphere and future roadmap.

DatabaseJDTXMVCC
0 likes · 19 min read
How JDTX Redefines Distributed Transactions with ACID Guarantees
ITPUB
ITPUB
Oct 24, 2019 · Databases

How JDTX Achieves High‑Performance Distributed Transactions with MVCC and WAL

This article explains the design and implementation of JDTX, a distributed transaction middleware that combines MVCC, WAL, and a one‑phase commit model to provide strong ACID guarantees, high performance, and multi‑database support while outlining its challenges and future roadmap.

Database MiddlewareJDTXMVCC
0 likes · 17 min read
How JDTX Achieves High‑Performance Distributed Transactions with MVCC and WAL
JD Tech Talk
JD Tech Talk
Oct 24, 2019 · Databases

JDTX Distributed Transaction Solution: Design Principles, Features, and Implementation Challenges

This article introduces JDTX, a high‑performance, ACID‑compliant distributed transaction middleware, explains its architecture based on WAL and MVCC, compares rigid two‑phase commit and flexible BASE‑based approaches, and discusses the technical difficulties and future roadmap for integrating JDTX with Apache ShardingSphere and cloud‑native platforms.

ACIDDatabase MiddlewareJDTX
0 likes · 15 min read
JDTX Distributed Transaction Solution: Design Principles, Features, and Implementation Challenges
ITPUB
ITPUB
Oct 23, 2019 · Databases

Master MySQL Locks and Transactions: A Deep Dive into Concurrency Control

This article systematically explains MySQL's logical server architecture, the various lock types (shared, exclusive, intention, record, gap, next‑key, insert‑intention, AUTO‑INC), transaction fundamentals, isolation levels, MVCC mechanics, and practical deadlock detection and avoidance techniques, complete with real‑world SQL examples.

Isolation LevelsLocksMVCC
0 likes · 23 min read
Master MySQL Locks and Transactions: A Deep Dive into Concurrency Control
AntTech
AntTech
Oct 7, 2019 · Databases

OceanBase TPC‑C Benchmark: Transaction Engine Design and ACID Guarantees

OceanBase’s TPC‑C benchmark demonstrates how its distributed database achieves high durability, atomicity, isolation, and consistency on commodity cloud servers using Paxos‑based log replication, two‑phase commit, MVCC, and replicated tables, highlighting the architectural challenges and solutions for ACID compliance.

ACIDMVCCOceanBase
0 likes · 10 min read
OceanBase TPC‑C Benchmark: Transaction Engine Design and ACID Guarantees
JD Retail Technology
JD Retail Technology
Jul 3, 2019 · Databases

CB‑SQL: JD Retail’s Elastic Distributed Database Built on CockroachDB – Architecture and Implementation Details

The article introduces CB‑SQL, JD Retail’s next‑generation elastic database based on CockroachDB, and comprehensively explains its overview, layered architecture, storage replication, cluster management, MVCC, hybrid logical clocks, lock‑free distributed transactions, CDC changefeeds, and SQL interface support.

CDCCockroachDBMVCC
0 likes · 14 min read
CB‑SQL: JD Retail’s Elastic Distributed Database Built on CockroachDB – Architecture and Implementation Details
Youzan Coder
Youzan Coder
May 15, 2019 · Databases

Why a Simple SELECT LIMIT 1 Can Be Terribly Slow in MySQL InnoDB

A detailed investigation reveals that a seemingly trivial SELECT * FROM sbtest1 LIMIT 1 becomes slow due to InnoDB's MVCC visibility checks, a growing History List length, and a large number of deleted rows that must be scanned before a visible row is found.

InnoDBMVCCPerformance
0 likes · 12 min read
Why a Simple SELECT LIMIT 1 Can Be Terribly Slow in MySQL InnoDB
ITFLY8 Architecture Home
ITFLY8 Architecture Home
Mar 29, 2019 · Backend Development

How MVCC Beats Pessimistic Locks for Distributed Key‑Value Stores

This article examines a distributed system concurrency problem, compares lock‑based transactions with multiversion concurrency control (MVCC), and explains why MVCC often outperforms pessimistic locking in scenarios demanding high read responsiveness and low contention.

Concurrency ControlDistributed SystemsMVCC
0 likes · 10 min read
How MVCC Beats Pessimistic Locks for Distributed Key‑Value Stores
vivo Internet Technology
vivo Internet Technology
Mar 2, 2019 · Databases

MySQL Locks, Transactions, and Concurrency Control Overview

The article systematically explains MySQL’s locking mechanisms—including shared, exclusive, intention, gap, next‑key, insert‑intention, and auto‑increment locks—alongside transaction handling, isolation levels, MVCC‑based concurrency control, and deadlock detection and avoidance strategies, helping readers understand data consistency and performance.

Concurrency ControlIsolation LevelsLocks
0 likes · 23 min read
MySQL Locks, Transactions, and Concurrency Control Overview
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
Tencent Cloud Developer
Tencent Cloud Developer
Dec 3, 2018 · Databases

Architecture and Design of Tencent Cloud CynosDB and CynosFS

Tencent Cloud’s CynosDB uses a shared‑storage, primary‑multiple‑replica architecture built on the user‑space distributed file system CynosFS, separating compute from elastic block storage, sinking write‑ahead logs for asynchronous replay, and employing MVCC with Raft‑replicated segments to achieve high‑availability, scalable, and cost‑effective MySQL/PostgreSQL‑compatible performance.

CynosDBDatabase ArchitectureLog Sinking
0 likes · 17 min read
Architecture and Design of Tencent Cloud CynosDB and CynosFS
Tencent Cloud Developer
Tencent Cloud Developer
Sep 11, 2018 · Databases

CynosDB Architecture and Core Mechanisms: A Comprehensive Technical Overview

CynosDB is Tencent Cloud’s high‑performance, highly‑available NewSQL distributed database that uses a shared‑storage architecture with primary and replica instances, log‑structured storage, RDMA/SPDK transmission, Raft consensus, SSI MVCC, two‑phase locking, and state‑machine replication to provide elastic scaling, fast recovery, and cost‑effective enterprise data management.

CynosDBMVCCNewSQL
0 likes · 17 min read
CynosDB Architecture and Core Mechanisms: A Comprehensive Technical Overview
ITPUB
ITPUB
Jul 3, 2018 · Databases

How T‑TDSQL Implements Dual‑Temporal Data Management in Distributed Environments

This article details the architecture and implementation techniques of T‑TDSQL’s dual‑temporal database system, covering full‑state data reading, history‑table creation, snapshot query syntax, transaction snapshot attributes, visibility algorithms, transaction‑state management, index design, and valid‑time creation, query, update, and delete operations.

MVCCSQLTDSQL
0 likes · 17 min read
How T‑TDSQL Implements Dual‑Temporal Data Management in Distributed Environments
ITPUB
ITPUB
Jun 11, 2018 · Databases

How T‑TDSQL Implements a Full‑Temporal Data Model in MySQL

This article explains the design of T‑TDSQL's full‑temporal data model, detailing its state and time attributes, storage strategies for historical data in MVCC databases, various storage formats and modes, and a novel algorithm for historical state visibility judgment.

MVCCfull-temporalhistorical storage
0 likes · 13 min read
How T‑TDSQL Implements a Full‑Temporal Data Model in MySQL
ITPUB
ITPUB
Jun 5, 2018 · Databases

How T‑TDSQL Enables Full‑Temporal Data Management in Distributed Databases

T‑TDSQL, a temporal extension of Tencent's TDSQL, introduces a full‑temporal data model, scalable distributed architecture, MVCC‑based history visibility algorithms, and rich temporal features that address financial audit, data lineage, and high‑performance analytics for massive historical datasets.

MVCCTDSQLTemporal Database
0 likes · 15 min read
How T‑TDSQL Enables Full‑Temporal Data Management in Distributed Databases
Beike Product & Technology
Beike Product & Technology
Jun 1, 2018 · Databases

InnoDB Transaction and Index Principles

This article explains InnoDB's transaction and index principles, covering ACID properties, transaction logs, MVCC, locking mechanisms, and B+ tree index structures to help backend developers understand how InnoDB ensures data consistency and high concurrency.

ACIDB+TreeIndex Optimization
0 likes · 11 min read
InnoDB Transaction and Index Principles
Hujiang Technology
Hujiang Technology
May 8, 2018 · Databases

Understanding Consistent Reads and MVCC in MySQL InnoDB

This article explains how MySQL InnoDB implements consistent reads using multi‑version concurrency control (MVCC), detailing isolation levels, undo‑log mechanics, hidden columns, rollback segments, transaction commit behavior, and the impact on SELECT, INSERT, UPDATE, and DELETE operations.

Consistent ReadDatabaseInnoDB
0 likes · 14 min read
Understanding Consistent Reads and MVCC in MySQL InnoDB
ITPUB
ITPUB
Mar 11, 2017 · Databases

How Does MySQL InnoDB Lock Rows? Deep Dive into MVCC, 2PL, and Deadlocks

This article explains MySQL InnoDB's locking mechanisms—including MVCC snapshot vs. current reads, two‑phase locking, isolation levels, lock types, GAP locks, and deadlock scenarios—by analyzing simple and complex SQL examples and showing how different index and isolation configurations affect lock acquisition.

InnoDBMVCCdeadlock
0 likes · 25 min read
How Does MySQL InnoDB Lock Rows? Deep Dive into MVCC, 2PL, and Deadlocks
ITPUB
ITPUB
Aug 31, 2016 · Databases

Lock‑Free Transactions and Cache Switching: MVCC Techniques for Key‑Value Stores

This article explains how traditional ACID transactions fall short for modern large‑scale systems and presents lock‑free transaction methods, atomic and partial cache‑switch techniques, and a PostgreSQL‑style MVCC model for key‑value databases, including practical rules and trade‑offs.

CacheDatabaseKey-Value
0 likes · 10 min read
Lock‑Free Transactions and Cache Switching: MVCC Techniques for Key‑Value Stores
Efficient Ops
Efficient Ops
Aug 1, 2016 · Databases

Why Uber’s MySQL Switch Misrepresents PostgreSQL: A Deep Technical Dive

This article critically examines Uber’s claim that MySQL outperforms PostgreSQL by dissecting the alleged PostgreSQL limitations, presenting detailed performance tests, and clarifying the true costs of write amplification, replication, and upgrade processes for both databases.

Database PerformanceMVCCPostgreSQL
0 likes · 20 min read
Why Uber’s MySQL Switch Misrepresents PostgreSQL: A Deep Technical Dive
ITPUB
ITPUB
Jul 25, 2016 · Databases

How BRIN Indexes Use allnulls and hasnulls to Slash PostgreSQL Scans

This article demonstrates how PostgreSQL 9.5's BRIN index leverages the allnulls and hasnulls flags in its regular pages to filter out unnecessary heap page scans, dramatically improving query performance while also discussing the impact of MVCC bloat and maintenance.

BRINMVCCNull Handling
0 likes · 10 min read
How BRIN Indexes Use allnulls and hasnulls to Slash PostgreSQL Scans
dbaplus Community
dbaplus Community
May 20, 2016 · Databases

Inside TiKV: MVCC Mechanics and Distributed Transaction Design

This article explains how TiKV implements multi-version concurrency control (MVCC) on top of RocksDB and details its two‑phase commit transaction model, including Prewrite and Commit phases, Percolator‑style optimizations, lock handling, conflict resolution, and garbage‑collection strategies.

Garbage CollectionMVCCPercolator
0 likes · 14 min read
Inside TiKV: MVCC Mechanics and Distributed Transaction Design
21CTO
21CTO
Apr 16, 2016 · Databases

How MySQL InnoDB Locks Work: From MVCC Basics to Deadlock Analysis

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.

Database ConcurrencyInnoDBIsolation Level
0 likes · 26 min read
How MySQL InnoDB Locks Work: From MVCC Basics to Deadlock Analysis
21CTO
21CTO
Aug 18, 2015 · Databases

Understanding PostgreSQL MVCC: How It Handles Concurrency

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to ensure reads never block writes and vice versa, assigning transaction IDs to rows, managing visibility with xmin/xmax, supporting isolation levels, and requiring periodic VACUUM to clean dead rows and handle XID wraparound.

MVCCPostgreSQLVacuum
0 likes · 7 min read
Understanding PostgreSQL MVCC: How It Handles Concurrency