Loading ...
Sorry, an error occurred while loading the content.

UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2

Expand Messages
  • pattabhi27
    Hello PSFTDBA group - We have PT 8.48, HRMS 9.0 up and running on Oracle 10.2.0.4 We have an issue, where the system hangs intermittently and none of the Users
    Message 1 of 3 , Sep 2, 2009
    • 0 Attachment
      Hello PSFTDBA group - We have PT 8.48, HRMS 9.0 up and running on Oracle 10.2.0.4

      We have an issue, where the system hangs intermittently and none of the Users can login and already logged in users see "Processing..Processing" message. Until we find the SID that blocks the DB and kill the same, the system will be in the same state.

      Upon further researching the issue - "Processing...Processing" is occuring due to a database deadlock and the culprit (blocking lock) session is "Inacitve" attempting the exact same trouble update of "UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2" causing the deadlock before the session goes inactive for some reason (timeout, user closing the window etc - we do not know how or why at this point)


      Also by looking at the App Server LOGS - with the Messages - WORKFORCE_SYNC and HCR_EM_EVENT the deadlock is occurring because the queue is divided into sub queues and there are parallel updates happening to UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2 Looks like These updates are happening without a commit and hence throwing ORA-00060 error on the App. Server LOGS.


      On the Application Server LOGS - we see the below error messages at that point of time:
      ---------------------------------------------------------------------
      [08/31/09 16:38:18 PDAVIS@172.24.135.37 (IE 6.0; WINXP) ICPanel](0) SQL error. Stmt #: 337 Error Position: 0 Return: 60 - ORA-00060: deadlock detected while waiting for resource
      PSAPPSRV.1749022 (1756) [08/31/09 16:38:18 PDAVIS@172.24.135.37 (IE 6.0; WINXP) ICPanel](0) Sam SqlError: UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2
      PSAPPSRV.1749022 (1756) [08/31/09 16:38:18 PDAVIS@172.24.135.37 (IE 6.0; WINXP) ICPanel](0) Publish of message WORKFORCE_SYNC failed. (119,9) HCR_PUBLICATION_RULES.PublishWorkforce.OnExecute Name:PublishWorkforce PCPC:595 Statement:17
      --------------------------------------------------------------------

      Any pointers / assistance is highly appreciated.

      Thank you.

      Regards,
      Ram Marupudi.
    • T
      According to note ID 656090.1: Symptoms When submitting messages on Tools 8.48 we are seeing Deadlocking in PSIBQUEUEINST. During stress testing for SAT we
      Message 2 of 3 , Sep 2, 2009
      • 0 Attachment

        According to note ID 656090.1:

        Symptoms


        When submitting messages on Tools 8.48 we are seeing Deadlocking in PSIBQUEUEINST.
        During stress testing for SAT we found deadlocking in the PSIBQUEUEINST table like the following

        17.24.48 11446.608369 SQL error. Stmt #: 337  Error Position: 0  Return: 60 - ORA-00060: deadlock detected while waiting for resource
        17.24.48    0.000088 Sam SqlError: UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2
        17.24.54    6.117268            Str=PopulateReport
        17.24.54    0.000011            SAT_ACKNOWLEDGEMENT:AcceptanceAcknowledgement=NULL
        17.24.54    0.001791 User ID=IDCAP027; Browser=IE/6.0; OpSys=WINNT;

        Cause

        Not Applicable

        Solution

          
        Several cases have reported for this issue, however in each case it was discovered that the cause was custom peoplecode calling a publish.  In each case a loop was calling the publish peoplecode using the same variable. Once this code was simplified the publication worked fine with no deadlocks.

        In one case the solution was two fold:
        1) Have only one active Integration Broker domain.  
        2) Below find the publish peoplecode that was changed
        BEFORE:
        %IntBroker.Publish(&MSG);

        AFTER:
        %IntBroker.Publish(&MSG, True); /* deferred publishing */


        From: pattabhi27 <pattabhi27@...>
        To: psftdba@yahoogroups.com
        Sent: Wednesday, September 2, 2009 12:51:14 PM
        Subject: PeopleSoft DBA Forum UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2

         


        Hello PSFTDBA group - We have PT 8.48, HRMS 9.0 up and running on Oracle 10.2.0.4

        We have an issue, where the system hangs intermittently and none of the Users can login and already logged in users see "Processing. .Processing" message. Until we find the SID that blocks the DB and kill the same, the system will be in the same state.

        Upon further researching the issue - "Processing. ..Processing" is occuring due to a database deadlock and the culprit (blocking lock) session is "Inacitve" attempting the exact same trouble update of "UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQ ID+:1 WHERE QUEUENAME=:2" causing the deadlock before the session goes inactive for some reason (timeout, user closing the window etc - we do not know how or why at this point)

        Also by looking at the App Server LOGS - with the Messages - WORKFORCE_SYNC and HCR_EM_EVENT the deadlock is occurring because the queue is divided into sub queues and there are parallel updates happening to UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQ ID+:1 WHERE QUEUENAME=:2 Looks like These updates are happening without a commit and hence throwing ORA-00060 error on the App. Server LOGS.

        On the Application Server LOGS - we see the below error messages at that point of time:
        ------------ --------- --------- --------- --------- --------- -
        [08/31/09 16:38:18 PDAVIS@172.24. 135.37 (IE 6.0; WINXP) ICPanel](0) SQL error. Stmt #: 337 Error Position: 0 Return: 60 - ORA-00060: deadlock detected while waiting for resource
        PSAPPSRV.1749022 (1756) [08/31/09 16:38:18 PDAVIS@172.24. 135.37 (IE 6.0; WINXP) ICPanel](0) Sam SqlError: UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQ ID+:1 WHERE QUEUENAME=:2
        PSAPPSRV.1749022 (1756) [08/31/09 16:38:18 PDAVIS@172.24. 135.37 (IE 6.0; WINXP) ICPanel](0) Publish of message WORKFORCE_SYNC failed. (119,9) HCR_PUBLICATION_ RULES.PublishWor kforce.OnExecute Name:PublishWorkfor ce PCPC:595 Statement:17
        ------------ --------- --------- --------- --------- --------- -

        Any pointers / assistance is highly appreciated.

        Thank you.

        Regards,
        Ram Marupudi.



        Looking for the perfect gift? Give the gift of Flickr!
      • pattabhi27
        Hi - Thanks for the response. Did find this at knowledge center, implemented in our PROD environment, but did not help. Issue still persists. Thank you.
        Message 3 of 3 , Sep 2, 2009
        • 0 Attachment
          Hi - Thanks for the response. Did find this at knowledge center, implemented in our PROD environment, but did not help. Issue still persists.

          Thank you.

          Regards,
          Ram Marupudi.



          --- In psftdba@yahoogroups.com, T <tobybest@...> wrote:
          >
          > According to note ID 656090.1:
          > Symptoms
          > When submitting messages on Tools 8.48 we are seeing Deadlocking in PSIBQUEUEINST.
          > During stress testing for SAT we found deadlocking in the PSIBQUEUEINST table like the following
          >
          > 17.24.48 11446.608369 SQL error. Stmt #: 337 Error Position:
          > 0 Return: 60 - ORA-00060: deadlock detected while waiting for resource
          > 17.24.48 0.000088 Sam SqlError: UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2
          > 17.24.54 6.117268 Str=PopulateReport
          > 17.24.54 0.000011 SAT_ACKNOWLEDGEMENT:AcceptanceAcknowledgement=NULL
          > 17.24.54 0.001791 User ID=IDCAP027; Browser=IE/6.0; OpSys=WINNT;
          >
          >
          > Cause
          > Not Applicable
          > Solution
          >
          > Several cases have reported for this issue, however in each case it was
          > discovered that the cause was custom peoplecode calling a publish. In
          > each case a loop was calling the publish peoplecode using the same
          > variable. Once this code was simplified the publication worked fine
          > with no deadlocks.
          >
          > In one case the solution was two fold:
          > 1) Have only one active Integration Broker domain.
          > 2) Below find the publish peoplecode that was changed
          > BEFORE:
          > %IntBroker.Publish(&MSG);
          >
          > AFTER:
          > %IntBroker.Publish(&MSG, True); /* deferred publishing */
          >
          >
          >
          >
          > ________________________________
          > From: pattabhi27 <pattabhi27@...>
          > To: psftdba@yahoogroups.com
          > Sent: Wednesday, September 2, 2009 12:51:14 PM
          > Subject: PeopleSoft DBA Forum UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQID+:1 WHERE QUEUENAME=:2
          >
          >
          >
          > Hello PSFTDBA group - We have PT 8.48, HRMS 9.0 up and running on Oracle 10.2.0.4
          >
          > We have an issue, where the system hangs intermittently and none of the Users can login and already logged in users see "Processing. .Processing" message. Until we find the SID that blocks the DB and kill the same, the system will be in the same state.
          >
          > Upon further researching the issue - "Processing. ..Processing" is occuring due to a database deadlock and the culprit (blocking lock) session is "Inacitve" attempting the exact same trouble update of "UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQ ID+:1 WHERE QUEUENAME=:2" causing the deadlock before the session goes inactive for some reason (timeout, user closing the window etc - we do not know how or why at this point)
          >
          > Also by looking at the App Server LOGS - with the Messages - WORKFORCE_SYNC and HCR_EM_EVENT the deadlock is occurring because the queue is divided into sub queues and there are parallel updates happening to UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQ ID+:1 WHERE QUEUENAME=:2 Looks like These updates are happening without a commit and hence throwing ORA-00060 error on the App. Server LOGS.
          >
          > On the Application Server LOGS - we see the below error messages at that point of time:
          > ------------ --------- --------- --------- --------- --------- -
          > [08/31/09 16:38:18 PDAVIS@... 135.37 (IE 6.0; WINXP) ICPanel](0) SQL error. Stmt #: 337 Error Position: 0 Return: 60 - ORA-00060: deadlock detected while waiting for resource
          > PSAPPSRV.1749022 (1756) [08/31/09 16:38:18 PDAVIS@... 135.37 (IE 6.0; WINXP) ICPanel](0) Sam SqlError: UPDATE PSIBQUEUEINST SET QUEUESEQID=QUEUESEQ ID+:1 WHERE QUEUENAME=:2
          > PSAPPSRV.1749022 (1756) [08/31/09 16:38:18 PDAVIS@... 135.37 (IE 6.0; WINXP) ICPanel](0) Publish of message WORKFORCE_SYNC failed. (119,9) HCR_PUBLICATION_ RULES.PublishWor kforce.OnExecute Name:PublishWorkfor ce PCPC:595 Statement:17
          > ------------ --------- --------- --------- --------- --------- -
          >
          > Any pointers / assistance is highly appreciated.
          >
          > Thank you.
          >
          > Regards,
          > Ram Marupudi.
          >
          >
          >
          >
          >
          > __________________________________________________________________
          > Looking for the perfect gift? Give the gift of Flickr!
          >
          > http://www.flickr.com/gift/
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.