What is interesting about this situation can be solved using the MyISAM storage.
I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site
CREATE TABLE site_workorder_seq
(
SiteID int not null,
SiteWorkorderNum int not null auto_increment,
PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;
Here is a sample loading into this table:
mysql> DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)
mysql> USE david
Database changed
mysql> CREATE TABLE site_workorder_seq
-> (
-> SiteID int not null,
-> SiteWorkorderNum int not null auto_increment,
-> PRIMARY KEY (SiteID,SiteWorkorderNum)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO site_workorder_seq (SiteID) VALUES
-> (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
-> (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
+--------+------------------+
16 rows in set (0.00 sec)
mysql>
Let's look at the the last WorkorderNum from each site
mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
-> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 2 |
+--------+------------------+
5 rows in set (0.05 sec)
mysql>
Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:
INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;
Let's run this and see what happens
mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
-> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
| 5 |
+-------------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
+--------+------------------+
17 rows in set (0.00 sec)
mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
-> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
| 4 | 4 |
| 5 | 2 |
+--------+------------------+
5 rows in set (0.00 sec)
mysql>
As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.
Here's what the MySQL 5.5 documentation says:
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
Unless you fall into the other caveats noted in the question you link to, the last_insert_id()
function will return what you expect, and will not be affected by other sessions.
Best Answer
Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive.
However, depending on the variable
innodb_autoinc_lock_mode
, other non-trivial inserts likeinsert... on duplicate key update
andinsert... select
may not guarantee that deterministic behavior (as they may not know the actual number of rows to be inserted). This is to allow for extra concurrency by sacrifying the consecutive values. In all cases, a unique, monotonically increasing value is guaranteed for the table as a whole, it is just that in some cases there may be gaps and interleavings.You can know more about innodb autoinc behavior in the manual. And you can also test it in practice by doing:
while you insert as fast as possible other value in concurrency.
Addendum: I just realized a case in which you cannot assume that: if you use auto_increment_offset :-)