Hello,

We are using Spring and Hibernate and we are having problem with implementing a quite simple algorithm so that it is thread safe and also load-balancing safe.

This is the algorithm:
1. Read a record
2. If record does not exist:
2a. Execute a business logic
2b. Create the record

Alternate algorithm that would also work for us:
1. Read a record
2. If record does not exist:
2a. Create record
2b. Execute a business logic
2c. Update the record

The record has a particular foreign key column so that we are not reading by primary key but by a foreign key:

select * from TABLE where col1 = N

The problem we are having is that if two threads or two load-balanced nodes execute our implementation of this algorithm at the same moment, then both threads or nodes "think" that the record does not exist and both of them are attempting to create the record - ending up with two identical records in the database, and more importantly, the business logic is being executed twice (and was supposed to be executed only once).

We investigated ways of locking the entire table for the time of execution of this implementation. However, users in forum posts seem to discourage others from doing it. When our existing implementation of this algorithm was executed in a single non-readOnly transaction with Serializable isolation level, we were getting a deadlock where both threads were waiting for each other throwing exception after 3 attempts.

Please let me know how an appropriate implementation of this algorithm should look.

Thank you