MySQL Invoice Number Sequence: Gap-Free Numbering

Customers do not care about the internal id of a row, but invoice numbers, receipt numbers and ticket numbers often must be consecutive (many tax rules require invoice numbers without gaps), and often restart each year or for each company. AUTO_INCREMENT is not designed for that. This lesson shows why, and what to use.

AUTO_INCREMENT does not promise consecutive numbers

query.sql

SQL

CREATE TABLE demo (id INT AUTO_INCREMENT PRIMARY KEY, note VARCHAR(20) NOT NULL UNIQUE);

INSERT INTO demo (note) VALUES ('first');
INSERT IGNORE INTO demo (note) VALUES ('first');          -- duplicate: nothing inserted, but a number is used
INSERT INTO demo (note) VALUES ('second');

START TRANSACTION;
INSERT INTO demo (note) VALUES ('rolled back');
ROLLBACK;                                                  -- the number is not given back
INSERT INTO demo (note) VALUES ('third');

SELECT id, note FROM demo ORDER BY id;

Output

OUTPUT

+----+--------+
| id | note   |
+----+--------+
|  1 | first  |
|  3 | second |
|  5 | third  |
+----+--------+
3 rows in set

The ids are 1, 3 and 5. Numbers were used up by the ignored duplicate and by the rolled-back insert, and are never returned. Other causes: failed inserts, deleted rows, a server restart in older versions, and bulk inserts that reserve ranges. The generator is built for speed with many sessions at once, not for gap-free sequences (AUTO_INCREMENT).

For row identity, gaps do not matter. For legally consecutive numbers, you need something else.

A counter table

Keep the next number for each sequence in a small table, and take a number with one atomic statement:

query.sql

SQL

CREATE TABLE invoice_counters (
    year SMALLINT NOT NULL PRIMARY KEY,
    last_number INT UNSIGNED NOT NULL
);

CREATE TABLE invoices (
    year SMALLINT NOT NULL,
    number INT UNSIGNED NOT NULL,
    customer VARCHAR(30) NOT NULL,
    PRIMARY KEY (year, number)
);

Output

OUTPUT

1
2
3

Query OK, 0 rows affected

Query OK, 0 rows affected

Taking the next number, the first time for a year (the row does not exist yet):

query.sql

SQL

1
2
3

INSERT INTO invoice_counters (year, last_number) VALUES (2026, 1)
ON DUPLICATE KEY UPDATE last_number = LAST_INSERT_ID(last_number + 1);
SELECT LAST_INSERT_ID() AS number_taken;

Output

OUTPUT

+--------------+
| number_taken |
+--------------+
|            5 |
+--------------+
1 row in set

This is a trap. The first call inserted a new row, so the UPDATE branch did not run, and LAST_INSERT_ID() was not set by this statement. It still shows 5, a leftover from the demo table above in the same connection. Starting a sequence with a “first insert” special case makes the first number unreliable. A simpler rule has no special case: create the counter row with 0 when you create the year, and always take numbers with an UPDATE:

query.sql

SQL

INSERT INTO invoice_counters (year, last_number) VALUES (2027, 0);

UPDATE invoice_counters SET last_number = LAST_INSERT_ID(last_number + 1) WHERE year = 2027;
SELECT LAST_INSERT_ID() AS number_taken;

UPDATE invoice_counters SET last_number = LAST_INSERT_ID(last_number + 1) WHERE year = 2027;
SELECT LAST_INSERT_ID() AS number_taken;

Output

OUTPUT

+--------------+
| number_taken |
+--------------+
|            1 |
+--------------+
1 row in set

+--------------+
| number_taken |
+--------------+
|            2 |
+--------------+
1 row in set

LAST_INSERT_ID(expr) stores the value of the expression for this connection and returns it, so the number you read is exactly the one this statement wrote. It is safe with concurrent sessions, because LAST_INSERT_ID() is per connection, and the UPDATE locks the counter row until the transaction ends. (Create the rows for next year in a migration or a scheduled job, before January.)

Why a transaction is the key

The counter row stays locked until COMMIT. If the invoice insert fails and the transaction rolls back, the counter update rolls back too, and the number is not lost. Other sessions wait for the lock, so the sequence has no gaps (and also no parallelism on that sequence):

query.sql

SQL

INSERT INTO invoice_counters (year, last_number) VALUES (2028, 0);

-- an attempt to issue an invoice that fails (the customer is required)
START TRANSACTION;
UPDATE invoice_counters SET last_number = LAST_INSERT_ID(last_number + 1) WHERE year = 2028;
SET @n = LAST_INSERT_ID();
INSERT INTO invoices (year, number, customer) VALUES (2028, @n, NULL);
ROLLBACK;

SELECT last_number FROM invoice_counters WHERE year = 2028;

Output

OUTPUT

ERROR 1048 (23000): Column 'customer' cannot be null

+-------------+
| last_number |
+-------------+
|           0 |
+-------------+
1 row in set

The insert failed (error 1048), and the rollback also undid the counter update, so the counter is still 0: the number 1 is available for the next invoice.

The complete pattern

query.sql

SQL

START TRANSACTION;

UPDATE invoice_counters SET last_number = LAST_INSERT_ID(last_number + 1) WHERE year = 2028;
SET @n = LAST_INSERT_ID();
INSERT INTO invoices (year, number, customer) VALUES (2028, @n, 'Green School');

COMMIT;

START TRANSACTION;
UPDATE invoice_counters SET last_number = LAST_INSERT_ID(last_number + 1) WHERE year = 2028;
SET @n = LAST_INSERT_ID();
INSERT INTO invoices (year, number, customer) VALUES (2028, @n, 'Blue Traders');
COMMIT;

SELECT CONCAT('INV-', year, '-', LPAD(number, 5, '0')) AS invoice_no, customer FROM invoices ORDER BY year, number;

Output

OUTPUT

+----------------+--------------+
| invoice_no     | customer     |
+----------------+--------------+
| INV-2028-00001 | Green School |
| INV-2028-00002 | Blue Traders |
+----------------+--------------+
2 rows in set

The formatted number (INV-2028-00001) is produced when displaying, from the stored year and number, so the format can be changed later without touching the data.

Costs and alternatives

Approach	Gap-free	Concurrent inserts	Use for
`AUTO_INCREMENT`	No	Fast	Row ids
Counter table in the same transaction (above)	Yes	Serial per sequence: others wait	Invoice numbers, receipts, per-company ticket numbers
`MAX(number) + 1` in the transaction	Only with locking (`FOR UPDATE`)	Serial, and easy to get wrong (a gap lock can deadlock)	Avoid
UUID	Not a sequence	Fast	Public ids, no ordering

Keep the transaction short. While it is open, nobody else can take a number from the same sequence. Do the slow work (PDF generation, emails) after the commit.
Number only when the invoice is finalized. Drafts do not need a legal number, and drafts that are deleted would leave holes.
Never delete a numbered invoice. Cancel it with a credit note, so that the sequence stays complete.
A per-company sequence is just another counter row: key the counter table by (company_id, year) (Multi-Tenant Data).

dangerous

Do not use MAX()+1

SELECT MAX(number) + 1 followed by an INSERT gives the same number to two simultaneous requests. Use the counter row, which is locked by the UPDATE.