How to avoid race condition in findOrCreate() method?

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

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