0

A row tuple of inserts concurrent with two transactions does not specify a primary key id, which is self-increasing

How does Mysql ensure that two transactions are inserted smoothly, with different primary key ids?

If possible, I would like to be able to answer the execution process of insert statements, including inserting intentional locks, ensuring that the primary key ID does not conflict, and what locks are held after insert is completed.

Tao.Aaron
  • 21
  • 3
  • Seems to be a duplicate of [this question](https://stackoverflow.com/questions/4358732/is-incrementing-a-field-in-mysql-atomic). For MyISAM, the whole table would be locked, by whatever thread reaches first. For InnoDB, the row would be locked. – Tim Biegeleisen Apr 21 '19 at 15:00
  • @TimBiegeleisen, InnoDB does not use a row lock for incrementing a table's auto-inc value. It uses a special type of table lock. – Bill Karwin Apr 21 '19 at 15:14
  • @Bill Karwin Can you specify the process? – Tao.Aaron Apr 22 '19 at 02:22

1 Answers1

5

Basically the same way any other thread-safe code works: by creating a critical section of code, so no more than one thread has access to increment a given table's auto-increment value unless it first acquires a special type of table-lock.

It's a lock that lasts very briefly, only long enough to increment the table's auto-increment value. Then the lock is released, allowing another thread to do the same in thread-safe manner.

The lock is also per-table, unlike traditional implementations of critical section code, which often use a global mutex around a specific section of code.

There are options that control how the table lock is acquired and released in InnoDB. You might like to read https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828