Practical issue with MYSQL auto incremented primary keys

Question

I have written an accounting module for my java app recently.

The module is based on MYSQL tables and INNODB Engine. one of the requirements is of course a "running" ID for each invoice. I tried to generate the id using the auto_increment method and by using a sequence table, but in both I have the same problem: since the id is generated when I persist the invoice entity, if an error occurs during flushing of the persisted entity to the database, the auto_id is incremented, causing a "hole" in my series of invoices. I can of course drop this method and assign the new invoice with the maximum ID of previous invoices + 1, but I think this is a bad practice. What other methods can I use to ensure I don't have holes in my invoices series, assuming that once in a while an invoice can fail to save due to some validation issue.

Time and time again, people try to use auto_increment for what it's not for. Use another query that does sequential numbering for you, do not rely on auto_increment. Relying on auto_increment for any type of sequential-numbering is bad practice and completely wrong use of that type of column. — N.B., May 08 '12 at 09:32

score 1 · Answer 1 · answered May 08 '12 at 08:37

1

In spring framework there is something like

@Transactional(rollbackFor=RuntimeException.class)

so everything should be rollback should there be problem with the server call. I'm sure other frameworks has similar approach.

answered May 08 '12 at 08:37

Edwin Bautista

463
3
17

I really like your approach. I tried to find this call for my toplink persistence provide, but I don't seem to find any... maybe you can help me find something equivalent in toplink? – Vova May 08 '12 at 10:03
I think Toplink is a persistence layer, its the equivalent of hibernate if I am not mistaken, @Transactional is by practice put in the service layer so that if there are multiple database calls, such as in your case a generation of a sequence number then an update/insert on a table, if one of them fails both calls are rollback. What is your service layer? – Edwin Bautista May 08 '12 at 23:33
your are right, Toplink is for me same as hibernate... My business layer is (if I understand these terms correctly) is "JAX WS 2.2" I actually am just using what Netbeans provides as a template when creating a new RESTFull Web Service... BTW Sorry for the late reponse – Vova May 13 '12 at 05:25

score 0 · Answer 2 · answered May 08 '12 at 08:49

if your MySQL version > 5.0.2 then you could try to use triggers which will increment the value of the table column properly. But you should remember that delegating to the database the generation of invoice number, which is more related to business logic, I presume, is not a very good idea, because in failure case you may have some problems. So I'd rather advice you to generate it programmatically in your code.

score 0 · Answer 3 · answered May 08 '12 at 09:22

It's not bad practice in itself, but it might be worth having a different "traditional" numeric primary-key field to guarantee structural integrity of the tables, and have another field for the invoice number.

You can then populate that invoice number using different logic, either just a simple MAX+1 or perhaps by looking up from a keys table, to allow for different numbering sequences for different types of invoice.

For example:

CREATE TABLE `keys` (
    `id` INT NOT NULL auto_increment,
    `type` VARCHAR(10) NOT NULL,
    `prefix` VARCHAR(10) NOT NULL,
    `value` INT(10) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
);

INSERT INTO `keys` (`type`, `prefix`) VALUES
('Sales Receipt', 'SRI'),
('Sales Invoice', 'SIN'),
('Sales Refund', 'SRF');

Then in your (pseudo)-code you can do

Database.BeginTransaction;
NewInvNum = Database.Query("SELECT `value` FROM `keys` WHERE `type` = 'SIN'");
MyInvoice.InvoiceNumber = NewInvNum;
Database.SaveInvoice(MyInvoice);
Database.Query("UPDATE `keys` SET `value` = {0} WHERE `type` = 'SIN'", NewInvNum+1);
Database.CommitTransaction;

The transaction (or some other concurrency-protection anyway) is important, so that when more than one invoice is in the process of being created, they don't get the same number.

You will **have** to lock the key table (or at least the row for that key) during the *whole* transaction that creates the invoice. Otherwise this will not generate unique numbers — , May 08 '12 at 09:34
I actually already have such a table - the sequence table. Its just that I use it not explicitly as you suggest but by setting my generation type to @TableGeneration. I rather not use a seperate table for keys as it can cause some differences between the two tables (for example after restoring a backup) — Vova, May 08 '12 at 09:38
If you're restoring bits of databases from backups, I think you'll have bigger problems than just an out-of-sync keys table. — Cylindric, May 08 '12 at 10:28

Practical issue with MYSQL auto incremented primary keys

3 Answers3