Two threads adding new rows at the same time - how to prevent?

Question

Hi all,

In my application, I have couple of threads that execute some logic. At the end they adding new row to some table.

Before adding the new row, they check if a previous entry with the same details does not already exist. If one found - they updating instead of adding.

The problem is when some thread A do the check, see that no previous entity with the same details exist, and just before he add a new row, the thread B search the DB for the same entity. Thread B see that no such entity exist so he add new row too.

The result is that there are two rows with the same data in the table.

Note: no table key violated, because the thread get the next sequence just before adding the row, and the table key is some ID that does not related to the data.

Even if I will change the table key so it will be a combination of the data, It will prevent two rows with the same data, but will cause a DB error when the second thread will try to add the row.

Thank you in advance for the help, Roy.

Answer 1

You need to wrap the calls to check and write the row in a critical section or mutex.

With a critical section, interrupts and thread-switching are disabled while you perform the check and write, so both threads can't write at once.

With a mutex, the first thread would lock the mutex, perform its operations, then unlock the mutex. The second thread would attempt to do the same but the mutex lock would block until the first thread released the mutex.

Specific implementations of critical section or mutex functionality would depend on your platform.

Answer 2

You should be using a queue, possibly blocking queue. Threads A and B (producers) would add objects to the queue and another thread C (consumer) would poll the queue and remove the oldest object from the queue persisting it to the DB. This will prevent the problem when both A and B in the same time want to persist equal objects

Answer 3

I think this is a job for SQL constraints, namely "UNIQUE" on the set of columns that have the data + the appropriate error handling.

Answer 4

You speak of "rows" so presumably this is a SQL database?

If so, why not just use transactions?

(Unless the threads are sharing a database connection, in which case a mutex might help, but I would prefer to give each thread a separate connection.)

Answer 5

Multithreading is always brain-fucking ^^.

Main thing to do is to delimitate the critical ressources and critical operations.

• Critical ressource : your table.
• Critical operation : adding yes, but the whole procedure

You need to lock access to your table from the beginning of the check, until the end of the add. If a thread attempt to do the same, while another is adding/checking, then he waits until the thread finish its operation. As simple as that.

Answer 6

I would recommend avoid locking in the client layer. Synchronized only works within one process, later you may scale so that your threads are across serveral JVMs or indeed machines.

I would enforce uniquiness in the DB, as you suggest this will then cause an exception for the second inserter. Catch that exception and do an update if that's the business logic you need.

But consider this argument:

Sometimes either of the following sequences may occur:

A insert Values V_A, B updates to values V_B.

B insert V_B, A updates to V_A.

If the two threads are racing either of these two outcomes V_A or V_B is equally valid. So you can't distinguish the second case from A inserts V_A and B just fails!

So in fact there may be no need for the "fail and then update" case.

Answer 7

Most database frameworks (Hibernate in Java, ActiveRecord etc in Ruby) have a form of optimistic locking. What this means is that you execute each operation on the assumption that it will work without conflict. In the special case where there is a conflict, you check this atomically at the point where you do the database operation, throw an exception, or error return code, and retry the operation in your client code after requerying etc.

This is usually implemented using a version number on each record. When a database operation is done, the row is read (including the version number), the client code updates the data, then saves it back to the database with a where clause specifying the primary key ID AND the version number being the same as it was when it was read. If it is different - this means another process has updated the row, and the operation should be retried. Usually this means re-reading the record, and doing that operation again on it with the new data from the other process.

In the case of adding, you would also want a unique index on the table, so the database refuses the operation, and you can handle that in the same code.

Pseudo code would look something like

do {
  read row from database
  if no row {
     result_code = insert new row with data
  } else {
     result_code = update row with data
  }
} while result_code != conflict_code

The benefit of this is that you don't need complicated synchronization/locking in your client code - each thread just executes in isolation, and uses the database as the consistency check (which it is very quick, and good at). Because you're not locking on some shared resource for every operation, the code can run much faster.

It also means that you can run multiple separate operating system processes to split the load and/or scale the operation over multiple servers as well without any code changes to handle conflicts.

Answer 8

You need to perform the act of checking for existing rows and then updating / adding rows inside a single transaction.

When you perform your check you should also acquire an update lock on those records, to indicate that you are going to write to the database based on the information that you have just read, and that no-one else should be allowed to change it.

In pseudo T-SQL (for Microsoft SQL Server):

BEGIN TRANSACTION
SELECT id FROM MyTable WHERE SomeColumn = @SomeValue WITH UPDLOCK
-- Perform your update here
END TRANSACTION

The update lock wont prevent people reading from those records, but it will prevent people from writing anything which might change the output of your SELECT

Answer 9

Thank you all for the answers!

Roy.