DEV Community: Guangyong

The Seven Types of Locks in InnoDB

Guangyong — Tue, 20 May 2025 11:49:37 +0000

MySQL is currently the most widely used relational database in the world, and InnoDB is its most popular storage engine. InnoDB is renowned for its strong performance under high concurrency and large data volumes. This performance is closely tied to its sophisticated locking mechanisms.

In total, InnoDB supports seven types of locks:

Auto-Increment Locks
Shared and Exclusive Locks
Intention Locks
Insert Intention Locks
Record Locks
Gap Locks
Next-Key Locks

Scenario: Given a table t(id AUTO_INCREMENT, name) using the default isolation level REPEATABLE READ (RR), suppose it has existing rows:

1, li  
2, yang  
3, chen

Transaction A runs:

insert into t(name) value ('xxx'); -- not yet committed

Transaction B runs later:

insert into t(name) value ('ooo');

Question: Will Transaction B be blocked?

Analysis: InnoDB avoids phantom reads under RR by applying locking mechanisms. Here:

Transaction A inserts a row (4, 'xxx') via the auto-increment column.
If Transaction B proceeds without being blocked, it will insert (5, 'ooo').
Later, Transaction A inserts another row: (6, 'xxoo').

When A executes:

select * from t where id > 3;

It retrieves rows 4 and 6 but not 5, as uncommitted changes from B aren't visible under RR. This gap (missing 5) appears like a phantom, breaking the assumption of continuity for auto-increment fields.

Auto-Increment Locks

An Auto-Increment Lock is a special table-level lock taken during inserts into AUTO_INCREMENT columns to ensure consistent, gap-free key generation.

From the official documentation:

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

To configure its behaviour, the innodb_autoinc_lock_mode parameter can be adjusted.

What if it’s not AUTO_INCREMENT?

With a table t(id UNIQUE PRIMARY KEY, name) and existing records:

10, li  
20, yang  
30, chen

If Transaction A inserts id=11 and not yet committed

insert into t values(11, xxx);

and Transaction B inserts id=12 concurrently,

insert into t values(12, ooo);

auto-increment locks are not used. What locks are applied, and whether blocking occurs, will be discussed later.

Shared and Exclusive Locks

InnoDB implements standard row-level locking, including Shared (S) and Exclusive (X) locks.

A transaction must acquire an S-lock to read a row.
An X-lock is required to modify or delete a row.

Compatibility Matrix:

That means:

Multiple S-locks can coexist → concurrent reads allowed.
X-locks are exclusive → only one transaction can write or read-write.

Drawback: Limited parallelism in high-concurrency systems.
Solution: InnoDB utilises Multi-Version Concurrency Control (MVCC) to improve performance

Intention Locks

InnoDB uses multiple granularity locking, allowing row-level and table-level locks to coexist. This is enabled via Intention Locks. An intention lock signals a transaction's intent to acquire future S or X locks on specific rows. It exists only at the table level.

Types:

IS (Intention Shared): Intends to acquire S-locks on some rows.
IX (Intention Exclusive): Intends to acquire X-locks on some rows.

Examples:

select ... lock in share mode → sets IS-lock.
select ... for update → sets IX-lock.

Compatibility:

IS and IX locks are not mutually exclusive, but they conflict with S/X locks.

Insert Intention Locks

Do inserts require strong exclusive locks like updates/deletes?

Not always. This is where Insert Intention Locks, a subset of Gap Locks, come in. These are index-based locks that allow concurrent inserts within the same index range, as long as they don’t conflict.

From the official docs:
Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

Example: table with unique PK, InnoDB, RR:
t(id unique PK, name);

10, li  
20, yang  
30, chen

Transaction A inserts id=11, not yet committed
Transaction B inserts id=12

Although both operate in the same index gap, their insert positions don’t conflict. Thus:

Insert Intention Locks are used.
Transaction B is not blocked.

Summary of Lock Strategies So Far:

Shared Locks (S) enable concurrent reads in InnoDB.
Exclusive Locks (X) ensure serialised update/delete in InnoDB.
Insert Intention Locks enable concurrent inserts in InnoDB.

Record Locks

Record Locks apply to individual index records. Example:

select * from t where id = 1 for update;

This locks the index entry with id=1, preventing concurrent modifications.

Note:
Regular reads like select * from t where id = 1 do not lock the row — these are snapshot reads, handled by MVCC.

Gap Locks

Gap lock locks the gaps between index records, as well as the range before the first index record or after the last one.

Example, InnoDB, RR:

t(id PK, name KEY, sex, flag);

There are four records in the table:

1, li, m, A
3, yang, m, A
5, chen, m, A
9, huang, f, B

Query:

select * from t where id between 8 and 15 for update;

This locks the gap, preventing other transactions from inserting, say, id=10.

Purpose:

Avoids “phantom reads” by locking non-existent rows.
Only effective under REPEATABLE READ (RR).
Automatically disabled under READ COMMITTED (RC).

Next-Key Locks

A Next-Key Lock is a combination of a Record Lock + Gap Lock. It locks:

The indexed row itself.
Including the index range.

From the docs:
If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

Example, InnoDB, RR:

t(id PK, name KEY, sex, flag);

with records:

1, li, m, A
3, yang, m, A
5, chen, m, A
9, huang, f, B

The potential next-key lock on the primary key is:

(-infinity, 1], (1, 3], (3, 5], (5, 9], (9, +infinity)

Purpose:

Also to prevent phantom reads, effective under RR, disabled under RC.

Summary

Lock Type	Level	Use Case	Blocking Behaviour
Auto-Increment Lock	Table	Ensures sequential IDs for AUTO_INCREMENT	Blocks concurrent inserts
Shared/Exclusive Locks	Row	Controls read/write access	Read-read OK, write conflicts blocked
Intention Locks	Table	Signals future row-level lock intent	Weak lock, enables compatibility checking
Insert Intention Locks	Index Gap	Allows safe concurrent inserts	No blocking if insert positions differ
Record Locks	Index Row	Protects individual rows	Blocks conflicting changes
Gap Locks	Index Gap	Prevents phantom inserts in range	Active only under RR
Next-Key Locks	Index Row+	Combines record + gap lock for phantom prevention	Active only under RR

PRIMARY KEY and UNIQUE Index Constraints and Their Strange Behaviors

Guangyong — Mon, 24 Mar 2025 09:13:01 +0000

The difference between MySQL's primary key constraint and unique index constraint.

When Are Constraint Checks Triggered

MySQL checks constraints during two operations:

insert
update

If a constraint violation occurs, the behavior depends on the storage engine:

If the storage engine supports transactions (like InnoDB), the SQL statement will automatically roll back.
If the storage engine does not support transactions (like MyISAM), the SQL execution will be interrupted mid-way, potentially leaving the dataset in an unexpected partial state.

Example with InnoDB

create table t1(id int(10) primary key)engine=innodb;
insert into t1 values(1);
insert into t1 values(1);

The second insert violates the primary key constraint, and InnoDB will roll back that SQL statement. Can check errors with:

Example with MyISAM(The Tricky Part)

create table t2(id int(10) unique)engine=MyISAM;
insert into t2 values(1);
insert into t2 values(5);
insert into t2 values(6);
insert into t2 values(10);

update t2 set id=id+1;

What result set do you get after this update? It's 2,5,6,10. Why?

The first row id=1 gets updated to 2 successfully.
The second row id=5 attempts to become 6, but since id=6 already exists, this violates the UNIQUE constraint, so SQL execution stops, and this row isn’t modified.
The third and fourth rows (id=6 and id=10) are not executed at all.

This partial update is some rows are updated, others fail silently.

How to Avoid This Mess?

Use the InnoDB storage engine. InnoDB will roll back the entire update if any constraint violation occurs — no partial updates.

Handling Constraint Violations During INSERT

Can use the insert ... on duplicate key syntax to specify what action to take if a primary key or unique constraint conflict occurs.

Example:

create table t3 (
  id int(10) unique,
  flag char(10) default 'true'
) engine=MyISAM;

insert into t3(id) values(1);
insert into t3(id) values(5);
insert into t3(id) values(6);
insert into t3(id) values(10);

insert into t3(id) values(10) on duplicate key update flag = 'false';

What happens here?

The attempt to insert id=10 triggers a UNIQUE constraint violation.
Instead of failing, it executes UPDATE flag='false' on the existing row where id=10.
Effectively, this is the same as:

update t3 set flag = 'false' where id = 10;

Interestingly, the insert statement returns:

Summary

Primary key and unique index constraints are checked on insert and update operations.
InnoDB rolls back the entire SQL on constraint violations.
MyISAM will partially execute and then stop on constraint violations, which may cause unexpected partial results.
Can use insert ... on duplicate key update to define custom behavior when constraints are violated.
Always use show warnings; to debug constraint-related errors.

In high-concurrency, large-scale systems, for the sake of stability (and your sanity), always use InnoDB.

MQ: How Does It Achieve Peak Shaving and Valley Filling?

Guangyong — Fri, 21 Mar 2025 01:33:30 +0000

Message Queuing (MQ) has many application scenarios, such as message publishing and subscription, decoupling between upstream and downstream systems. One typical use of MQ is to buffer traffic, helping to shave peaks and fill valleys.

How do upstream and downstream systems usually communicate?

There are two common communication methods:

Direct Call: Through an RPC framework, the upstream system directly calls the downstream system.

MQ Push: The upstream system sends messages to the MQ, and the MQ pushes the messages to the downstream system.

Can these methods cache traffic or achieve peak shaving and valley filling?

No. Whether using "direct calls" or "MQ push", both have a drawback: the downstream message receiver cannot control the traffic arriving at their system. If the upstream system doesn’t limit the speed, it could overwhelm the downstream system.

Example: Double 11 Shopping Festival Scenario

Upstream system: Initiates the order placing operation.
Downstream system: Completes the following business logic (inventory check, inventory freeze, balance check, balance freeze, order generation, balance deduction, inventory deduction, transaction generation, balance thaw, inventory thaw).

The upstream order placing operation is simple, sending 10,000 requests per second. The downstream logic is complex, processing only 2,000 requests per second. If the upstream system sends requests without throttling, it could overwhelm the downstream system, causing a system crash.

How to prevent overwhelming the downstream system?

To avoid the system crash, there are two common optimization strategies:

Buffering at the upstream queue, limiting the rate of requests sent.
Buffering at the downstream queue, limiting the rate of execution.

However, both solutions introduce additional business complexity. Any system requiring traffic buffering will need to incorporate similar mechanisms. This is a universal pain point that requires a generic solution.

Can MQ Achieve Traffic Buffering?

Yes, but it requires a small modification. To achieve traffic buffering with MQ, the MQ-server push mode can be upgraded to MQ-client pull mode. In this mode, the MQ-client, based on its processing capacity, pulls a batch of messages from the queue at regular intervals or in batches. The client then implements flow control, protecting itself from being overwhelmed. This is a generic feature provided by MQ, so no changes to the upstream or downstream systems are needed.

What Happens if the Upstream System Sends Too Much Traffic?

If the upstream system sends excessive traffic, the pull mode provided by MQ can indeed protect the downstream system by enabling it to pull messages at a controlled pace. But this raises the question: Will messages accumulate in the MQ queue?

In pull mode, the downstream MQ-client can fetch messages in batches. However, to maximize throughput, the downstream system needs to be optimized. For example, batch writes can be used to improve processing efficiency.

Conclusion

MQ-client pull mode enables periodic or batch message pulling, which helps smooth out traffic, offering self-protection for the downstream system (this is the job of MQ).
To increase overall throughput, the downstream system must implement optimizations such as batch processing (this is the job of the message receiver).

Why can't refunds be processed on Double 11? Because for the entire transaction process, only the first half is completed. The second half, involving the MQ, is still stored in the database, waiting for the downstream systems to process it.

InnoDB Indexs

Guangyong — Thu, 20 Mar 2025 09:00:09 +0000

Database indexes are divided into Primary Indexe and Secondary Indexe. How do InnoDB and MyISAM utilize B+ trees to implement these two types of indexes, and what are the differences between them?

Question 1: What is the index structure of MyISAM?

In MyISAM, the index structure is separate from the data rows, meaning it uses what is called a non-clustered index. The primary index and secondary index in MyISAM don’t have any significant differences:

There is a contiguous storage region for row records.
In a primary index, the leaf nodes store the primary key along with the corresponding row record pointer.
In a secondary index, the leaf nodes store the indexed column and a pointer to the corresponding row record.

Note: MyISAM tables don’t necessarily require a primary key.

The primary and secondary indexes are two independent B+ trees. When you perform a search using an indexed column, the system first locates the leaf node in the B+ tree, then uses the pointer to find the corresponding row record.

For example, consider the table t(id PK, name KEY, sex, flag); which contains four records:

1, joe, m, A
3, bob, m, A
5, king, m, A
9, shiho, f, B

The B+ tree indexing structure looks like this:

Row records are stored separately.
id is the PK, with an index tree for id, and the leaf points to the row record.
name is a KEY, with an index tree for name, and the leaf also points to the row record.

Question 2: What is the index structure of InnoDB?

In InnoDB, the primary index and row records are stored together, which is why it's called a clustered index:

There is no separate area for storing row records.
In a primary index, the leaf nodes store the primary key and the row record itself (not just the pointer).

Note: This is why InnoDB’s primary key queries are very fast.

Because of this structure, InnoDB tables must have a clustered index:

If a table defines a primary key, the primary key becomes the clustered index.
If there is no primary key, the first non-null unique column is used as the clustered index.
If there is no unique column, InnoDB creates a hidden row-id as the clustered index.

You can only have one clustered index in InnoDB, because row data can only be physically stored once on the disk.

InnoDB’s secondary indexes can be multiple. They differ from the clustered index:

The leaf nodes of a secondary index store the primary key (not just a pointer).

Question 3: Why does InnoDB recommend using a progressively increasing primary key?

In InnoDB, since row data and indexes are combined, using a progressively increasing primary key avoids index fragmentation during inserts, preventing excessive row movement in the index.

Question 4: Why is it not recommended to use long columns as primary keys in InnoDB?

Let’s consider a scenario where you have a user center that includes fields like ID card number, MD5 hash of ID card, name, birth date, etc. These fields are queried frequently and require transactional support, so you must use the InnoDB storage engine.

What’s the best way to design the data table? A simple design would be:

user(id_code PK, id_md5(index), name(index), birthday(index));

Now, the index tree and record structure looks like this:

id_code as a clustered index, pointing to the row records.
Other indexes store id_code values.

Since id_code (ID card number) is a long string, every index needs to store this value. When you have a large dataset and limited memory, MySQL's buffer pool will store fewer indexes and row data, increasing the chance of disk I/O.

Note: This also increases the disk space used by the indexes.

A better solution is to add an auto-increment id column with no business meaning as the primary key:

user(id PK auto inc, id_code(index), id_md5(index), name(index), birthday(index));

This way, the buffer pool can store more indexes and row data, reducing disk I/O frequency and improving overall performance.

Question 5: What potential problem arises from InnoDB's secondary index storing the primary key value?

When using a secondary index in InnoDB, you might encounter a problem called “covering index”, or “back-to-table queries”.

What are back-to-table queries? Let’s use the earlier example:

t(id PK, name KEY, sex, flag);

The id column is the clustered index (PK), and the leaf nodes store row records.
The name column is a secondary index, and its leaf nodes store the primary key (id).

Since the secondary index does not point directly to the row record, we must scan the index twice to get the data:

First, we locate the primary key value (e.g., id=5) in the secondary index.
Then, we use the primary key to find the corresponding row record in the clustered index.

This is known as a back-to-table query. It’s slower than scanning a single index tree. Example query:

SELECT id, name, sex FROM t WHERE name='bob';

The process:

First, scan the secondary index to get the primary key id=5.
Then, scan the primary index to retrieve the actual row.

This process is slower than just scanning the index tree once.

Question 6: How to optimize back-to-table queries?

The common solution is to use covering indexes. What is a covering index?

According to the SQL Server documentation:

According to the SQL Server documentation (also applicable to MySQL), a covering index allows you to retrieve all the required data from a single index without needing to go back to the table.

To implement a covering index, you can create a composite index that includes all the columns used in the query. For example, instead of using a single column index on name, you can upgrade it to a composite index:

create index idx_name_sex on t(name, sex);

Now, the query:

select id, name, sex from t where name='bob';

Can be handled by the covering index, avoiding the need to go back to the table, thus improving performance.

Database Indexes: Why and How?

Guangyong — Wed, 19 Mar 2025 10:39:04 +0000

Question 1: Why Do We Need Indexes in a Database?

Imagine a library with 10 million books, and you need to find The Road to Architect. If you check each book one by one, how long would that take? Instead, librarians have a system:

History books are on the first floor, literature on the second, IT books on the third…
IT books are further divided into software and hardware…
Software books are sorted alphabetically…

This way, finding a book becomes much faster. Similarly, when a database stores 10 million records and you need to find name="Architect", scanning one by one would be painfully slow. That's why we use indexes to speed up lookups.

Question 2: Hash vs. Tree-Why Use a Tree Structure for Indexes?

There are two common data structures used to speed up lookups:

Hashing (e.g., HashMap)-Average time complexity for search/insert/update/delete is O(1).
Tree structures (e.g., balanced binary search trees)-Average time complexity for operations is O(log(n)).

Clearly, a hash-based index is faster than a tree-based index. So why do databases still prefer tree-based indexes?

It all comes down to SQL query requirements.

If you only need single record lookups, like SELECT * FROM t WHERE name="Architect";, hash indexes are indeed the best choice.
However, for sorted queries, such as: GROUP BY, ORDER BY and Range comparisons (<, >)

A hash index becomes inefficient, degrading to O(n) complexity. On the other hand, tree structures retain O(log(n)) efficiency due to their ordered nature.

Key takeaway: Database indexes must be designed to meet query patterns, not just raw performance metrics.

Also, note that InnoDB doesn't support manually created hash indexes. Instead, it has an adaptive hash index, which is managed automatically by the database engine.

Question 3: Why Do Databases Use B+ Trees for Indexes?

To understand why, let's first look at some tree structures.

Binary Search Tree (BST)

A BST is a simple and well-known data structure, but it's not suitable for database indexing because:

As the dataset grows, the tree height increases, making searches slower.
Each node stores only one record, leading to excessive disk I/O.

B-Tree

B-Tree improve on BST:

Instead of binary splits, they use m-way branching.
Both leaf and non-leaf nodes store data.
You can retrieve all records using in-order traversal.

The key advantage of B-Trees is their efficient use of the locality principle:

Memory is fast, but disk reads are slow.
Disk prefetching means data is read in pages (e.g., 4KB per page in OS, 16KB in MySQL).
Locality principle: If a program accesses data, it's likely to access nearby data soon.

Since B-Trees store multiple records per node, they reduce the number of disk I/O operations, making lookups faster.

B+Tree

The B+ Tree is an enhancement of the B-Tree:

Non-leaf nodes do not store actual data-only keys.
All data is stored in leaf nodes at the same level.
Leaf nodes are linked, allowing for fast range queries without in-order traversal.

Why is B+ Tree better?

Faster range queries-Find min and max, then traverse the linked list.
More compact indexing-Non-leaf nodes store only keys, making the index more memory-efficient.
Lower tree height-With m-way branching, B+ Trees have much lower depth than BSTs.

How Does B+ Tree Reduce Tree Depth?

Let's estimate:

Assume each node is 4KB.
A key size is 8 bytes, so one node can store 500 keys.
The tree has an m-way branching factor of ~1000.

Tree Level	Nodes	Total Keys	Approx Size
1	1	Row 1 500	4KB
2	1000	500000	4MB
3	1000000	500M	4GB

With just 3 levels, a B+ Tree can handle 500 million records while keeping the index small (~4GB). This makes it an excellent choice for large databases.

Summary

Indexes accelerate database queries.
Hash indexes (O(1)) are faster for lookups, but tree indexes (O(log(n))) are better for range queries.
InnoDB does not support manual hash indexes but uses adaptive hash indexing.
Disk prefetching and locality principles make B-Trees highly efficient for database indexing.
B+ Trees outperform B-Trees because they:
1. Have faster range queries due to leaf node links.
2. Store actual data only in leaf nodes, optimizing memory usage.
3. Have lower depth, enabling faster lookups and less disk I/O.

The B+ Tree is the standard for database indexing because it balances efficiency, scalability, and query flexibility.