I am using JPA @Query annotation . Want to insert into two table using simple oracle query in JPA. How can I achieve this .
Don't want to use begin clause . Want single query to insert into two table.
Don't want to used one to many or any relationship due to some restriction.
1 Replies
I believe you might be facing this issue, which is pretty likely especially if both transactions attempt to insert the same SKUs.
If that's the case, I can think of two ways to mitigate it:
Partial solution: try sorting the SKUs in List<CreateSkuRequest> SKUs
by SKU.code
and (if that's not enough) using saveAndFlush()
to store them, ensuring the order of insertion. This should eliminate the circular wait, meaning that now, at least one of the transactions should succeed (the other will probably get a unique constraint violation)
Full solution: if you prefer both transactions to succeed, you would have to acquire a table-level lock for the SKU table. You should be able to do this using a custom update query:
@Query(value = "LOCK TABLE SKU IN EXCLUSIVE MODE", nativeQuery = true)
@Modifying
void lockTable();
then, just call the method as the first operation inside createSku
. Note that this might turn out to be only marginally more efficient than putting the transactions in a queue, so if I were you, I'd probably still just go with that approach.