并发插入MySQL时锁等待超时问题及锁相关疑问

Let's break down your problem step by step, starting with the core questions about the supremum pseudo-record, then moving into why you're hitting lock timeouts and how to resolve them.

What is the supremum pseudo-record? #

The supremum pseudo-record is a virtual, system-generated entry that InnoDB appends to the end of every B-tree index. It acts as a sentinel marker for the index's upper bound—think of it as a placeholder for any future record that would be larger than all existing entries in the index.

Is it a gap lock? Not exactly, but it's tightly linked to gap locking behavior. When you insert a record that's larger than all existing index entries, InnoDB locks the gap between the last real record and this supremum pseudo-record to prevent phantom reads (in the default REPEATABLE READ isolation level). The lock on the supremum record is part of this gap locking mechanism.

Why does it show as record type 'RECORD'? #

InnoDB's transaction status log represents the supremum pseudo-record as a RECORD because it treats this virtual entry like any other index record for locking purposes. You can easily identify it by the heap no 1 marker—real index records start at heap no 2, so heap no 1 always refers to this virtual supremum entry.

Why are you hitting lock wait timeouts? #

Looking at your InnoDB status log and scenario description, the key issues are:

1. Unordered primary key inserts: If your edges (Table B) primary key isn't auto-incrementing, inserts can be scattered across the index, triggering gap locks (including locks on the supremum record) that block concurrent inserts.
2. Extremely long transactions: The waiting transaction has been active for 2611 seconds—holding locks for this long guarantees lock contention and timeouts for other threads.
3. Foreign key constraint overhead: Inserting into Table B requires InnoDB to validate the referenced Table A record exists, adding shared (S) locks to Table A. If these checks aren't optimized, they amplify lock competition.

How to avoid these locks and timeouts? #

Here are actionable fixes tailored to your concurrent insert scenario:

1. Use auto-incrementing primary keys for both tables #

InnoDB has special optimizations for auto-increment primary keys:

• Inserts are appended to the end of the index, eliminating the need for gap locks (including those on the supremum record)
• InnoDB uses a lightweight "auto-inc lock" instead of row-level gap locks, drastically reducing lock contention for concurrent writes

2. Lower transaction isolation level (if business logic allows) #

By default, MySQL uses REPEATABLE READ isolation level, which enables gap locking to prevent phantom reads. If your application doesn't require strict repeatable reads:

• Switch to READ COMMITTED isolation level. In this mode, InnoDB only uses gap locks for foreign key and unique constraint checks, not for regular DML operations. This eliminates most gap locks (including those on the supremum record).

Set it globally with:

SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Or configure it per transaction in your Hibernate code.

3. Eliminate long transactions #

Your 2611-second transaction is a critical red flag. Ensure:

• Each insert operation runs in a short-lived transaction—never hold open transactions while doing non-database work (like file I/O, API calls, or waiting for user input)
• Use batch inserts instead of individual saves. Hibernate's saveAll() method or configuring hibernate.jdbc.batch_size (e.g., 50-100) reduces transaction count and lock hold time.

4. Optimize foreign key checks #

• Confirm Table A's primary key is properly indexed (it should be by default, but double-check)
• Use @ManyToOne(fetch = FetchType.LAZY) in your Hibernate mapping to defer foreign key validation until necessary, reducing immediate lock contention.

5. Tune lock timeout (temporary band-aid) #

If you need immediate relief while implementing the above fixes, increase the lock wait timeout:

SET GLOBAL innodb_lock_wait_timeout = 120; -- Default is 50 seconds

Note: This is not a long-term solution—focus on the root causes above.

内容的提问来源于stack exchange，提问作者Ravi