CODEWITHSUNDEEP
c#sql-serverentity-framework
chris
chris

Reputation: 1216

How to Restart Column Increment Automatically Per ID?

We are developing a multi-tenant system used by small businesses and need to generate invoices for their customers. Basically each tenant for obvious reasons want their own sequence of invoice numbers generated independently from each other. So the first tenant can have invoice 1, 2, 3 and the second tenant can have the same because they are independent business and know nothing about each other.

We are using entity framework 7 as our ORM and SQL 2014 as our database. We need a way to generate these invoice numbers without the possibility of accidental duplication for the same tenant under heavy concurrent loads. Initially in EF we were just going to take the max of the invoice column where tenant = current tenant ID and then add 1 to it but after stress testing it created alot of duplicated invoice numbers for that tenant. Are triggers better for this? Sequences? I'm not sure where to go next with this.

Here's a simplified table layout describing our situation. Notice the last table how the invoice number needs to restart per tenant. This is what we're trying to achieve

+----------+-------------+
| TenantID | TenantName  |
+----------+-------------+
|        5 | ABC Company |
|        6 | XYZ Corp    |
+----------+-------------+
+----------+------------+----------------+
| TenantID | CustomerID |  CustomerName  |
+----------+------------+----------------+
|        5 |          2 | Alpa Customer  |
|        5 |          5 | Beta Customer  |
|        6 |          3 | Delta Customer |
|        6 |          4 | Omega Customer |
+----------+------------+----------------+

    +----------+------------+-----------+-------------------------------------------------------+
| TenantID | CustomerID | InvoiceID | InvoiceNumber(this one needs to restart per tenant) |
+----------+------------+-----------+-------------------------------------------------------+
|        5 |          2 |         1 |                                                     1 |
|        5 |          2 |         7 |                                                     2 |
|        5 |          5 |         2 |                                                     3 |
|        5 |          5 |         4 |                                                     4 |
|        5 |          5 |         5 |                                                     5 |
|        6 |          3 |         8 |                                                     1 |
|        6 |          4 |         3 |                                                     2 |
|        6 |          4 |         6 |                                                     3 |
+----------+------------+-----------+-------------------------------------------------------+

ANSWER BASED ON @ERIKE I ended up adding a unique constraint around TenantID and InvoiceNumber, then I take the max of the invoice number and add one to it and try the insert. This is wrapped around a do while loop in C#. Whenever a unique constraint error is raised, it retries again

bool retryInsert;
do
{
    try
    {
        retryInsert = false;
        var invNo = (db.tbl_Invoice
            .Where(t => t.TenantID == invoice.TenantID)
            .Max(t => t == null ? 0 : t.InvoiceNumber)
            ) + 1;

        invoice.InvoiceNumber = invNo;
        db.tbl_Invoice.Add(invoice);
        db.SaveChanges();
    }

    catch (DbUpdateException ex)
    {
        retryInsert = false;
        var sqlexception = ex.InnerException as SqlException;
        if (sqlexception != null)
        {
            if (sqlexception.Errors.OfType<SqlError>()
                .Any(se => se.Number == 2627))
            {
                retryInsert = true;
            }

            else throw ex;
        }
    }
} while (retryInsert);

return invoice;

Upvotes: 3

Views: 329

Answers (2)

ErikE
ErikE

Reputation: 50241

In order to support the highest transactions per second, I believe that your best answer is to put a unique index on (tenant ID, invoice ID) in the invoice table and do the concurrent insert, then in the case of unique key violation, retry.

I base this on an article I read some years ago about achieving the highest throughput in exactly such a scenario.

In any case, I would strongly recommend against having one table per tenant. You might look into sequences, but I'm not sure if having one sequence per tenant is reasonable.

A "last invoice" table with one row for each tenant could work:

UPDATE dbo.TenantLastInvoice
SET
   LastInvoiceID = LastInvoiceID + 1,
   @InvoiceID = InvoiceID
WHERE TenantID = 123;

I am not 100% sure whether you need a locking hint, you could consider ROWLOCK, UPDATELOCK, and if all else fails, READPAST (and retrying if no update occurs).

Do not expect to be able to perfectly avoid skipping IDs occasionally. It seems a hard fact that one either tolerates the chance of collisions (completely unacceptable) or tolerates skipping IDs in certain edge cases (annoying, but not the end of the world).

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

I would create a separate table to hold the next Invoice Number. When you add a new invoice, make sure to update the next invoice number. Keep everything in a transaction and you will be in pretty good shape.

The remaining issue (multiple threads trying to update the table at the same time) can be managed a few ways. My favorite, at least to start with, is to have a Timestamp column in the next invoice number table and insure the timestamp hasn't changed as part of the update.

Upvotes: 1

Related Questions