how to make sure 2 apps dont get the same row

Hi,

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??

Hi,

U can have checksum calculated using this OWA_OPT_LOCK package.

first u get the original checksum

DECLARE
v_rowid ROWID;
BEGIN
SELECT rowid
INTO v_rowid
FROM emp
WHERE empno = 1000;
DBMS_OUTPUT.PUT_LINE(owa_opt_lock.checksum('SCOTT','EMP', v_rowid));
END;

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)

v_rowid ROWID;
v_new_checksum NUMBER;
BEGIN
SELECT rowid
INTO v_rowid
FROM emp
WHERE empno = p_empno
FOR UPDATE;

v_new_checksum := owa_opt_lock.checksum('SCOTT','EMP', v_rowid);

IF v_new_checksum = p_old_checksum THEN
UPDATE emp
SET deptno = p_new_dept
WHERE rowid = v_rowid;
COMMIT;
ELSE
ROLLBACK;
RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
END IF;
END;

the above method is one of the ways....

have a look at this

http://www.oracle-base.com/Articles/...gMethods9i.asp

HTH

Srini

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:

http://otn.oracle.com/pls/tahiti/tah...&word=queueing

A transaction can be written in 2 ways - pessimistic and optimistic.
Your transaction type is pessimistic.
Change it to optimistic type.

For example,
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.