MySQL auto_increment Implementation

Operations, often encounter auto_increment doubts:

1. Machine exception crash, the issue of ID fallback after reboot
2. Performance considerations, each acquisition is certainly not persisted, in-memory value, statement replication how to ensure that the master is consistent
3. Is the value of the ID protected by Binlog?

1. Auto_increment-related parameter control

1.1 Innodb_autoinc_lock_mode

0: Each statement acquires an exclusive lock until statement ends, guaranteeing that the ID of the statement execution process is continuous.
1: Use a mutex when you determine the number of bars affected by the insert. If this is insert select,load data, use exclusive lock.
2: The IDs generated by multiple statement will be interspersed, and if they are statement copied, there will be inconsistencies.

1.2
Auto_increment_increment
Auto_increment_offset
Control the self-increment starting value and interval

2. Auto_increment related data structure 1. Lock_auto_inc in lock mode, which is the Auto_increment table lock.

/*Basic Lock Modes*/enumLock_mode {lock_is=0,/*intention Gkfx*/Lock_ix,/*intention Exclusive*/lock_s,/*shared*/lock_x,/*Exclusive*/lock_auto_inc,/* Locks the auto-inc counter of a table in an exclusive mode * /Lock_none,/*This is used elsewhere to note consistent read*/Lock_num= Lock_none/*Number of lock modes*/};

2. Dict_table_t:innodb table Definition

lock_t*        Autoinc_lock;   Table lock mutex_t        Autoinc_mutex;   Mutex lock ib_uint64_t    autoinc;  Self-increment　　　　　　　　 ulong        n_waiting_or_granted_auto_inc_locks;  Number of queues waiting for self-increment table lock const trx_t*        Autoinc_trx;        Hold self-Add table lock transactions

3. trx_t: Transaction structure

    ulint        n_autoinc_rows;        Statement number of rows inserted    ib_vector_t*    autoinc_locks;  Self-increment lock held

4. Handler:table's InnoDB engine handle

  ulonglong next_insert_id;   The next inserted ID  ulonglong Insert_id_for_cur_row, the currently inserted ID  discrete_interval auto_inc_interval_for_cur_row; Apply one interval at a time, slowing the presence of the server layer. Reduce the call  to InnoDB uint auto_inc_intervals_count;  Interval to request an ID from InnoDB. increments by [1248]. Up to 1<<1

Note: These variables in the handler are valid only under one statement, and the end of the statement is cleared.

3. Test Case

CREATE table pp (id int primary key auto_increment, name varchar (100));

Session1:insert into PP (name) values (' xx ');
Session2:insert into PP (name) values (' xx '), (' xx '), (' xx '), (' xx ')
Session3:insert into PP (name) select name from PP;

4. Auto_increment Principle of implementation

　　

4.2 Explanation of the lock

According to the time granularity of the lock holding, it is divided into
1. Memory level: Similar to mutex, soon released
2. Statement level: statement end, release
3. Transaction level: Transaction commit or rollback before release
4. Conversation level: Session level, connection disconnected before release

Here, Session1 and Session2 are all determining the number of inserts, so use a mutex to assign a fixed ID. and Session3 unknown, so in order to ensure that this statement ID is continuous, get a lock, maintain until the end of the statement release.

Therefore, in order to increase the concurrency, the lock holding the granularity of the smaller the better.

4.3 Explanation of the cache

For a statement, pre-allocating the ID value, reducing the request to INNODB, and correspondingly reducing the holding lock.

5. Test Details 5.1 First time execution

Based on select MAX (id) from PP: Gets the initial value of the Autoinc
This also explains the first question at the beginning of the article, why the machine crash, the ID will be back.

Simple function Stack:

Ha_innobase::open
Innobase_initialize_autoinc

5.2 Session 1

1. First hold the mutex, get Autoinc
2. Because the insert number is 1, calculate the new autoinc and update to dict_table_t, and then release the end of the mutex

Simple function stack
Handler::update_auto_increment
Ha_innobase::get_auto_increment
Ha_innobase::innobase_lock_autoinc
Mutex_enter (&table->autoinc_mutex);
Dict_table_autoinc_update_if_greater

5.3 Session 2

1. Because the insert has 4 bars, the previous steps are similar to Session1, but the calculation completes with the new autoinc of 5 and updates the dict_table_t.
2. Because the cache is [3,4,5], the next three inserts are fetched in the local cache and no longer request InnoDB.

5.4 Session 3

1. Because the number of inserts is not determined, lock is held during the entire execution of the statement.
2. At the end of the statement, release statement commit
3. Apply for 1 for the first time, 2 for the second application, 4 for the third application, and a total of 3 applications.

Simple function Stack:
Handler::update_auto_increment
Ha_innobase::get_auto_increment
Row_lock_table_autoinc_for_mysql

Trans_commit_stmt
Row_unlock_table_autoinc_for_mysql

Clean statement-level environment after statement ends
Ha_release_auto_increment

insert_id_for_cur_row= 0; The Insert ID of the current statement is set to 0
Auto_inc_interval_for_cur_row.replace (0, 0, 0); Pre-allocated emptying
Auto_inc_intervals_count= 0; Pre-allocated iterations are also clear 0
Table->in_use->auto_inc_intervals_forced.empty (); Clean up linked list

6. Warning:

1. If your watch is a insert+delete mode, you will find that the ID has been reused after rebooting, and be careful to say that it has been put in the pit.
2. If there is a self-increment key on the table, insert Select,load file will block the insert.

7. Consider:

1. How the distributed globally unique increment (not guaranteed continuous) is implemented. This is the problem that the distributed system needs to solve!