I have a table that contains invoices for several companies, each company needs to have their own incrementing invoice number system.
id | invoiceId | companyId
--------------------------
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 1 | 3
I was hoping to achieve this with a unique compound key similar to this approach for MyISAM outlined here, but it seems it is not possible with InnoDB.
I need to return the new ID immediately after insertion and have concerns about creating a race condition if I try and achieve this with PHP.
Is my best option to create a trigger and if yes what would that look like? I have no experience with triggers and my research into using an after insert trigger has me worried with this quote from the MariaDB documentation:
RESTRICTIONS
You can not create an AFTER trigger on a view. You can not update the NEW values. You can not update the OLD values.
Thanks for any advice
You need to add a unique index besides getting your next value. The next value is best gotten by querying the table with a trigger or by some procedure within a transaction. The remark of trigger on a view is not relevant in that case.
The unique index is on companyId,invoiceId is required to prevent two insert processes running on the same company adding an invoice, which then can end up both with the same invoiceId. Even better is when you switch to InnoDB so you can use transactions: Then 2 processes started at virtually the same time can benefit from transaction isolation with as result that they will be serialized and you get 2 unique incrementing invoice ids returned without having to handle the unique index exception in your code.
As far as I know, mysql's last_id is connection based, and not global and shared between processes.
using this simple script I've validated my concerns
(note this is codeigniter syntax, but it's relatively easy to understand)
I accessed the following function twice, within 10 seconds of each other(in different browser windows)
function test(){
$arr['var'] = rand(0,100000000);
$this->db->insert('test_table',$arr);
sleep(30);
$id = $this->db->insert_id();
var_dump($id);
}
Interesting to note, instead of getting "2" as a response in both of them, I've gotten 1 and two respectfully. This makes even more sense when you look at the underlying function
function insert_id()
{
return @mysqli_insert_id($this->conn_id);
}
This solves the returned ID, Your race condition is the product of the underlying query, which is basically "Select MAX(invoiceId ) WHERE companyID = X" and add +1 to that, and insert it.
This should be possible with a table lock before insert, however this depends on how many times per second you expect this table to get updated.
note, on persistent connection the last_insert_id might work differently, I haven't tested it.