how to manage concurrency for inserting in C#?

Question

Many optimistic concurrency examples refer to updates by using a database timestamp or flag.

However, I want to handle optimistic concurrency for INSERTS

To illustrate here is a fake scenario:

Multiple users can insert a journal entry at the same time, but allow only one journal entry is allowed per date.

Without any concurrency control since multiple users can create journal entries, I can end up with multiple journal entries on the same date.

How do I prevent this from happening at the application layer WITHOUT the use of the database (i.e. a database unique key constraint)

Answer 1

You could write an 'on insert' trigger to check the contents of the table, and then discard the insert if your criteria are not met. This is available for most RDBMS.

Answer 2

Whether or not you define an explicit unique key constraint, that's exactly what you're asking for.

You can write an IF block in your SQL code to check for the existence of a journal entry of the specified date.

But an explicit unique key constraint is going to give better performance.