3 Replies Latest reply: Oct 10, 2013 1:03 PM by Andreas Weiden RSS

    enq: TX - row lock contention in forms 10g sequency number generation

    vsm

      Iam Getting the Deadlock issue in oracle formdeveloper 10g database is 11g Acutually in our small Hospital organization using different forms generating entrying labrequest form finally save time

      one sequency number will generated i have give procedure below every save criteria form using below procedure some time iam getting lock iam using blocksession query i have find out some

       

      OSUSERMACHINETERMINALPROGRAMSQL_IDLOGON_TIMEBLOCKING_SESSION_STATUSBLOCKING_SESSIONEVENT
      user423UMCCDOM\LEVEL4-MICULEVEL4-MICUPrjMcr.EXE0ccngw7dfkmgb23/09/2013 11:34:41 AMVALID277enq: TX - row lock contention
      SYSTEMWORKGROUP\PRIAPPSVRPRIAPPSVRfrmweb.exe0ccngw7dfkmgb23/09/2013 11:32:50 AMVALID186enq: TX - row lock contention
      SYSTEMWORKGROUP\PRIAPPSVRPRIAPPSVRfrmweb.exe0ccngw7dfkmgb22/09/2013 2:49:47 PMVALID277enq: TX - row lock contention
      SYSTEMWORKGROUP\PRIAPPSVRPRIAPPSVRfrmweb.exe0ccngw7dfkmgb23/09/2013 7:00:22 AMVALID186enq: TX - row lock contention
      user290UMCCDOM\LEVEL2-NLEVEL2-NPrjMcr.EXE0ccngw7dfkmgb23/09/2013 10:28:06 AMVALID277enq: TX - row lock contention

       

      lock are happen after open the code i have find out used FOR UPDATE in procedure please tell me any alternative for this every labrequest form saving time using this procedure for generating prefix sequence number multiple user using with different systems

       

      PROCEDURE   Gsp_GenSeqNum (I_SvPrefixCd IN VARCHAR2,I_SvUserName IN VARCHAR2,

                                O_SvSeqNum OUT VARCHAR2) IS

      --This Procedure generates the sequence number

      --by retrieving the prefix based on the code

      --supllied to the procedure.

        CURSOR  crSeqGenerator IS

               SELECT Gsn_prefix_last_num, Gsn_prefix_year

                  FROM G_SEQUENCE_NUMBER

               WHERE Gsn_prefix_Cd = I_SvPrefixCd

                     AND Gsn_prefix_year = TO_CHAR(SYSDATE,'YYYY')

        FOR UPDATE OF Gsn_prefix_last_num;

        udSeqNum        G_SEQUENCE_NUMBER.Gsn_Prefix_last_num%TYPE;

        udNextSeqNum    G_SEQUENCE_NUMBER.Gsn_Prefix_last_num%TYPE;

        udYear          G_SEQUENCE_NUMBER.Gsn_Prefix_year%TYPE;

        udPrefix        G_SEQUENCE_PREFIX.Gsp_Prefix_Value%TYPE;

      BEGIN

        SELECT Gsp_Prefix_Value

        INTO udPrefix

        FROM G_SEQUENCE_PREFIX

        WHERE Gsp_Prefix_Cd = I_SvPrefixCd;

        OPEN crSeqGenerator;

        FETCH crSeqGenerator

         INTO udSeqNum,

              udYear;

        IF crSeqGenerator%FOUND THEN

           udNextSeqNum  :=udSeqNum + 1;

           O_SvSeqNum     := udPrefix||'/'||TO_CHAR(SYSDATE,'YY')

                                     ||'/'||LPAD(TO_CHAR(udNextSeqNum),6,'0');

        ELSE

      INSERT INTO G_SEQUENCE_NUMBER VALUES(I_SvPrefixCd,TO_CHAR(SYSDATE,'YYYY'),0,I_SvUserName,SYSDATE);

           udSeqNum      :=0;

           udNextSeqNum  :=udSeqNum + 1;

           O_SvSeqNum:=UdPrefix||'/'||TO_CHAR(SYSDATE,'YY')

                               ||'/'||LPAD(TO_CHAR(udNextSeqNum),6,'0');

        END IF;

        UPDATE G_SEQUENCE_NUMBER

           SET Gsn_Prefix_last_num=udNextSeqNum

         WHERE Gsn_Prefix_Cd = I_SvPrefixCd

           AND Gsn_Prefix_year=TO_CHAR(SYSDATE,'YYYY');

        CLOSE crSeqGenerator;

      END Gsp_GenSeqNum;

       

      Thanks

      subbu