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??
U can have checksum calculated using this OWA_OPT_LOCK package.
first u get the original checksum
WHERE empno = 1000;
then u can write a procedure
update_dept(p_empno IN emp.empno%TYPE,
p_old_checksum IN NUMBER,
p_new_dept IN emp.deptno%TYPE)
WHERE empno = p_empno
v_new_checksum := owa_opt_lock.checksum('SCOTT','EMP', v_rowid);
IF v_new_checksum = p_old_checksum THEN
SET deptno = p_new_dept
WHERE rowid = v_rowid;
RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
the above method is one of the ways....
have a look at this
Advanced Queueing can do this for you. Basically you generate data into a table from producers, and there are consumers who consume the rows, and never walk on each other.
Check the docs:
A transaction can be written in 2 ways - pessimistic and optimistic.
Your transaction type is pessimistic.
Change it to optimistic type.
select the row and store inv_stat column value in a variable.
Then in the update statement add a where clause and the check the for the old value. If the update succeeds, then it means no transaction has changed the row. If it fails, then raise an error.
Click Here to Expand Forum to Full Width