Techiio-author
Started by Peter DanielsSep 25, 2021

Open
How to insert into multiple table using JPA @Query only?

1 VIEWES 0 LIKES 0 DISLIKES SHARE
0 LIKES 0 DISLIKES 1 VIEWES SHARE

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

Techiio-commentatorNeel Dey replied 4 months ago0 likes0 dislikes

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.

You must be Logged in to reply
Techiio-logo

Techiio is on the journey to build an ocean of technical knowledge, scouring the emerging stars in process and proffering them to the corporate world.

Follow us on:

Subscribe to get latest updates

You can unsubscribe anytime from getting updates from us
Developed and maintained by Wikiance
Developed and maintained by Wikiance