After publishing 50 installments of the 'MySQL Core Modules' series covering transactions, locks, and undo, the author expresses gratitude to readers and the community, outlines upcoming articles on Undo, MVCC, and Redo, and reviews past content while planning a compiled book.
This article reviews Java Spring code for a coupon claim and approval workflow, identifies concurrency and transaction issues such as missing locks and inconsistent update order, and proposes optimizations including user‑level locking, simplifying lock management, and aligning transactional update sequences to prevent deadlocks.
This article explains MySQL's storage engine architecture, compares built‑in engines such as InnoDB and MyISAM, details their file system interactions, table‑space types, and transaction features—including ACID properties, isolation levels, locking mechanisms, and practical migration steps for production environments.
This article provides a comprehensive analysis of MySQL’s internal architecture, detailing the four-layer design, the server service layer’s parsing, optimization and execution components, and the critical roles of Undo Log, Redo Log, and Binlog in ensuring ACID transaction properties.
This guide compiles the most common MySQL interview questions asked by leading Chinese tech firms, covering fundamentals, data types, storage engines, indexes, transactions, locks, views, stored procedures, triggers, and optimization, with clear explanations, diagrams, and a free downloadable resource.
This article explains Spring's transaction management, covering ACID principles, transaction propagation types, isolation levels, timeout settings, read‑only flags, rollback behavior, and the effect of asynchronous execution, supplemented with practical code examples and common pitfalls.
This article explains the internal process MySQL 8.0.32 InnoDB uses to allocate, create, and manage Undo segments—including cached segment retrieval, new segment creation, insertion into rollback segment lists, conflict avoidance through mutexes, and a concise step‑by‑step summary of the entire workflow.
This comprehensive guide demystifies the entire payment ecosystem—covering the nature of payment, transaction flow, clearing and settlement models, various payment modes, routing, accounting, and risk management—using 88 illustrations and 66 essential knowledge points for a quick reference handbook.
This article provides a comprehensive overview of MySQL's layered architecture, the functions and core components of the server layer, and detailed explanations of Undo Log, Redo Log, and Binlog mechanisms that together ensure the ACID properties of transactions, including practical execution flows for queries and updates.
This article explains how to leverage Spring's TransactionSynchronizationManager to detect active transactions, register synchronization callbacks, and asynchronously send Kafka messages only after a transaction successfully commits, illustrated with a payment‑system example and complete Java code snippets, while also noting thread‑local considerations and promotional offers.
This article explains how to leverage Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks so that Kafka messages are sent asynchronously only after a transaction successfully commits, ensuring data consistency without impacting the main business flow.
This article examines how InnoDB acquires various row, gap, and next‑key locks during INSERT … ON DUPLICATE KEY UPDATE statements under REPEATABLE‑READ and READ‑COMMITTED isolation levels, illustrating the lock behavior with detailed SQL examples and performance‑schema queries.
This article explains why Spring transactions may fail, demonstrates simple examples with and without the @Transactional annotation, details the annotation’s definition and attributes, and provides a step‑by‑step source‑code analysis of transaction creation, commit, rollback, and cleanup in Spring 4.3.12.
The article explains how to leverage Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks that asynchronously send Kafka messages after a transaction commits, ensuring reliable fund‑flow archiving in payment systems while avoiding thread‑switch issues.
This article explains how to use Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks that asynchronously send Kafka messages only after a transaction commits, illustrated with a payment‑system case and complete Java code examples.
This article investigates how many rows should be inserted per batch in MySQL, analyzes the limits of SQL statement size, buffer pool, transaction overhead, and indexes, and presents empirical tests that reveal the most efficient batch size for different data volumes.
This article explains how MySQL writes data to cache before flushing to disk, compares single‑row versus batch inserts, discusses how hardware limits, transaction size, and lock contention affect performance, and shows how to estimate an optimal batch size with concrete calculations and MyBatis examples.
This article explains how to leverage Spring's TransactionSynchronizationManager to detect active transactions, register synchronization callbacks, and asynchronously send Kafka messages only after a transaction successfully commits, providing a practical example and detailed code snippets for building a reusable Spring Boot starter library.
This article explains how to use Spring's TransactionSynchronizationManager to detect active transactions, register synchronization callbacks, and asynchronously publish payment‑log messages to Kafka without affecting the main business flow, complete with practical code snippets and pitfalls to avoid.
This article dissects the exact moment a Spring @Transactional method commits relative to a surrounding lock, explains why committing after unlock can cause overselling, and provides source‑level debugging techniques to verify transaction start, commit, and rollback behavior in MySQL under high concurrency.
This article explains what MySQL deadlocks are, outlines the four necessary conditions that cause them, provides a concrete example with SQL code, and offers practical strategies such as lock ordering, timeout settings, and MySQL’s built‑in detection to prevent and resolve deadlocks.
This article explains the most common reasons why Spring @Transactional annotations may become ineffective or fail to roll back, covering access‑modifier issues, final methods, internal calls, self‑injection, multithreading, unsupported table engines, mis‑configured propagation, exception handling, nested transactions, large‑transaction problems, and the advantages of programmatic transaction management.
This article demonstrates how to leverage Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks that asynchronously publish payment ledger messages to Kafka after transaction commit, while providing a starter-style library for easy integration.
This article explains how to correctly manage transactions across multiple threads in a Spring application by using manual SqlSession control, providing utility methods for list partitioning, thread‑pool configuration, and example code that demonstrates both failing and successful transaction scenarios.
This article examines how InnoDB handles row locking when an INSERT triggers a primary‑key index conflict, detailing the lock types on primary and unique indexes, the implicit‑to‑explicit lock conversion, and the role of the supremum record in the locking process.
This article explains why Spring transactions can become ineffective or fail to roll back, covering issues such as wrong method visibility, final methods, internal calls, missing Spring bean registration, multithreading, unsupported table engines, misconfigured propagation, exception handling, and offers practical solutions for each case.
This article examines how InnoDB acquires shared record locks during a duplicate‑primary‑key INSERT, explains the underlying lock‑checking mechanism, demonstrates lock behavior with concurrent DELETE and INSERT transactions, and provides SQL examples and performance_schema queries to illustrate the process.
This article explains how Kafka implements transactions using a two‑phase commit protocol, compares its approach with RocketMQ, details the exactly‑once semantics for streaming workloads, and walks through the coordinator roles, message flow, and practical use‑case scenarios.
This article objectively compares NewSQL distributed databases with traditional middleware‑based sharding solutions, examining their architectural differences, transaction support, scalability, HA, storage engines, and ecosystem maturity, to help readers decide which approach best fits their performance, consistency, and operational needs.
This article explains how to achieve idempotent order creation and update by using unique order IDs, database primary‑key constraints, Redis flags, and a version‑column mechanism to detect and prevent duplicate submissions and ABA anomalies in distributed systems.
This article outlines the business background, detailed payment workflow, associated product and coupon management, and practical implementation tips for designing a robust payment system, covering process decomposition, sequence diagrams, data structures, transaction handling, and key technical considerations such as transaction management, locking, and settlement accuracy.
This article explains how InnoDB resolves a deadlock by ordering the involved transactions, selecting a victim based on priority, rollback cost and table modifications, updating transaction weights, optionally logging the deadlock details, and finally waking the victim transaction to roll back.
This article analyzes the complexities of payment system design, covering business background, modular process breakdown, sequence diagrams for pre‑payment, payment integration, post‑payment stages, and detailed structural design, while also highlighting related business components and practical implementation tips.
This article explores the subtle timing of Spring transaction start and commit relative to lock/unlock, demonstrates how improper ordering can cause over‑selling in high‑concurrency scenarios, and provides debugging techniques and practical solutions to ensure atomicity and correct rollback behavior.
This article objectively compares NewSQL distributed databases with traditional middleware‑based sharding solutions, examining their architectures, transaction models, scalability, high‑availability mechanisms, storage engines, maturity, and offering a decision framework to help engineers choose the most suitable approach for their workloads.
This article explains how InnoDB simulates deadlocks, how the ib_srv_lock_to background thread checks for deadlocks, how it builds lock‑wait snapshots, constructs lock‑wait graphs, calculates transaction weights, and finally resolves deadlocks, illustrated with detailed SQL examples and code snippets.
The article outlines the challenges of payment systems, detailing business background, process decomposition, flow sequencing, structural design, related product and coupon management, and summarizes key technical considerations such as transaction management, locking, retry mechanisms, and fund settlement for robust backend development.
This article demonstrates how to leverage Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks that asynchronously push payment‑ledger messages to Kafka only after the transaction successfully commits, ensuring data consistency and minimal impact on the main business flow.
This article explains how InnoDB handles lock waiting by allocating lock structures, registering waiting slots, recording start times, and notifying transactions, outlining each step of the queue‑registration‑notification workflow in MySQL 8.0.32.
This article examines the trade‑offs of sending messages before, during, or after database persistence in order‑creation workflows, explores transaction‑message patterns, half‑message checks, and message‑table strategies, and offers practical guidance for building reliable backend messaging systems.
This article explains how to leverage Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks so that Kafka messages are sent asynchronously only after a transaction successfully commits, illustrated with a Spring Boot starter example.
This article explains how to use Spring's TransactionSynchronizationManager to detect active transactions and register callbacks that asynchronously send Kafka messages after a transaction successfully commits, ensuring data consistency in payment‑system archiving scenarios.
The article explains how to analyze, decompose, and design a complex payment workflow by breaking it into modules, defining pre‑payment, payment‑gateway, and post‑payment sequences, and outlining the essential data structures such as account management, transaction records, and order handling, while also highlighting practical implementation considerations.
This article objectively compares NewSQL databases with middleware‑based sharding solutions, examining their architectural differences, transaction handling, scalability, high‑availability mechanisms, storage engines, and maturity to help readers decide which approach best fits their specific workload and business requirements.
This article explains the timing of Spring's @Transactional commit relative to lock release, analyzes why committing after unlock can cause overselling, shows how to trace the transaction lifecycle in the source code, and offers practical solutions to ensure atomicity in high‑concurrency scenarios.
The article explains how to respond to accidental MySQL data deletion by analyzing loss causes, choosing recovery methods such as mysqldump backups, binary‑log point‑in‑time restores, or Percona’s InnoDB tool, provides step‑by‑step commands, and recommends preventive practices like regular backups, transactions, and strict permissions.
This article explains how to use Spring's TransactionSynchronizationManager to detect active transactions and register callbacks that asynchronously send Kafka messages only after a transaction successfully commits, illustrated with a payment‑system case study and complete code examples.
The article objectively compares NewSQL distributed databases with traditional middleware‑based sharding solutions, examining their architectural differences, distributed transaction support, performance, scalability, high‑availability mechanisms, storage engines, and practical suitability for various application scenarios.
This article explains how to use Spring's TransactionSynchronizationManager to detect active transactions and register synchronization callbacks so that Kafka messages are sent asynchronously after transaction commit, illustrated with a payment‑system logging scenario and complete Java code examples.
This article explains what SQL Server deadlocks are, why they occur, demonstrates a reproducible deadlock scenario with T‑SQL code, and provides practical strategies such as proper schema design, indexing, query optimization, shorter transactions, appropriate isolation levels, and monitoring to minimize deadlock occurrences.
This article explains how to dynamically manage multiple data sources and ensure transaction consistency across a master and several application databases in Spring by extending AbstractRoutingDataSource, using configuration‑file and database‑table solutions, and implementing a custom multi‑database transaction manager.
This article breaks down the essential "kernels" of payment, transaction, settlement, accounting, and related financial processes, illustrating each layer with diagrams and explanations that reveal how businesses, platforms, and institutions collaborate to move money securely and efficiently.
This comprehensive guide explains why MySQL needs InnoDB locking, details the seven lock types, shows how a single SQL statement acquires locks under different isolation levels, demonstrates how to view lock information, and walks through practical deadlock analysis examples.
This article provides a comprehensive overview of essential MySQL interview topics, covering join types, differences among DROP, DELETE, TRUNCATE, UNION vs UNION ALL, CHAR vs VARCHAR, transaction properties and isolation levels, database normalization, index types and usage, query execution flow, storage engine differences, and practical SQL optimization techniques.
This article explains MySQL 8.0.32 InnoDB's two‑phase commit process, detailing the flush, sync, and commit sub‑stages, the concept of group commit, and how queues and mutexes coordinate transaction logging to reduce redundant disk writes.
The article explains how to execute parallel tasks with CompletableFuture, why @Async cannot guarantee transactional integrity in a multi‑threaded Spring environment, and presents a programmatic transaction approach—including copying transaction resources between threads—to achieve consistent commit or rollback across concurrent operations.
This article explains how MySQL 8.0 implements the two‑phase commit protocol, detailing when it is used, the responsibilities of the prepare stage for both the binlog and InnoDB storage engines, and the five key actions InnoDB performs during preparation.
This article explains the seven Spring transaction propagation levels, shows how nested @Transactional methods interact under each level, and provides concrete code‑level examples that illustrate when transactions are merged, suspended, started anew, or rolled back, helping developers choose the right strategy for reliable enterprise applications.
This article explains Spring's seven transaction propagation behaviors—REQUIRED, SUPPORTS, MANDATORY, REQUIRES_NEW, NOT_SUPPORTED, NEVER, and NESTED—detailing how nested transactional methods interact, when new transactions are created or suspended, and how exceptions influence commit and rollback across different propagation settings.
This article explains why the @Transactional annotation fails in Spring when using multithreading, demonstrates a failing example, and provides a solution using manual SqlSession management to guarantee that all database changes are rolled back if any thread encounters an exception.
This tutorial explains how Redis optimistic locking works, demonstrates the WATCH, MULTI, EXEC, and DISCARD commands with live examples, and shows how to integrate the mechanism into a Spring Boot application using retry logic to handle concurrent updates safely.
This article explains how to guarantee transactional integrity when importing a 100,000‑row Excel file with asynchronous threads in Spring, covering double‑async optimization, Future and CompletableFuture handling, @Transactional pitfalls, manual transaction control, and a complete solution for cross‑thread transaction management.
This article walks through building a functional Redis server in Go, focusing on transaction support (MULTI, EXEC, WATCH, UNWATCH, DISCARD), using locks for atomicity, version tracking for watch, and an undo‑log mechanism for rollback, with code examples and demo screenshots.
This article explains Spring's seven transaction propagation modes, how they affect nested @Transactional method calls, and the resulting commit or rollback behavior in various scenarios, providing clear examples and diagrams for each propagation type.
This tutorial explains how to use PHP and PDO to quickly insert ten million rows into a remote MySQL database by preparing the connection, employing batch inserts, managing transactions, tuning MySQL settings, and optionally using the LOAD DATA statement for optimal performance.
Spring’s transaction propagation defines seven behaviors—REQUIRED, SUPPORTS, MANDATORY, REQUIRES_NEW, NOT_SUPPORTED, NEVER, and NESTED—detailing how methods join, create, suspend, or nest transactions, and the article explains each type, practical scenarios, and how proper selection ensures atomicity, consistency, and correct rollback handling.
This article explains how to execute two dependent tasks in parallel, guarantee their successful completion before a final step, and maintain transaction consistency across multiple threads in Spring by using CompletableFuture, programmatic transaction management, and a custom transaction‑resource copying mechanism.
This article explains the complete execution process of a MySQL UPDATE statement, covering InnoDB page structures, tablespaces, buffer pool caching, redo/undo logs, binlog, and the two‑phase commit mechanism, helping readers understand how MySQL ensures data consistency and durability.
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.
This article introduces the open‑source distributed database MatrixOne, tracing its early NewSQL/MPP architecture, the challenges that prompted a major redesign, and the resulting HTAP‑oriented three‑layer architecture with decoupled compute, transaction, and storage layers, highlighting performance, cost, and scalability improvements.
This tutorial walks through creating a lightweight Go ORM—covering a custom logger, converting structs to table metadata, implementing basic CRUD operations, handling transactions, and adding hook callbacks—while exposing the core Session data structure and providing a GitHub reference.
This article explains the various lock types in MySQL—including global, table, row, metadata, and intent locks—how they work under InnoDB, demonstrates lock acquisition with SQL examples, and provides practical guidance for detecting and resolving deadlocks in production environments.
The article explains why large database transactions degrade backend API performance, outlines issues such as data inconsistency, lock contention, and log overhead, and presents practical optimization techniques including avoiding RPC inside transactions, using programmatic transactions, batch processing, splitting into smaller transactions, and asynchronous parallel execution with CompletableFuture.
This article explains MySQL's explicit and implicit locking mechanisms, illustrates how transaction isolation levels affect lock scope, provides practical SQL examples for various scenarios, and offers guidelines on when to apply pessimistic locks versus optimistic approaches to maintain database performance.
This article examines seven typical messaging scenarios—ordinary, ordered, delayed, transactional, trace, dead‑letter, and priority messages—explaining the problem each solves, the trade‑offs, and concrete implementations in RocketMQ, Pulsar, RabbitMQ and Kafka with code samples.
This article explains the differences between Read Committed (RC) and Repeatable Read (RR) isolation levels in MySQL, detailing how snapshot reads work, why other levels avoid them, and illustrating their behavior with concrete transaction examples.
The article explains how bundling many business‑logic steps into a single large transaction harms API performance, outlines the resulting concurrency, locking, undo‑log, and DB‑pressure issues, and then presents practical techniques—programmatic transactions, batch processing, transaction splitting, and asynchronous parallelism—to optimize and speed up backend services.
This article explains the principle of deadlocks, the four necessary conditions, how they manifest in SQL Server resources, methods for investigating deadlocks with system procedures and Profiler, and practical techniques—including isolation level changes, lock timeouts, and bound sessions—to avoid and resolve them.
This article introduces a generic asynchronous processing SDK for Java Spring applications, covering its purpose, advantages, underlying principles, component choices such as Kafka, XXL‑Job, MySQL, design patterns, database schema, configuration via Apollo, usage instructions, safety considerations, and provides code examples and a GitHub repository link.
This article demonstrates why @Transactional fails in multithreaded Spring services, presents a thread‑pool based solution using manual SqlSession control to achieve full rollback across child threads, and provides complete code examples and test results illustrating both failure and successful transaction handling.
This article examines MySQL's various lock types—including shared, exclusive, gap, next‑key, and insert‑intention locks—illustrates how they interact during concurrent transactions, analyzes a real‑world deadlock incident, and provides recommendations for preventing similar concurrency issues in high‑traffic systems.
This article explains how to enable and configure Spring Boot's @Async annotation, demonstrates proper thread‑pool setup, highlights common mistakes such as calling async methods within the same class or mixing with @Transactional, and provides guidance on handling blocking tasks and exceptions for robust asynchronous execution.
This article explains what a MySQL deadlock is, shows a concrete example using two conflicting transactions on a simple test table, demonstrates how to detect the deadlock with InnoDB status output, and provides practical tips for preventing deadlocks in production systems.
This article explains how MySQL's SELECT ... FOR UPDATE statement applies different lock types—row, table, or none—depending on whether the WHERE clause uses primary keys, unique indexes, regular indexes, range queries, non‑indexed fields, or returns no rows, illustrated with practical transaction examples.
The article analyzes how to improve inventory‑update transactions during massive sales periods by comparing two SQL transaction patterns, calculating their TPS under defined latency assumptions, and suggesting that placing the row lock closer to the commit yields significantly higher throughput.
This article explains MySQL deadlocks, describing common scenarios such as multi‑table updates, nested transactions, inconsistent index order, and concurrent index updates, provides a concrete banking transfer example, and outlines practical steps for monitoring, terminating, retrying, analyzing, and preventing deadlocks in production.
This guide explains MySQL's lock mechanisms—including shared and exclusive locks, row‑level locking requirements, pessimistic versus optimistic strategies, and practical implementations with InnoDB, transaction handling, and JMeter concurrency testing—to prevent overselling in high‑traffic scenarios.
This article walks through how a Java application interacts with MySQL, covering the driver, connection pooling, thread handling, query parsing, the optimizer, storage engine, buffer pool, undo/redo logs, binlog, and transaction commit, providing a complete end‑to‑end view of MySQL’s internal processing.
This article explains the fundamentals of database transactions, introduces Spring's @Transactional annotation, and enumerates nine typical situations—such as incorrect method visibility, final methods, self‑invocation, unmanaged beans, multithreading, wrong propagation, swallowed exceptions, unsupported exception types, and non‑transactional storage engines—where transactions may not work as expected, offering guidance to prevent these pitfalls.
This article explains how Redis transactions and pipelines can guarantee data consistency during high‑concurrency order processing while dramatically improving throughput, and provides Java code examples, feature comparisons, usage guidelines, and practical case studies for backend developers.
This article breaks down MySQL's complex locking mechanisms—global, table, and row locks—explaining how different SELECT statements, transaction isolation levels, and index types affect lock acquisition, and provides practical test scripts and visual examples to help developers master lock behavior.
The article explains Vitess's approach to generating globally unique identifiers by using dedicated sequence tables, details the underlying MySQL schema and Go implementation, and compares it with VtDriver's CAS‑based method, highlighting trade‑offs between transaction locks and lock‑free updates.
The article explains the concept of idempotency, its significance in preventing duplicate operations such as double payments, and outlines various backend techniques—including MVCC, deduplication tables, token mechanisms, and distributed locks—to ensure consistent system behavior across retries and network failures.
This article examines why PostgreSQL appears to outrank MySQL in recent popularity surveys, explains the DB‑Engines ranking methodology, compares architectural choices such as MVCC, multi‑process vs multi‑thread models, transaction rollback behavior, licensing, and real‑world performance benchmarks like TPC‑C, ultimately revealing that MySQL still dominates many high‑throughput OLTP scenarios.
The article evaluates database success by comparing PostgreSQL and MySQL across popularity, features, licensing, transaction behavior, MVCC, concurrency models, and use cases, showing PostgreSQL leads in complex queries and feature richness while MySQL remains the most widely used OLTP system, especially in China.
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—covers lock compatibility, deadlock causes and prevention, compares optimistic and pessimistic locking, and shows how SELECT FOR UPDATE behaves under different isolation levels.
This article explains how to parallelize business steps with CompletableFuture, why Spring's @Async and @Transactional annotations cannot guarantee transaction consistency across threads, and presents a programmatic transaction approach—including copying Spring's transaction resources between threads—to achieve reliable multi‑threaded commits or rollbacks.
This article explains how to manage multithreaded database insert operations in Spring Boot, demonstrates why the @Transactional annotation fails in a multithreaded context, and provides a solution using manual SqlSession transaction control with code examples and test results.