Idempotent insertion

[Roy Phillips]

A pattern I often use in database access, similar to upsert, is to insert if the row doesn't exists else return the existing row.

Here's an example of my current implementation: I query for the row using its natural key, on which there is a unique constraint, if it's found, that row is returned, otherwise I insert a new row.

This is executed transactionally, and if two threads attempt the save action concurrently, causing a duplicate key error, I re-query to get the row that 'won', and return it.

Does anyone see any problems with this approach, or know of a better way to achieve this result?

(the magic number "23505" is the standardized JDBC error code for attempted duplicate key insertion - I don't use a raw string in my code, I have an appropriate constant)

  def idemInsert(event: DonationEvent) = {

   val action = findQuery(event) flatMap {

     case Some(existing) =>

       DBIO.successful(existing)

     case None =>

       saveAction(event)

   }

   db.run(action.transactionally)

     .map(Some(_))

     .recoverWith {

       case e: PSQLException if e.getSQLState == "23505" =>

         db.run(findQuery(event))

     }

 }

~