12 Replies Latest reply: May 25, 2012 12:07 PM by Purvesh K RSS

    Insert hangs due to pk violation (plsql)

    123529
      Hello everybody,

      i'm unable to cope with the following problem:

      1) suppose to have a table with a single column and it's primary key

      CREATE TABLE TABUSRLG
      (
      TUL_CODUSR VARCHAR2(30 BYTE)
      );


      ALTER TABLE TABUSRLG ADD (
      PRIMARY KEY
      (TUL_CODUSR) ENABLE);

      2) from a session execute
      insert into tabusrlg values 'A';
      and DON'T rollabck or commit


      3) from another session try the same insert

      insert into tabusrlg values 'A';

      this insert hangs (and i agree with this)

      but ...
      Is there a way to prevent the insert to hangs ? i'd like tpo skip the insert in this case without waiting for a timeout.

      Usually when i want to update a record i execute a select for update nowait ;
      if the instruction throws an exception the record is locked and i don't try the update

      I'm looking for something similar but in this case i don't find a way to sort out it


      Any suggestion ?
      Many thanks in advance

      Stefano
        • 1. Re: Insert hangs due to pk violation (plsql)
          Paul  Horth
          Stefano,

          What is your actual problem? Why do you think this is likely to happen in your business?
          • 2. Re: Insert hangs due to pk violation (plsql)
            Purvesh K
            Stefano,

            Check what locks are being Held by different sessions while performing DML's.
            If should never happen a DML is blocking another DML, especially while an Insert statement is being processed (for an Update, it can be imagined.)

            You may refer to a similar SQL to get the lock details (Certain modifications might be required as it is a crude sequel)

            select username,
            v$lock.sid,
            v$lock.type
            lmode,
            request
            from v$lock, v$session
            where v$lock.sid = v$session.sid;

            Let us know the outcome.

            As specified by Ashy, I do not think it is a correct practice to Exclusively lock a table; I would say its a V BAD Practice.

            Regrads,
            P.
            • 3. Re: Insert hangs due to pk violation (plsql)
              123529
              Hi Paul,

              actually it's the real question !
              Basically i should disable multiple login with the same username to a client server application.

              The idea is:

              - a trigger at logon writes a record in a table with the username without rollback or commit (username is pk)
              - if i face a pk violation i quit the application
              - i don't want to commit because if the 'regular' (i mean the first user connected with that account) session crashes (network or power supply problem) the user itself could not logon again untill someone cleans the table
              - in this case a maintenance procedure clears orphans sessions and the table as a consequence

              Obviously any suggestion for the real problem is welcome but even the solution of the first question is stickling my curiosity

              Rgds/Ciao
              Stefano
              • 4. Re: Insert hangs due to pk violation (plsql)
                sb92075
                stefano.cafiero wrote:
                Hi Paul,

                actually it's the real question !
                Basically i should disable multiple login with the same username to a client server application.
                is application 3-tier like below?

                EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer
                • 5. Re: Insert hangs due to pk violation (plsql)
                  123529
                  It's not 3 tier ; it's client server

                  client<=>DB
                  • 6. Re: Insert hangs due to pk violation (plsql)
                    Paul  Horth
                    You are re-inventing the wheel.

                    If you want to prevent multiple logons of a user, create a profile

                    See CREATE PROFILE in the docs, particularly sessions_per_user.
                    • 7. Re: Insert hangs due to pk violation (plsql)
                      123529
                      Thanks Paul,

                      i've took a look ... seems interesting (i can't try right now, it's night time) i'll try tomorrow morning at office;

                      the only drawback seems that the same username can't login to the same instance by another application isn't it ?
                      or there's way to setup it at v$session.PROGRAM level ?


                      Stefano
                      • 8. Re: Insert hangs due to pk violation (plsql)
                        Paul  Horth
                        >
                        disable multiple login with the same username
                        >

                        That's what you asked for.
                        • 9. Re: Insert hangs due to pk violation (plsql)
                          rp0428
                          >
                          The idea is:

                          - a trigger at logon writes a record in a table with the username without rollback or commit (username is pk)
                          - if i face a pk violation i quit the application
                          >
                          That is a TERRIBLE implementation. That means that NOTHING the user does, directly or indirectly, can ever commit or rollback.
                          >
                          - i don't want to commit because if the 'regular' (i mean the first user connected with that account) session crashes (network or power supply problem) the user itself could not logon again untill someone cleans the table
                          - in this case a maintenance procedure clears orphans sessions and the table as a consequence
                          >
                          So have the logon code check for an orphaned session and do the cleanout.

                          Why not just write a simple procedure that check V$SESSION to see if the user is already logged in for that application. See V$SESSION in the Database Reference
                          http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3016.htm

                          That system view can provide the Oracle username, session status (e.g. SNIPED), SCHEMANAME, OSuser, MODULE and other.

                          Some of those like MODULE can be set when the connection is made so you could provide the application name.

                          A simple check of this view could tell you if you want to continue the logon.
                          • 10. Re: Insert hangs due to pk violation (plsql)
                            Purvesh K
                            Stefano,

                            I am still not clear Why should Oracle hold a Lock and prevent from another session Inserting data?

                            I was not able to replicate the situation. Can anybody send steps to replicate the scenario?
                            SESSION 1:
                            CREATE TABLE test_table (col varchar2(10));
                            
                            select username,
                            a.sid,
                            a.type,
                            lmode,
                            request
                            from v$lock a, v$session b
                            where a.sid = b.sid
                              AND username = USER;
                              
                            USERNAME     SID     TYPE     LMODE     REQUEST
                            ------------------------------------------
                            PURVESH          23     AE          4          0
                            PURVESH          17     AE          4          0
                            PURVESH          143     JQ          4          0
                            PURVESH          143     AE          6          0
                            PURVESH          23     TO          3          0  
                            
                            insert into test_table values ('A');
                                 
                            select username,
                            a.sid,
                            a.type,
                            lmode,
                            request
                            from v$lock a, v$session b
                            where a.sid = b.sid
                              AND username = USER;
                              
                            USERNAME     SID     TYPE     LMODE     REQUEST
                            ------------------------------------------
                            PURVESH          23     AE          4          0
                            PURVESH          17     AE          4          0
                            PURVESH          143     JQ          6          0
                            PURVESH          143     AE          4          0
                            PURVESH          23     TO          3          0
                            PURVESH          23     TM          3          0
                            PURVESH          23     TX          6          0
                            
                            -- As you see here a Table Row Level lock (TX) and a Table Lock (TM) is placed to prevent Table Structure modification.
                            
                            SESSION 2:
                            insert into test_table values ('B');
                            
                            select username,
                            a.sid,
                            a.type,
                            lmode,
                            request
                            from v$lock a, v$session b
                            where a.sid = b.sid
                              AND username = USER;
                              
                            USERNAME     SID     TYPE     LMODE     REQUEST
                            ------------------------------------------
                            PURVESH          23     AE          4          0
                            PURVESH          17     AE          4          0
                            PURVESH          143     JQ          6          0
                            PURVESH          143     AE          4          0
                            PURVESH          23     TO          3          0
                            PURVESH          17     TM          3          0
                            PURVESH          23     TM          3          0
                            PURVESH          17     TX          6          0
                            PURVESH          23     TX          6          0
                            
                            The above data proves that another session was not prevented from inserting a record into the same table. SID 17 has placed another TX and TM in Mode 3.
                            I could not understand why are you thinking of alternatives without finding out where the actual problem lies?
                            I would recommend you to check out what locks are being held on your table and investigate the reason.
                            You can definitely let us know if you face any issue, just ensure to post appropriate data for our analysis.

                            Regards,
                            P.
                            • 11. Re: Insert hangs due to pk violation (plsql)
                              rp0428
                              >
                              I was not able to replicate the situation. Can anybody send steps to replicate the scenario?
                              >
                              OP provided the steps to replicate the situation; use the code provided.

                              Your code creates a table but doesn't create the primary key so your example is not equivalent to OP's.

                              If you use OP's code you will find that the INSERT by the second session will hang until the first session issues a COMMIT or ROLLBACK.
                              • 12. Re: Insert hangs due to pk violation (plsql)
                                Purvesh K
                                Yes, the Primary Key is the reason. I get that now. Sorry, I did not read the problem carefully. :(

                                In my view, the design that does not populate via a Sequence, should be a Bad designing and Implementation. Stephano must probably think of re-designing the table; using Alphanumeric/Sequence etc to rectify it.


                                Regards,
                                P.