Detailed example of innodb_autoinc_lock_mode in mysql

The following editor will bring you a brief discussion on the expression form and value selection reference method of innodb_autoinc_lock_mode. The editor thinks it’s pretty good, so I’ll share it with you now and give it as a reference. Let’s follow the editor and take a look.

Prerequisites, percona version 5.6, transaction isolation level is RR

mysql> show create table test_autoinc_lock\G
*************************** 1. row ***************************
    Table: test_autoinc_lock
Create Table: CREATE TABLE `test_autoinc_lock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
8 rows in set (0.00 sec)

Condition 1 innodb_autoinc_lock_mode is set to 0

session1
 begin;delete from test_autoinc_lock where a>7;//这时未提交
session2
mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态，理论上这个不是gap锁的锁定范围，那么它是在等什么呢
session4
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
          trx_id: 2317
         trx_state: LOCK WAIT
        trx_started: 2016-10-31 19:28:05
   trx_requested_lock_id: 2317:20
     trx_wait_started: 2016-10-31 19:28:05
        trx_weight: 1
    trx_mysql_thread_id: 9
         trx_query: insert into test_autoinc_lock(a) values(2)
    trx_operation_state: setting auto-inc lock
     trx_tables_in_use: 1
     trx_tables_locked: 1
     trx_lock_structs: 1
   trx_lock_memory_bytes: 360
      trx_rows_locked: 0
     trx_rows_modified: 0
  trx_concurrency_tickets: 0
    trx_isolation_level: REPEATABLE READ
     trx_unique_checks: 1
  trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
     trx_is_read_only: 0
trx_autocommit_non_locking: 0

At this time, check that session3 is waiting for the auto-increment lock and has been in the setting auto-inc lock state

session2

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

At this time, session3 lock wait timeoutExit

##session3

At this time, if you look at session3, you can see that the insert is completed.

mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
9 rows in set (0.00 sec)//注意看这时的最大自增值是13，也就是之前自增最大值上+1，也就是说session2后来释放了预计生成的自增id，将13留给了session3，自增id值的申请完全是串行顺序的。

Conclusion: When innodb_autoinc_lock_mode is 0, it is the official traditional

level. This self-increasing lock is a table lock level and must wait until the current SQL execution is completed or rolled back before it is released. In this case, under high concurrency conditions, it is conceivable that the competition for self-increasing locks is relatively large.

Condition 2 innodb_autoinc_lock_mode is set to 1

##

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> delete from test_autoinc_lock where a>7;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
9 rows in set (0.00 sec)//注意看这时的最大自增值是13


session2
mysql> insert into test_autoinc_lock(a) values(100);//同样gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(5);
Query OK, 1 row affected (0.00 sec)


mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 15 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
10 rows in set (0.00 sec)//session3直接完成了，并且注意观察插入的自增id值是15，也就是跳过了预计分配给session2的14，可以看到自增id值立马就分配给了session3，而不必等session2执行完成

Conclusion: innodb_autoinc_lock_mode is 1, which is the official coherent level. At this time, if it is a single insert SQL, the lock can be obtained immediately and released immediately without waiting for the current SQL execution to complete. (Unless there is already a session that has acquired an auto-increasing lock in other transactions). In addition, when the SQL is some batch insert sql, such as insert into...select..., load data, replace ..select..., it is still a table-level lock, which can be understood as degenerating into having to wait for the current SQL to be executed. Only then released.

It can be considered that when the value is 1, it is a relatively lightweight lock and will not affect replication. The only flaw is that the self-increasing value generated is not necessarily completely continuous (but I personally think this is often It is not very important, and there is no need to count the number of rows based on the auto-incremented id value)

Condition 3 innodb_autoinc_lock_mode is set to 2First Conclusion: When innodb_autoinc_lock_mode is set to 2, all insert types of SQL can immediately obtain the lock and release it, which is the most efficient. But a new problem will be introduced: when binlog_format is statement, the replication at this time cannot guarantee

safety

, because batch inserts, such as insert..select.. statements, can also be executed immediately in this case. After obtaining a large number of self-increasing ID values, there is no need to lock the entire table. The slave will inevitably cause confusion when playing back the SQL. Let's do a test to verify that copying is not safe.

master session1
mysql> show variables like '%binlog_for%';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into test_autoinc_lock(a) select * from test_auto;
Query OK, 8388608 rows affected, 1 warning (29.85 sec)
Records: 8388608 Duplicates: 0 Warnings: 1


master session2（注意session2在session1执行完成之前执行）
mysql> insert into test_autoinc_lock(a) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_autoinc_lock where a=2;
+---------+------+
| id   | a  |
+---------+------+
| 1376236 |  2 |
+---------+------+
1 row in set (0.00 sec)


slave session1（这时可看到1376236主键冲突）
mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.9.73.139
         Master_User: ucloudbackup
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000006
     Read_Master_Log_Pos: 75823243
        Relay_Log_File: mysql-relay.000002
        Relay_Log_Pos: 541
    Relay_Master_Log_File: mysql-bin.000006
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
       Replicate_Do_DB: 
     Replicate_Ignore_DB: 
      Replicate_Do_Table: 
    Replicate_Ignore_Table: 
   Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
          Last_Errno: 1062
          Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto'
         Skip_Counter: 0
     Exec_Master_Log_Pos: 75822971

It is not difficult to find the cause of the problem when we analyze the binlog of the main library. Before the first batch insert is executed, the second simple insert is executed. A lock with an auto-increment ID value of 1376236 is obtained. At this time, there is no problem writing in the main library, but when it is reflected to the slave library, because it is statement-based replication, a primary key conflict will inevitably occur.

SET INSERT_ID=1376236/*!*/;
#161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c   Query  thread_id=20  exec_time=0   error_code=0
use `test`/*!*/;
SET TIMESTAMP=1477921471/*!*/;
insert into test_autoinc_lock(a) values(2)
/*!*/;
# at 75822940
#161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d   Xid = 274
COMMIT/*!*/;
# at 75822971
#161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b   Query  thread_id=57  exec_time=30  error_code=0
SET TIMESTAMP=1477921466/*!*/;
BEGIN
/*!*/;
# at 75823050
# at 75823082
#161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1   Intvar
SET INSERT_ID=1/*!*/;
#161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba   Query  thread_id=57  exec_time=30  error_code=0
SET TIMESTAMP=1477921466/*!*/;
insert into test_autoinc_lock(a) select * from test_auto

Summary: 1 When copying innodb row, you can set innodb_autoinc_lock_mode to 2, this The table can obtain the maximum concurrency in all insert situations

2 When copying innodb statement, you can set innodb_autoinc_lock_mode to 1 to ensure the safety of replication and obtain the maximum concurrency of a simple insert statement

3 In the case of myisam engine, no matter what kind of self-increasing ID lock is table-level lock, setting the innodb_autoinc_lock_mode parameter is invalid (testing omitted)

4 In fact, the questioner mentioned that the self-increasing ID value under the innodb engine As a primary key, compared to uuid or custom primary key, the insertion speed can be mentioned, because innodb is a primary key cluster

index

, the actual primary key value must be accessed in the order of the primary key, then automatically Increasing the id itself is in ascending order, so when inserting data, the bottom layer does not need to do additional sorting operations, and it also reduces the number of index page splits, thereby greatly increasing the insert speed (unless other solutions can also guarantee The primary key is completely auto-incremented)[Related recommendations]

1.

Mysql free video tutorial

2.

Add new user permissions in MySQL Detailed explanation of examples

3.

Detailed explanation of examples of changing passwords and access restrictions in MySQL

4.

Using regular expressions to replace the content in the database Detailed explanation of examples

5.

Detailed explanation of examples of php storing pictures in mysql

The above is the detailed content of Detailed example of innodb_autoinc_lock_mode in mysql. For more information, please follow other related articles on the PHP Chinese website!