This discussion is archived
3 Replies Latest reply: Oct 10, 2013 11:03 AM by Andreas Weiden RSS

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

vsm Newbie
Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points