Database Deadlock

From: Oracle <ylew_at_my.lhsgroup.com>
Date: Mon, 20 Sep 1999 11:06:54 +0800
Message-ID: <37e5a3d6@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 Sun Sep 19 1999 - 22:06:54 CDT