Best approach to distribute a key into multiple tables within a transaction

This is the case. I have a master table with an invoice number (int) and many tables with related data. When a invoice is made I increment the invoice number in the master table and then put the additional data, and the invoice number too, in the rest of the tables.

As everything is in a transaction (READ COMMITED), all changes are not phisically written until the this transaction is commited.

I know I'll have an error if some other transaction uses the same invoice number at the same time and commits first. I could retry with a new number but, is this the best approach ?

If there are many concurrent long invoices (transactions with a lot of data) there could be many many retries.

I'm thinking in a开发者_开发问答 way to reserve an invoice number quickly or locking it but I don´t know how....

If you use an auto_incrementing invoice id, your transaction engine should take care of this for you.

I.E. suppose the current highest invoice is 2, you insert a new one by adding a row to that table. Calling LAST_INSERT_ID() will give you the ID of the row you just inserted in that master table of invoices, which is 3. If at the same time another another transaction begins, it will get the number 4. Your transaction engine is smart enough to not double assign the ID. You can then use this ID to tag all of your data in the ancillary tables without worrying that it will be doubly assigned.

do this:

BEGIN TRANSACTION

INSERT INTO YourHeaderTable  (col1, col2,..) values (...)---where PK is identity
SELECT @newID=SCOPE_IDENTITY()

INSERT INTO Table2 VALUES (@newID,...)  --use the @newID from YourHeaderTable  
INSERT INTO Table3 VALUES (@newID,....) --use the @newID from YourHeaderTable  
...

COMMIT

and you will never have a problem with two concurrent users with the same new id. The database will manage the increment of the ID and concurrent users for you, making this error free and reliable. Anything you try to do other than this is nor only reinventing the wheel, but will be more complex, less reliable and more prone to error.

Use an IDENTITY column – that will guarantee a unique value. And you can (if you're using SQL Server 2005+) use the OUTPUT clause of the initial INSERT query to capture the ID value generated for your invoice row(s). If you're inserting multiple invoices you should capture additional columns from your insert to uniquely identify the invoices for inserting related rows in other tables.

Well, if you absolutely can't have holes (as per identity), then you are going to need to lock your custom id / seed table row until you are 100% sure that all of your inserts have committed. However, you'll also need to make sure any other concurrent accesses to this record are also SERIALIZABLE isolation level.

BEGIN TRAN
SELECT NextId FROM myKeysTable
WITH (XLOCK, SERIALIZABLE)
WHERE myTablePKName = 'SomeId'
...

This will hold an exclusive lock on the row, preventing other reads (and with the SERIALIZABLE proviso).

Alternatively, have a look at sp_getapplock - with examples here. Just ensure that there is only one way to access your custom keys table.

But this doesn't sound very scalable / performant.