-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|