Laravel lockForUpdate and invoice number generation

Question

I have a multi tenanted app where each user can generate invoices / credits.

I would like to generate an auto incrementing number by each user invoice or user credit while still keeping the id column used in Laravel relationships.

I want to auto increment the number column based on user_id and type.

My Invoices table:

id    number   biller_id   type      ...
1     1        1           invoice   
2     2        1           invoice
3     1        1           credit
4     1        2           invoice

So I end up with:

Biller 1 will have invoice numbers 1, 2, 3... and credit numbers 1, 2, 3...

And same for each user.

I have not been successful in locking the table for each creation event so that other transactions do not access their last invoice number, so I end up with repeated invoice / credit numbers for each user. I can see this from running my seed.

I have tried the following without success:

In the boot method of my Invoice class:

/**
 * Boot Method of class
 */
protected static function boot()
{
    /**
     * When creating the invoice, create its "number"
     */
    static::creating(function ($obj) {

        $lastTransaction = Invoice::where('biller_id', $obj->biller_id)
            ->where('type', $obj->biller_id)
            ->orderBy('created_at', 'DESC')
            ->lockForUpdate()
            ->first();

        $nextId = 1;
        if ($lastTransaction) {
            $nextId = $lastTransaction->number + 1;
        }

        $obj->number = $nextId;

    });

    parent::boot();
}

Also as a static createWithlock method (and I remove the lockForUpdate from the creating method):

public static function createWithLock($invoiceData = null)
{
    if (! $invoiceData) {
        return [
            'type' => 'error',
            'value' => 'No invoice data supplied!',
        ];
    }

    DB::beginTransaction();

    try {
        // Lock Invoices table to ensure correct creation of invoice number
        DB::select(DB::raw('LOCK TABLES invoices WRITE'));

        self::create($invoiceData);

        DB::select(DB::raw('UNLOCK TABLES'));

    } catch (\Exception $e) {

        DB::rollBack();

        return [
            'type' => 'error',
            'value' => $e->getMessage(),
        ];
    }

    DB::commit();

    return [
        'type' => 'success',
        'value' => 'Invoice created successfully.',
    ];
}

I get repeated number values for the combinations. Any suggestions appreciated on how to lock the table during creation process to stop duplicates.

Why do you need an auto incrementing invoice number? Unless this is a legal requirement, just generate a unique id, do not make your life more difficult! — Shadow, Jun 07 '20 at 23:40
Must be a really serious one, if you want to lock a table for this. You should have a chat with your client and explain that this is not such a good idea. I really suggest you read this answer to the same question asked by somebody else: https://stackoverflow.com/a/34572959/5389997 — Shadow, Jun 08 '20 at 00:03

score 0 · Answer 1 · answered Jun 08 '20 at 06:17

Your "for update" lock must be run inside a transaction. I cannot see from your code, that it is. So both the ->lockForUpdate() and the creation of the new invoice must be inside the same transaction.

I often find that Atomic Locks are a lot easier to use, and will take care of more actions (including if you calculate an ID in your code that is parsed back to the database layer after some small delay).

Atomic Locks are also easily added as a middleware (that can later easily be added to more endpoints, that locks the same object).

Laravel lockForUpdate and invoice number generation

1 Answers1