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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…