Re: Database Deadlock

Instead of using a pessimistic locking strategy, can you use an optimistic strategy instead? - I used this approach on a credit- card processing project a few years back and it worked very well.

Just a suggestion

Graham

--
Empowerment - delegating the responsibility but not the authority.

Opinions expressed do not necessarily reflect those of Abbott Laboratories.

Oracle wrote in message <37e5a3d6_at_news.lhsgroup.com>...
>Hi,
>
>I am currently running a program which will execute 6 child processes. All
>these child processes will then call a store procedure to SELECT/UPDATE a
>particular row. This program is a backend problem which will read/SELECT
>information from customer table, do some processing then UPDATE the new
>information back to the customer table. While the backend process is
>executing the frontend, user interface program might be executing as well,
>to enter information, Just like a banking system.
>
>It seems that one of the process try to SELECT from the table when another
>process try to UPDATE to the table, which causes deadlock problem.
>
>Below is the error from the program.
>SQL error in function 'bch_GetCustPackage':
>Last SQL statement:
>SELECT BCH_PACKAGE_NUM, CUSTOMER_ID FROM CUST_BCH_PROCESS WHERE BCH_PA
>at or near line number: 162
>SQL return code : -60
>SQL error message :
>ORA-00060: deadlock detected while waiting for resource
>
>SQL error in function 'bch_GetCustPackage':
>Last SQL statement:
>SELECT BCH_PACKAGE_NUM, CUSTOMER_ID FROM CUST_BCH_PROCESS WHERE BCH_PA
>at or near line number: 173
>SQL return code : -1002
>SQL error message :
>ORA-01002: fetch out of sequence
>
>The following is part of the store procedure:
> CURSOR locGetPackage
> IS
>SELECT BCH_PACKAGE_NUM
> FROM CUST_BCH_PROCESS
> WHERE BCH_PACKAGE_NUM > pionMinPackageNum
> AND PROCESSED_BY_BCH = piosProccessedByFlag
> AND BILLCYCLE = piosBillcycle
> AND CONTROL_GROUP = piosControlGroup
> AND BILL_INFORMATION = piosBillInformation
> ORDER BY BCH_PACKAGE_NUM, CUSTOMER_ID;
>
> CURSOR locLockPackage ( pconPackageNum
> CUST_BCH_PROCESS.BCH_PACKAGE_NUM%TYPE )
> IS
>SELECT BCH_PACKAGE_NUM
> FROM CUST_BCH_PROCESS
> WHERE BCH_PACKAGE_NUM = pconPackageNum
> AND PROCESSED_BY_BCH = piosProccessedByFlag
> AND BILLCYCLE = piosBillcycle
> AND CONTROL_GROUP = piosControlGroup
> AND BILL_INFORMATION = piosBillInformation
> FOR UPDATE;
>
> lcrGetPackage locGetPackage%ROWTYPE;
> lcrLockPackage locLockPackage%ROWTYPE;
>
> BEGIN
> WHILE TRUE
> LOOP
> IF NOT locGetPackage%ISOPEN
> THEN
> OPEN locGetPackage;
> END IF;
>
> FETCH locGetPackage INTO lcrGetPackage;
>
> IF locGetPackage%FOUND
> THEN
> -- now we try to lock the fetched row. If it works
> -- we have won. Otherwise we must make another try.
>
> IF NOT locLockPackage%ISOPEN
> THEN
> OPEN locLockPackage( lcrGetPackage.BCH_PACKAGE_NUM );
> END IF;
>
> FETCH locLockPackage INTO lcrLockPackage;
>
> IF locLockPackage%NOTFOUND
> THEN
> -- Another process must have grabbed the package
> -- and changed its status
> ROLLBACK;
> CLOSE locLockPackage;
> CLOSE locGetPackage;
> ELSE
> -- great. now we can update all entries for this
> -- package
> UPDATE CUST_BCH_PROCESS
> SET PROCESSED_BY_BCH = 'W',
> BCH_PROCESS = pionBCHProcessId
> WHERE BCH_PACKAGE_NUM = lcrGetPackage.BCH_PACKAGE_NUM;
>
> --- DBMS_OUTPUT.put_line('update ' || SQL%ROWCOUNT);
> COMMIT;
>
> poonResPackageNum := lcrGetPackage.BCH_PACKAGE_NUM;
>
> CLOSE locLockPackage;
> CLOSE locGetPackage;
> return;
> END IF;
>
> ELSE
> -- it seems that there is nothing to do ...
>
> poonResPackageNum := -1;
> CLOSE locGetPackage;
> ROLLBACK;
> return;
>
> END IF;
> END LOOP;
>
>
>
>
Received on Wed Sep 22 1999 - 14:58:32 CDT