Best way to generate (and save) incremental invoice numbers in a multi-tenant MySQL database

I have found two different ways to, first, get the next invoice number and, then, save the invoice in a multi-tenant database where, of course, each tenant will have his own invoices with different incremental numbers.

My first (and actual) approach is this (works fine):

Add a new record to the invoices tables. No matter the invoice number yet (for example, 0, or empty)
I get the unique ID of THAT created record after insert
Now I do a "SELECT table where ID = $lastcreatedID **FOR UPDATE**"
Here I get the latest saved invoice number with "SELECT @A:=MAX(NUMBER)+1 FROM TABLE WHERE......"
Finally I update the previously saved record with that invoice number with an "UPDATE table SET NUMBER = $mynumber WHERE ID = $lastcreatedID"

This works fine, but I don't know if the "for update" is really needed or if this is the correct way to do this in a multi-tenant DB, due to performance, etc.

The second (and simpler) approach is this (and works too, but I don't know if it is a secure approach):

INSERT INTO table (NUMBER,TENANT) SELECT COALESCE(MAX(NUMBER),0)+1,$tenant FROM table WHERE....
That's it

Both methods are working, but I would like to know the differences between them regarding speed, performance, if it may create duplicates, etc.

Or... is there any better way to do this?

I'm using MySQL and PHP. The application is an invoice/sales cloud software that will be used by a lot of customers (tenants).

Thanks

Solution

Regardless of if you're using these values as database IDs or not, re-using IDs is virtually guaranteed to cause problems at some point. Even if you're not re-using IDs you're going to run into the case where two invoice creation requests run at the same time and get the same MAX()+1 result.

To get around all this you need to reimplement a simple sequence generator that locks its storage while a value is being issued. Eg:

CREATE TABLE client_invoice_serial (
  -- note: also FK this back to the client record
  client_id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
  serial INTEGER UNSIGNED NOT NULL DEFAULT 0
);

$dbh = new PDO('mysql:...');
/* this defaults to 'on', making every query an implicit transaction. it needs to
be off for this. you may or may not want to set this globally, or just turn it off
before this, and back on at the end. */
$dbh->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
// simple best practice, ensures that SQL errors MUST be dealt with. is assumed to be enabled for the below try/catch.
$dbh->setAttribute(PDO::ATTR_ERRMODE_EXCEPTION,1);

$dbh->beginTransaction();
try {
    // the below will lock the selected row
    $select = $dbh->prepare("SELECT * FROM client_invoice_serial WHERE client_id = ? FOR UPDATE;");
    $select->execute([$client_id]);

    if( $select->rowCount() === 0 ) {
        $insert = $dbh->prepare("INSERT INTO client_invoice_serial (client_id, serial) VALUES (?, 1);");
        $insert->execute([$client_id]);
        $invoice_id = 1;
    } else {
        $invoice_id = $select->fetch(PDO::FETCH_ASSOC)['serial'] + 1;
        $update = $dbh->prepare("UPDATE client_invoice_serial SET serial = serial + 1 WHERE client_id = ?");
        $update->execute([$client_id])
    }
    $dbh->commit();
} catch(\PDOException $e) {
    // make sure that the transaction is cleaned up ASAP, then let the exception bubble up into your general error handling.
    $dbh->rollback();
    throw $e; // or throw a more pertinent error/exception of your choosing.
}
// both committing and rolling back will release the lock

At a very basic level this is what MySQL is doing in the background for AUTOINCREMENT columns.