How do I ensure data consistency in this concurrent situation?

Question

The problem is this:

• I have multiple competing threads (100+) that need to access one database table
• Each thread will pass a String name - where that name exists in the table, the database should return the id for the row, where the name doesn't already exist, the name should be inserted and the id returned.
• There can only ever be one instance of name in the database - ie. name must be unique

How do I ensure that thread one doesn't insert name1 at the same time as thread two also tries to insert name1? In other words, how do I guarantee the uniqueness of name in a concurrent environment? This also needs to be as efficient as possible - this has the potential to be a serious bottleneck.

I am using MySQL and Java.

Thanks

Answer 1

Create a unique constraint on name column in database.

Answer 2

Add a unique constraint for the name column.

Answer 3

Assuming there is a unique constraint on the name column, each insert will acquire a lock. Any thread that attempts to insert it a second time concurrently will wait until the 1st insert either succeeds or fails (tx commit or rolls back).

If the 1st transaction succeeds, 2nd transaction will fail with with a unique key violation. Then you know it exists already.

If there is one insert per transaction, it'ok. If there are more than 1 insert per transaction, you may deadlock.

Each thread will pass a String name - where that name exists in the table, the database should return the id for the row, where the name doesn't already exist, the name should be inserted and the id returned.

So all in all, the algo is like this:

1 read row with name
   2.1 if found, return row id
   2.2 if not found, attempt to insert
      2.2.1 if insert succeeds, return new row id
      2.2.2 if insert fails with unique constraint violation
          2.2.2.1 read row with name
          2.2.2.2 read should succeed this time, so return row id

Because there can be a high contention on the unique index, the insert may block for some time. In which case the transaction may time out. Make some stress test, and tune the configuration until it works correctly with your load.

Also, you should check if you get a unique constraint violation exception or some other exception.

And again, this works only if there is one insert per transaction, otherwise it may deadlock.

---

Also, you can try to read the row at step 1 with "select * for update". In this case, it waits until a concurrent insert either commits or succeeds. This can slightly reduce the amount of error at step 2.2.2 due to the contention on the index.