spring boot - Locking mechanism for Multiple instances accessing and updating same field on table

Question

I have a spring-boot application that processes orders and are placed on three nodes and work simultaneously. For processing an order they have to go to the store table and get the last order id placed on that store. Its not the universal order id, but its just a count of orders placed from that store. Following is the table structure in question:

TABLE: Store

StoreNumber: String

storeOrderNum: int

StoreInfo: This is store information

Every instance will take the storeOrderNum and increase it by 1.

The update query we use is:

UPDATE store

SET store_order_num =  (SELECT CASE

WHEN store_order_num > 9998 OR store_order_num IS NULL THEN 1

ELSE store_order_num + 1

END

FROM store

WHERE store_num = #{store_num}) WHERE store_num = #{store_num}

I want to implement a locking mechanism, so that more than 1 instance can't read or write on the same column for a particular store at the same time. Functionality wise, if both instances come to the same update, one should wait until the other one is done. Any direction would be helpful. Thank you.

question from:https://stackoverflow.com/questions/65905796/locking-mechanism-for-multiple-instances-accessing-and-updating-same-field-on-ta

Answer 1

Waitting for answers