Concurrent DB operations

In one of the projects I worked, there is a persistence layer which provides the API in the form of createOrUpdate method. This method starts a transaction verifies weather any object exists with the same business key. If exists, it will update existing object otherwise it will insert a new one.

While performing DB update operations like create or insert in multiple threads, I found two kinds of issues

1. Phantom Read. When multiple threads making a decision of insert or update by performing DB lookup, two threads may declare insert but due to other thread, it leads to constraint violation errors.
   
2. Transaction dead lock. If the transaction is relatively large, if another transaction trying to insert same object it leads to deadlock.
   

Setting a database transaction isolation level to serializable or declaring these methods as serializable solves the problem but it will defeat the purpose of parallel threads.

Below are the two kinds of workarounds for this issue:

1. Re-submit failed transaction with random wait.
   
2. Lock and write model
   

The first one is very simple to implement. When ever a transaction is failed due to, wait for some random time and retry the transaction. Identifying the cases that can be retried is important for this solution. For all the failures, it will not make sense to retry. Only recoverable error cases like constraint violation and deadlock can be retried.

In the second model, we need to identify the business key which makes the entity unique in domain and track the locks on this key. Every thread before attempting create/update transaction, it needs to obtain the lock on business key. After completing the transaction, it needs to release the lock. Releasing lock notifies the threads which are waiting for the lock. This kind of granular locking may not be possible with every database, so to keep the application database independent, this works better.