CODEWITHSUNDEEP
javamysqlconcurrencydata-consistency
MalcomTucker
MalcomTucker

Reputation: 7477

How do I ensure data consistency in this concurrent situation?

The problem is this:

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

Upvotes: 2

Views: 2098

Answers (3)

ewernli
ewernli

Reputation: 38615

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.

Upvotes: 7

lajuette
lajuette

Reputation: 1017

Add a unique constraint for the name column.

Upvotes: 2

Tadeusz Kopec for Ukraine
Tadeusz Kopec for Ukraine

Reputation: 12403

Create a unique constraint on name column in database.

Upvotes: 3

Related Questions