Re: How to avoid race condition in findOrCreate() method?

Nikita Tovstoles wrote:
> Hi,
> 
> I'd appreciate guidance on how to write a safe findOrCreate method. I'd
> like to write a method that, given a natural key:
> -returns POJO representing a row (if one exists), or
> -creates and inserts a new row and returns the object.
> 
> The constraints are:
> -app is running on multiple appservers (thus making method
> 'syncrhonized' won't help)
> -app logic allows for multiple transactions to attempt this method
> w/same argument at the same time
> -DB is running under REPEATABLE_READ TX iso level 
> Problem:
> Given two DB transactions T1, and T2 and the same natural key, the
> following race condition is possible:
> 
> T1: BEGIN TX;
> 
> T2: BEGIN TX;
> 
> T1: SELECT w/ name = "joe"; //returns nothing
> 
> T2: SELECT w/ name = "joe"; //returns nothing
> 
> T1: INSERT w/ name = "joe";
> T1: COMMIT; //row inserted
> 
> T2: INSERT w/ name = "joe";
> T2: COMMIT; //constraint violation
> 
> The only solution I thought of thus far is to:
> -lock entire table
> -attempt an insert (if succeeds you're done)
> -else detect (somehow) unique constraint (from exception)
> -perform a SELECT
> 
> But this strikes me as non-scalable, and I'm wondering whether there is
> a better way.
> 
> The full post can be found here:
> 
> http://forum.hibernate.org/viewtopic.php?t=968646
> 
> thanks a lot,
> -nikita
> 

That's normal behavior.  Handle the constraint violation exception
however you want.  Either you do pessimistic locking and hurt
scalability, or you have something that's more scalable that you need to
handle rollbacks with.

-- 
Christopher G. Stach II


-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]