ok yeah, now I remember this issue. There was a guy once that wanted to do inserts, but each insert had to be in increments of 100 if you could imagine, starting @1000. And we had to wrap the whole thing in a stored proc to have one place of vulnerability. Your issue surfaced and it threw off his numbering by 1 or so.
By wrapping it, we could sanely have one point of doing it, with a lock, and maintain the auto_inc value with ALTER TABLE
The other approach I said to him was have an incrementer table, lock the 1 row, get the value in that row, use it, update that incTable by 100. unlock.
The whole time we were laughing about OCD issues. I think he liked multiples of 10 only, idk
Edit:
Schema:
-- drop table ocd_nextnums;
create table ocd_nextnums
( -- id table for nextnum, for the OCD impaired
tableName varchar(100) not null,
nextnum int not null
-- won't bother with indexes, go for it if you want
)engine=INNODB; -- note engine type
insert ocd_nextnums(tableName,nextnum) values('thing',1);
insert ocd_nextnums(tableName,nextnum) values('some_other_table',1);
-- drop table thing;
create table thing
( id int primary key, -- NOT an auto_increment, but is a PK
email varchar(100) not null,
version varchar(20) not null,
lastupdate datetime not null,
UNIQUE KEY (email)
)engine=MyIsam;
Stored Proc:
-- drop procedure putInThing;
delimiter $$
create procedure putInThing
(
email_In varchar(100), version_In varchar(20)
)
BEGIN
declare toUse int;
declare theCount int;
select count(*) into theCount from thing where email=email_In;
select id into toUse from thing where email=email_In; -- useful for result set @end
IF theCount=1 THEN
-- was there, do UPDATE
update thing set version=version_In,lastupdate=now() where email=email_In;
ELSE
-- new row, do INSERT (please note the FOR UPDATE clause)
select nextnum into toUse from ocd_nextnums where tableName='thing' FOR UPDATE;
update ocd_nextnums set nextnum=nextnum+1 where tableName='thing';
insert thing(id,email,version,lastupdate) values (toUse,email_In,version_In,now());
end if;
select toUse; -- <------- that was your id
END
$$
Test:
call putInThing('t1@t.com','111');
call putInThing('t2@t.com','121');
call putInThing('t3@t.com','107');
select * from thing;
+----+----------+---------+---------------------+
| id | email | version | lastupdate |
+----+----------+---------+---------------------+
| 1 | t1@t.com | 111 | 2015-08-14 17:08:10 |
| 2 | t2@t.com | 121 | 2015-08-14 17:08:54 |
| 3 | t3@t.com | 107 | 2015-08-14 17:08:56 |
+----+----------+---------+---------------------+
call putInThing('t3@t.com','101111007'); -- is an update
call putInThing('t3@t.com','42'); -- is an update
call putInThing('t3@t.com','10007'); -- is an update
call putInThing('h@hillaryclinton.com','1'); -- is an insert
select * from thing;
+----+----------------------+---------+---------------------+
| id | email | version | lastupdate |
+----+----------------------+---------+---------------------+
| 1 | t1@t.com | 111 | 2015-08-14 17:08:10 |
| 2 | t2@t.com | 121 | 2015-08-14 17:08:54 |
| 3 | t3@t.com | 10007 | 2015-08-14 17:22:09 |
| 4 | h@hillaryclinton.com | 1 | 2015-08-14 17:22:47 |
+----+----------------------+---------+---------------------+
From the Mysql INNODB part of Manual:
To implement reading and incrementing the counter, first perform a
locking read of the counter using FOR UPDATE, and then increment the
counter.....
Will you see me using this, probably not. Just showing it. I am fine with gaps and sleeping at night. Which is why I named the first table what I did :>
