This discussion is archived
12 Replies Latest reply: May 25, 2012 10:07 AM by Purvesh K RSS

Insert hangs due to pk violation (plsql)

123529 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    It's not 3 tier ; it's client server

    client<=>DB
  • 6. Re: Insert hangs due to pk violation (plsql)
    Paul Horth Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    >
    disable multiple login with the same username
    >

    That's what you asked for.
  • 9. Re: Insert hangs due to pk violation (plsql)
    rp0428 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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.

Legend

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