| Bug #98595 | Auto-inc number is different between different connections | ||
|---|---|---|---|
| Submitted: | 14 Feb 2020 9:30 | Modified: | 14 Feb 2020 13:46 |
| Reporter: | Fengchun Hua | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.18 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[14 Feb 2020 13:46]
MySQL Verification Team
Hi Mr. Hua, Thank you for your bug report. However, this is not a bug. This is exactly how the traditional auto-increment lock is designed to work in InnoDB storage engine, having in mind that InnoDB is also a MVCC engine. This is all thoroughly described in our Reference Manual, chapter 15.6.1. Simply, auto-increment lock is a table lock. In a single connection, the value of the next auto_increment value can be reused. This is not possible in concurrent connections, due to MVCC. Hence, you get the expected result. Last, but not least, this behaviour can not be changed and it is the same for any value of innodb_autoinc_lock_mode. Not a bug.
Description: See this caseļ¼ --innodb_autoinc_lock_mode=0 drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, ggid varchar(32) binary DEFAULT '' NOT NULL, email varchar(64) DEFAULT '' NOT NULL, passwd varchar(32) binary DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE ggid (ggid) ) ENGINE=innodb; insert into t1 (ggid,passwd) values ('test1','xxx'); insert into t1 (ggid,passwd) values ('test2','yyy'); #the sql below will return an error insert into t1 (ggid,passwd) values ('test2','this will fail'); if you execute this sql in same connection: insert into t1 (ggid,passwd) values ('test3','yyy'); select * from t1; the result is: +----+-------+-------+--------+ | id | ggid | email | passwd | +----+-------+-------+--------+ | 1 | test1 | | xxx | | 2 | test2 | | yyy | | 3 | test3 | | yyy | +----+-------+-------+--------+ but if you execute the same sql in a new connection: the result is: +----+-------+-------+--------+ | id | ggid | email | passwd | +----+-------+-------+--------+ | 1 | test1 | | xxx | | 2 | test2 | | yyy | | 4 | test3 | | yyy | +----+-------+-------+--------+ the third sql's auto inc number is not the same between different connections. How to repeat: See Description Suggested fix: The root case is, when one sql execute fail(confliction on the secondary index). autoinc_persisted will not rollback, which just increased when executing this sql. but autoinc is not increased because this sql failed. if you are using a new connection, this connection will open thetable, which will init autoinc again(because autoinc==autoinc_persisted in ha_innodb.cc:7029 8.0.18). then the autoinc number will increase by 1. but if you are using the same connection, it will not open table again. so, it will reuse autoinc again. I perfer the new connection's behavior, because once a autoinc number is used, no mater the result of this sql, this number should not be reused anymore.