Table lock for insert triggers?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Table lock for insert triggers?

  1. #1
    Join Date
    Jan 2001
    Posts
    216

    Table lock for insert triggers?

    We have a trigger that ensures number of rows should not exceed a certain count. The trigger is as follows:

    CREATE OR REPLACE TRIGGER test AFTER INSERT ON offcparm
    DECLARE
    dbcount INTEGER;
    BEGIN
    SELECT COUNT(*) INTO dbcount FROM offcparm;
    IF dbcount > 1 THEN
    raise_application_error(-20000, 'Only 1 row supported');
    END IF;
    END;
    /

    Now, this works fine, except for cases where I do it through 2 sql sessions.

    eg, open 2 sqlplus sessions
    In session 1 do: insert into offcparm values ('1', '1');
    In session 2 do: insert into offcparm values ('2', '2');
    issue commit in session 1
    issue commit in session 2

    How can this be fixed? Please help!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I've seen someone propose to solve this issue (single row) by adding a column with two constraints: it is unique AND is equal to (say) 1.

    (With aknowledgements to Anonymous.)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    If the table itself is only supposed to have one row, then add a numeric Primary key that gets set to one during the INSERT. You might also have an update trigger that makes sure that it is always set to 1.

    Code:
    CREATE OR REPLACE TRIGGER test 
    BEFORE INSERT ON offcparm
       FOR EACH ROW
    DECLARE
       :NEW.new_pkey := 1;
    EXCEPTION
       WHEN DUP_VAL_ON_INDEX
          mylog('row already exists...BLAH...BLAH...BLAH...');
          RAISE;
       WHEN OTHERS THEN RAISE;
    END;
    /
    this space intentionally left blank

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    I've seen someone propose to solve this issue (single row) by adding a column with two constraints: it is unique AND is equal to (say) 1.

    Yeah, I would kind of attack it with a unique index also. I would probably:
    1. create a dummy field, say ID.
    2. make ID a primary key
    3. create an insert trigger that stuffs a "1" into the ID field unconditionally.

    Code:
    SQL> create table xyz (id number(10) primary key,
      2      f2 varchar2(6));
    
    Table created.
    
    SQL> edit
    Wrote file afiedt.buf
    
      1  create or replace trigger t1
      2  before insert on xyz
      3  for each row
      4  begin
      5     :new.id := 1;
      6* end;
    SQL> /
    
    Trigger created.
    
    SQL> insert into xyz (f2) values ('foo');
    
    1 row created.
    
    SQL> insert into xyz(f2) values ('bar');
    insert into xyz(f2) values ('bar')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SYSTEM.SYS_C005255) violated
    
    SQL> select * from xyz;
    
            ID F2
    ---------- ------
             1 foo
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    damn that gandolf989.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2001
    Posts
    216
    So, this appears to be a common problem in all our triggers. I gave a simple eg here. We also see it for cases where we do unique value checking (through triggers). So I was hoping for a generic solution, like lock the whole table before an insert?? not sure.

    Interestingly, we dont have this problem for constraints. So, if I try to insert 2 exact same rows in 2 sessions, Oracle blocks the second session till the first one is committed and then errors out on the second. Thats the behavior we want..

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chikkodi
    So I was hoping for a generic solution, like lock the whole table before an insert?? not sure.
    Sure, you can do that. However, now you've taken your highly scalable piece of software and turned it into a spreadsheet where only one user can access a particular piece of data at a time.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Originally posted by marist89
    damn that gandolf989.
    Great minds think alike!!!
    this space intentionally left blank

  9. #9
    Join Date
    Jan 2001
    Posts
    216
    But doesnt Oracle do the same for primary keys? It does not allow me to insert the same row from another session till the first one is committed.

    Or we have to make our inserts single threaded, which is slow too. else we will have bad data in the db.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width