Results 1 to 4 of 4

Thread: Locking

  1. #1
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Unanswered: Locking

    I have a query which goes something like

    select * from FINAL TABLE (insert into table A values (default,b,c))

    Basically i want to select the inserted query (the table A has some autogenerated identity columns which i need to select)
    Taking into consideration concurrent users in the system, i needed to know

    1. Will this query run fine in a concurrent user scenario
    2. If Step 1 is true, does the query obtain a table level lock or a row level lock on table A.
    IBM Certified Database Associate, DB2 9 for LUW

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 uses typically row-level locks. But that actually depends on the isolation level, the number of locks (see lock escalation) etc.

    Your statement is just a regular INSERT, which means that DB2 has to use X-locks on those rows. Additionally, DB2 collects all the rows of the table for the result set (which is probably not what you want NEW TABLE instead). For that, it would need S-locks, but since those rows have an X-lock already, it doesn't make a difference.

    To answer your questions:
    1. Yes, it will - why should this be a problem? If you use isolation level RR, it could become an issue, thought.
    2. It depends. The usual locking rules apply.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    ok here is the complete scenario -

    Table A has
    COL1 - autogenerated
    COL2 - varchar
    COL3 - varchar


    now the unique id which i will be generating (and storing separately) will be a combination of COL 1 , 2 and 3

    Also i want the unique id to be a continous number without any gaps for maintainance / report purposes

    so what i do (since my system has concurrency) is
    Lock table A
    insert into table A value
    then select * from table A where max(COL1)
    release lock

    If i dont have locks obviously i'll have wrong answers

    hence i was inquiring about the statement to replace the above scenario
    Code:
    select * from FINAL TABLE (insert into table A values (default,b,c))
    coz if at the end of the day if i am still putting a table lock this statement will be similar to my lock + unlock scenario

    Btw i am using CS level.


    on side-note what is the difference between
    Code:
    select * from FINAL TABLE (insert into table A values (default,b,c))
    and
    Code:
    select * from NEW TABLE (insert into table A values (default,b,c)) t
    IBM Certified Database Associate, DB2 9 for LUW

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The "no gaps" thing pops up once in a while here. If you would like to have it just for a nicer look, you should forget it right away. If you have to guarantee "no gaps", you will have a major headache before you have resolved this reliably (think about application, DBMS crashes, regular rollbacks, effects on concurrency, ...) Unless you are required by law, you really don't want to do that - trust me.

    Your scenario above says "release locks". Do you mean you "commit" there? If not, you already have a hole.

    In any case, you should never ever rely on a certain locking behavior implemented by the DBMS based on your query. The only thing you can rely on is the description of the isolation levels and which guarantees are given by those. With your statement, you may have an index, which still allows concurrent inserts to go through, and if one such concurrent insert rolls back, you will have a gap in your sequence. Also, with isolation level CS, DB2 is required to hold read locks only as long as a cursor is position on the row. Thus, if you do INSERT + SELECT on table A (ignoring any explicit locks), the lock is freed immediately after the SELECT finishes. The transaction can still go on. If you have explicit table locks, you have to use exclusive locks. And that is a performance-killer, of course.

    p.s: Regarding the difference between NEW TABLE and FINAL TABLE, you may want to read here: http://publib.boulder.ibm.com/infoce.../r0000875.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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