I'm facing an issue of duplicated incremental field on a concurrency scenario.

I'm using EF as the ORM tool, attempting to insert an entity with a field that acts as a incremental INT field. Basically this field is called "SequenceNumber", where each new record before insert, will read the database using MAX to get the last SequenceNumber, append +1 to it, and saves the changes. 

Between the getting of last `SequenceNumber` and saving, that's where the concurrency is happening.

I'm not using `ID` for `SequenceNumber` as it is not a unique constraint, and may reset on certain conditions such as monthly, yearly, etc.

```
InvoiceNumber       | SequenceNumber | DateCreated
INV00001_08_14      | 1              | 25/08/2014
INV00001_08_14      | 1              | 25/08/2014 <= (concurrency is creating two SeqNo 1)
INV00002_08_14      | 2              | 25/08/2014
INV00003_08_14      | 3              | 26/08/2014
INV00004_08_14      | 4              | 27/08/2014
INV00005_08_14      | 5              | 29/08/2014
INV00001_09_14      | 1              | 01/09/2014 <= (sequence number reset)

```

Invoice number is formatted based on the `SequenceNumber`.

After some research I've ended up with these possible solutions, but want to know the best practice

1. Pessimistic Concurrency, locking the table from any reads until the current transaction is completed (not fancy of this idea as I guess performance will be of a great impact?)
2. Create a stored procedure solely for this purpose, does select and insert on a single statement as such concurrency is at minimum (would prefer a EF based approach if possible)

==============================

**EDIT**

Another solution that I've thought of is to make the **InvoiceNumber** a **Unique Constraint**, so on cases of duplicate entries it would throw the Unique Constraint Violation errors, catch it, and reattempt to get a new SequenceNumber and InvoiceNumber to reinsert to the DB.