This article examines why concurrent upsert operations in a high‑throughput MySQL service can cause deadlocks due to GAP locks and phantom reads, explains the underlying InnoDB locking mechanisms, and offers practical solutions such as adjusting isolation levels or redesigning data sharding logic.
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.
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.
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.
This article explains the four MySQL transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—by creating a sample table and running step‑by‑step command‑line sessions that demonstrate dirty reads, non‑repeatable reads, phantom reads, and the practical limits of InnoDB's MVCC implementation.
This article explains transaction isolation levels, dirty reads, and phantom reads, compares how major databases such as SQL Server, PostgreSQL, MySQL, Oracle, DB2, MongoDB, CouchDB, Couchbase, and Cassandra implement these concepts, and highlights the performance‑security trade‑offs of each isolation level.
An in‑depth guide explains MySQL’s four transaction isolation levels, how to modify them globally or per session, the role of autocommit, shared and exclusive lock mechanisms, and provides step‑by‑step InnoDB examples with screenshots illustrating each level’s behavior.
This article explains the ACID fundamentals, common concurrency anomalies such as dirty, non‑repeatable and phantom reads, the four SQL‑standard isolation levels, how MySQL implements them, and step‑by‑step command‑line tests demonstrating their effects on real transactions.
This guide explains MySQL's four transaction isolation levels, how to change them globally or per session, the impact of autocommit, the different lock types, and provides step‑by‑step InnoDB examples that illustrate the behavior of READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ, and SERIALIZABLE.
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.
This article explains the concurrency problems that arise in databases, describes the five types of read/write anomalies, details the three‑level lock protocol and the two‑phase locking rule, and compares the four standard isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—highlighting their effects on performance and data consistency.
