How to convert sql query to Hibernate Criteria query?

JPA conditional insertion / RDBMS transactions isolation?

• I want to insert a record to an RDBMS table only if the table does not consist of any rows which are "similar" (according to some specific, irrelevant criteria) to the said row. I have a simple SELECT query for checking if any "similar" rows exist. I originally figured that to achieve my target, it'd be enough to run the SELECT query conditionally followed by the INSERT query, both together inside one transaction. However, I've been reading about different isolation levels, and it seems like in a concurrent environment, two threads might run the SELECT statement concurrently, and end up both inserting "similar" rows to the table, even though the SELECT+INSERT are in the same transaction. First question: I'm using JPA (over Hibernate) and a MySQL DB (using InnoDB engine for all my tables). What is the best way to achieve my goal? I'd obviously rather stay standard, but am willing to go native if needed. Second question: Assuming I don't mind "overriding" records with "similar" records, and I replace the SELECT statement with DELETE one - will it work as I intend it to then? Basically what I'm asking here is whether a DELETE+INSERT in one transaction is atomic, in the sense that if two threads run those simultaneously then no matter how they run, the table won't ever consist of both inserted entries after the two transactions are finished (On one hand, I'd be really surprised if it's not atomic in this sense, but on the other, it seems like this forces a table lock, doesn't it?). Thank you for your help!

• Answer:

  You are dealing with an instance of the phantom problem [1]. That means: one transaction does a predicate read, with a predicate that might match additional entities inserted or updated by a concurrent transaction. It is a non-repeatable read on table/class level. And indeed, this problem is usually solved by table locks. Theoretically you problem could be solved by a conditional insert/update qualified by a JPQL subquery (this type of query is called upsert). But JPQL only allows JPQL Update and Delete queries. Inserts can only be performed via the API. So your one option would be to acquire a table read lock in one transaction to prevent the other transaction from inserting a phantom. This requires using the Serializable isolation level and bare SQL access to MySQL [2]: LOCK TABLES my_table READ --Stuff happens UNLOCK TABLES Note, that acquiring table locks is very restrictive to concurrency and usually kills performance. So this is no practical approach. However, there is a second aproach, based on JPA's optimistic locking capabilities [3]. It works like this: Start a transaction. The insert transaction tries to read a similar entity providing the LockMode OPTIMISTIC_FORCE_INCREMENT. This causes the DBS to update the version column of the selected entities upon transaction commit. If the entitiy was concurrently read by the same logic, the transaction will abort with an OptimisticLockException. Which is what you want, since it indicates that another transaction tried to insert a similar entity and the abort prevented a duplicate. If your the first query did not return a matching entity insert the new entity. For illustration here is an example for the LockMode OPTIMISTIC_FORCE_INCREMENT. This approach works if a similar entity already exists. It does not work, if two concurrent transactions try to insert two new and seperate entities which are similar to each other but to no existing entity! To deal with this case the scheme has to be refined: Start a transaction. Insert the new entity without checking any condition. Select all entities similar to the one you inserted, using OPTIMISTIC_FORCE_INCREMENT. If it returns more than one entity abort, else commit. The reason this works is that transactions are allowed to generate in inconsistent database state for the duration of transaction since they run isolated from each other (the I in ACID). At commit time the database has to be consistent again (i.e. it must not contain similar entities), so the transaction either has to leave a consistent state (new entity has no similar one) or abort to return to the old consistent state. By selecting duplicates with OPTIMISTIC_FORCE_INCREMENT only one transaction can successfully insert an entity per equivalence class as defined by the similarity. Furthermore the scheme avoids allocating any table locks, so it is an efficient approach, too. [1] http://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads [2] http://dev.mysql.com/doc/refman/5.1/de/lock-tables.html [3] https://blogs.oracle.com/enterprisetechtips/entry/locking_and_concurrency_in_java