How to use Linq to Sql as a Serial Number Generator to avoid Gaps? -

have created following linq sql transaction try , create invoices number without gaps. assuming 2 tables:

table 1: invoicenumbers. - columns id, serialnumber, increment - example: 1, 10001, 1

table 2: invoices. - columns: id, invoicenumber, name - example: 1, 10001, "bob smith"

        dim db new invoices.invoicesdatacontext         dim lastinvoicenumber = (from n in db.invoicenumbers order n.lastserialnumber descending                           select n.lastserialnumber, n.increment).first         dim nextinvoicenumber integer = lastinvoicenumber.lastserialnumber + lastinvoicenumber.increment         dim newinvoicenumber = new invoices.invoicenumber {.lastserialnumber = nextinvoicenumber, .increment = lastinvoicenumber.increment}         dim newinvoice = new invoices.invoice {.invoicenumber = nextinvoicenumber, .name = "test" + nextinvoicenumber.tostring}         db.invoicenumbers.insertonsubmit(newinvoicenumber)         db.invoices.insertonsubmit(newinvoice)     db.submitchanges() 

all works fine is possible using method 2 users might pick same invoice number if hit transaction @ same time? if so, there better way using linq sql?

gaps in sequences inevitable when dealing transactional databases.

first, cannot use select max(id)+1 because may give same id 2 transactions execute @ same time. means have use database native auto-increment column (mysql, sqlite) or database sequence (postgresql, mssql, oracle) obtain next available id.

but using auto-increment sequence not solve problem. imagine have 2 database connections started 2 parallel transactions @ same time. first 1 acquired id auto-increment sequence , became used value +1. 1 nanosecond later, second transaction acquired next id, +2. imagine first transaction rolled reason (encountered error, code decided abort it, program crashed - name it). after that, second transaction committed id +2, creating gap in id numbering.

but if number of such parallel transactions more 2? cannot predict, , cannot tell running transactions reuse ids abandoned.

it theoretically possible reuse abandoned ids. however, in practice prohibitively expensive on database, , creates more problems when multiple sessions try same thing.

tdlr: stop fighting it, gaps in used ids normal.