how to make sure 2 apps dont get the same row
I am tuning a legacy system which was written on Oracle 7.2 with Powerbuilber as a frontend.
We are developing and additional web based interface to the db and have upgraded to 8.1.7 for now.
The problem is that i have an "GoodsInventory" table. When a purchase is made I need to insure that the Powerbuilber (which I preety much cant change) and the new WEb App (made by us so can be changed)
pull one row of inventory at a time and data integrity is maintained.
I select the next available row of inventory and update it with the order details. i want to be sure only one app does this at a time (powerbiler/WEb app)
I have tried SELECT * FROM INVENTORY FOR UPDATE OF INV_STATUS NOWAIT
(INV_STAT is the status flag which i update from A (available) to S (sold) once the row is taken)
but that wont help. I was thinking is i could write a pl/sql block to allocate the next avalible row??
Any suggestions here??