[MySQL] 2. Lock Mechanism Execution Analysis

Introduction

In high-concurrency environments, database locking mechanisms are crucial for ensuring data consistency and integrity. MySQL, as a widely-used relational database, provides various lock types and mechanisms to manage concurrent access. However, improper use of locks may lead to performance bottlenecks, deadlocks, and other issues that affect system stability and response time.

Basic Lock-Related Concepts

• Lock Definition: A lock is a mechanism used to control access to shared resources, preventing multiple transactions from modifying the same data simultaneously, thereby ensuring data consistency and integrity.
• Lock Types:
  • Table-level Lock: Locks the entire table.
  • Shared Lock (S Lock): Allows multiple transactions to read data simultaneously but prevents modification.
  • Exclusive Lock (X Lock): Allows one transaction to modify data while preventing other transactions from reading or modifying.
  • Intention Locks (IS and IX Locks): Used at the table level to indicate that a transaction intends to acquire locks at the row level.
  • Auto-increment Lock (AUTO-INC Lock): Used to handle concurrent inserts on auto-increment columns, preventing conflicts.
  • Gap Lock: Locks the gaps between index records to prevent phantom reads.
  • Next-Key Lock: Combines record locks and gap locks, locking index records and the gaps before them.
  • Record Lock: Locks specific index records.
  • Row-level Lock: Locks specific rows.
  • Optimistic Lock: Implemented through version numbers or timestamps, suitable for read-heavy scenarios.
  • Pessimistic Lock: Implemented through explicit locking, suitable for write-heavy scenarios.
• Deadlock: Multiple transactions wait for each other to release locks, resulting in inability to continue execution.
• Lock Compatibility: Compatibility rules exist between different types of locks, determining which locks can coexist.
• Lock Granularity: The scope of locked resources; finer granularity provides higher system concurrency but increases management overhead.

MySQL Lock Introduction

Basic Commands

• Create test table

1
2
3
4
5
6
7
8
9

-- auto-generated definition
create table example_single_pk
(
    id      bigint                              not null comment 'id'
        primary key,
    created timestamp default CURRENT_TIMESTAMP not null comment 'create time',
    updated timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment 'update time'
)
    comment 'example_single_pk' charset = utf8mb4;

• Execute commands

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT id, created, updated FROM example_single_pk;

INSERT INTO example_single_pk (id) VALUES (1);

SELECT id, created, updated FROM example_single_pk;

UPDATE example_single_pk SET id = 6 WHERE id = 1;

SELECT id, created, updated FROM example_single_pk;

DELETE FROM example_single_pk WHERE id = 1 or id = 6;

SELECT id, created, updated FROM example_single_pk;

• Results

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

mysql> SELECT id, created, updated FROM example_single_pk;
Empty set (0.00 sec)

mysql>
mysql> INSERT INTO example_single_pk (id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT id, created, updated FROM example_single_pk;
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  1 | 2025-09-27 11:14:40 | 2025-09-27 11:14:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE example_single_pk SET id = 6 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT id, created, updated FROM example_single_pk;
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  6 | 2025-09-27 11:14:40 | 2025-09-27 11:14:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> DELETE FROM example_single_pk WHERE id = 1 or id = 6;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT id, created, updated FROM example_single_pk;
Empty set (0.00 sec)

Classification by Granularity

Table-level Lock - READ

Locking

1
2

mysql> LOCK TABLES example_single_pk READ;
Query OK, 0 rows affected (0.00 sec)

[Github] 4. Value-Checker-Java: Customizable AOP Validation Framework

Introduction

Value-Checker-Java is essentially a customizable AOP pointcut framework. It allows developers to insert custom validation logic before method execution, and this validation logic can be arbitrarily complex business rules.

However, if it merely provides an AOP pointcut, that wouldn’t be very meaningful. The core value of Value-Checker-Java lies in its thread-safe context management mechanism. Without this context management, data queried in the first validator cannot be used in subsequent validators, forcing each validator to re-query data, which defeats the purpose of validation chains.

[Github] 3. Basic-Check: Validation Framework

Introduction

Parameter validation is a common and crucial requirement in daily Java development. Traditional parameter validation typically requires writing extensive if-else conditional code in each method, which is not only redundant and tedious but also prone to omissions. Basic-Check-Java was born to solve this pain point as a lightweight parameter validation framework.

This article will provide an in-depth introduction to Basic-Check-Java’s design philosophy, core features, and practical applications, helping developers quickly master this practical tool.

[Cluster] 1. RAFT Algorithm: A Complete Evolution from Single Node to Distributed Consensus

Introduction

• RAFT (Raft Consensus Algorithm) is a distributed consensus algorithm designed to solve the problem of achieving data state agreement among multiple nodes in distributed systems.
• Compared to the renowned Paxos algorithm, RAFT’s design philosophy emphasizes “understandability” through clear role separation and straightforward state transitions, making it easier for developers to comprehend and implement.
• This article demonstrates the complete evolution of the RAFT algorithm from single-node to multi-node clusters through 11 detailed diagrams, covering key scenarios including normal operation, failure handling, network partitions, and conflict resolution.

Core RAFT Concepts

• Before diving into the analysis, let’s familiarize ourselves with several core concepts of RAFT:

Node States:

[Github] 2. MyBatis Generator Custom Plugins

🔗 Project Repository: mybatis-generator-custome-plugins

A powerful collection of MyBatis Generator custom plugins designed to enhance code generation capabilities with MySQL-specific features, DTO layer generation, and automatic Service layer generation.

🚀 Features Overview

This plugin collection includes 6 custom plugins:

📦 Dependency Analysis

Core Dependencies

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

<!-- MyBatis Generator Core Dependency -->
<dependency>
  <groupId>org.mybatis.generator</groupId>
  <artifactId>mybatis-generator-core</artifactId>
  <version>1.4.2</version>
</dependency>

        <!-- MyBatis Dynamic SQL Support -->
<dependency>
<groupId>org.mybatis.dynamic-sql</groupId>
<artifactId>mybatis-dynamic-sql</artifactId>
<version>1.5.2</version>
</dependency>

        <!-- MyBatis Spring Boot Integration -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>

Maven Plugin Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

<plugin>
  <groupId>org.mybatis.generator</groupId>
  <artifactId>mybatis-generator-maven-plugin</artifactId>
  <version>1.4.2</version>
  <configuration>
    <verbose>true</verbose>
    <overwrite>true</overwrite>
  </configuration>
  <dependencies>
    <dependency>
      <groupId>com.goody.utils</groupId>
      <artifactId>mybatis-generator-custome-plugins</artifactId>
      <version>1.0.0</version>
    </dependency>
  </dependencies>
</plugin>

🔧 Plugin Details

MySQL Extension Plugins

InsertIgnoreIntoPlugin

• Function: Adds INSERT IGNORE statement support to Mapper
• Generated Methods: insertIgnoreCustom(), insertIgnoreBatchCustom()
• Use Cases: Ignore primary key conflicts during batch inserts

InsertOnDuplicateKeyPlugin

• Function: Adds ON DUPLICATE KEY UPDATE statement support to Mapper
• Generated Methods: insertOnDuplicateKeyCustom(), insertOnDuplicateKeyBatchCustom()
• Use Cases: Auto update records on duplicate key conflicts during insert

ReplaceIntoPlugin

• Function: Adds REPLACE INTO statement support to Mapper
• Generated Methods: replaceIntoCustom(), replaceIntoBatchCustom()
• Use Cases: Replace existing records or insert new ones

DTO Layer Generation Plugin

DtoGeneratorPlugin

• Function: Automatically generates DTO classes
• Features:
  • Lombok annotation support (@Data, @Builder, @AllArgsConstructor, @NoArgsConstructor)
  • Auto-generated fromEntity() and toEntity() conversion methods
  • Package structure: *.model.dto

Service Layer Generation Plugin

ServiceGeneratorPlugin

• Function: Automatically generates Service interfaces and implementation classes
• Features:
  • Complete CRUD operation methods
  • Support for single and composite primary keys
  • Spring annotation support (@Service, @Autowired)
  • Package structure: *.service.interfaces and *.service.impl

Custom Mapper Plugin

CustomerMapperPlugin

• Function: Generates extended Mapper interfaces
• Package structure: *.dao.customer

💻 Usage Guide

Step 1: Add Dependencies

Add the plugin to your project:

[Customs] 1. IntelliJ IDEA Configuration & Recommended Plugins

Introduction

As one of the most powerful Java IDEs available, IntelliJ IDEA can be significantly enhanced through proper configuration and carefully selected plugins. This guide presents a curated collection of essential plugins and configuration skills that will transform your development experience.

The goal is to provide ready-to-use configurations and plugins that immediately improve productivity, code quality, and development workflow.

Essential Plugins

🔧 Development Tools

CamelCase (3.0.12)