DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to make sure 2 apps dont get the same row

  1. #1
    Join Date
    Jul 2003
    Posts
    1

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

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    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

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  


Click Here to Expand Forum to Full Width