Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Posts
    13

    Unanswered: lock issue with IN clause

    Why Select/Delete/Update with IN clause scanning entire table/index?

    I have created a table
    Cretate table TAB1(id int not null primary key, dept int);
    Inserted 6 rows with values(1,1; 2,2; 3,3 ; 4,4 ; 5,5; 6,6).

    In first transaction(T1).
    delete from TAB1 where id =3 ; DB2 locks 3rd row with X lock.

    In second transaction(T2)->
    Select * from TAB1 where id IN (2,6); DB2 is waiting for T1 to be commited.

    When I execute separately, it works fine.
    Select * from TAB1 where id =2;
    select * from TAB1 where id = 6;

    Why DB2 waits for another transcation to be completed even though it is not requesting same ids?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mowry_889
    Why Select/Delete/Update with IN clause scanning entire table/index?
    I guess you already know why - because the optimizer believes it is more efficient. And it probably is.

    You can stop reading now.

    If your question were "how to tell the optimizer not to scan the entire table", I would say "try ALTER TABLE ... VOLATILE CARDINALITY".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Setting this environment variable might also help (requires instance restart):

    db2set DB2_EVALUNCOMMITTED=ON

    While you are at it, you should also set these:

    db2set DB2_SKIPINSERTED=ON
    db2set DB2_SKIPDELETED=ON
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2009
    Posts
    13
    Thanks for your reply. I am little confused why DB2 behaves differently when I use IN clause with multiple parameters and just with one parameter.
    In the above example,
    Select * from TAB1 where id IN(2,6) It's trying to read every row by requesting share lock. It will in the wait state because other session holds "X" lock on row 3.
    versus
    select * from TAB1 where id IN(2)
    select * from TAB1 where id IN(6)
    Avove statements return data without any wait.


    I was expecting with IN(2,6) clause, it should read only rows 2 and 6. Why it is trying to read every row in the table/index.

    Thanks

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by mowry_889
    Thanks for your reply. I am little confused why DB2 behaves differently when I use IN clause with multiple parameters and just with one parameter.
    In the above example,
    Select * from TAB1 where id IN(2,6) It's trying to read every row by requesting share lock. It will in the wait state because other session holds "X" lock on row 3.
    versus
    select * from TAB1 where id IN(2)
    select * from TAB1 where id IN(6)
    Avove statements return data without any wait.


    I was expecting with IN(2,6) clause, it should read only rows 2 and 6. Why it is trying to read every row in the table/index.

    Thanks
    In order to have some idea how to answer that, it would helpful to know the verson of DB2 you are using (including fixpack level), the nubmer of rows in the table, the number of unique values in the column called "id", exactly what runstats command you have run, etc, etc, etc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2009
    Posts
    13
    C:\IBM\SQLLIB\BIN>db2level
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09052" with
    level identifier "03030107".
    Informational tokens are "DB2 v9.5.200.315", "s080811", "WR21411", and Fix Pac

    "2".
    Product is installed at "C:\IBM\SQLLIB" with DB2 Copy Name "DB2COPY".

    My sample table is
    Cretate table TAB1(id int not null primary key, dept int);
    Inserted 6 rows with values(1,1; 2,2; 3,3 ; 4,4 ; 5,5; 6,6).

    Thanks

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The answer is due to the number of rows in the table and the number you are fetching and the fact that you do not have an index with id,dept. Db2 knows that it has to access 33% of the table and if it were to use the index it would have to hit 33% of the rows in the index and then, also, access 33% of the data page, so it is cheaper and less CPU intensive to just scan the table. Once you have more data in your table and the stats are current it should begin using the index for your IN clause. Though, if your IN clause gets too large, you may go back to a scan of all rows.
    Some of the options above will get DB2 to use the index even though there are only 6 rows in the table, but you will see increased CPU time, I'm sure.
    Dave

  8. #8
    Join Date
    Aug 2009
    Posts
    13
    Already there is an index on ID since it's a primary key. I tried by creating index only dept and also combination on (ID,DEPT), but I see the same issue.

    I am running Select * from TAB1 where ID IN(2,6)-> it should access only indexes 2 and 6 since there is an index on ID. Is it correct?

    There are 30 rows in the table TAB1, and I am trying to access only 2 rows. Select statement goes in the wait state if I run delete on any id from other session before my select st. for example delete from t3 where id=1;

    If I run Select * from T3 where ID IN(6), it comes back fine.

    Thanks,

Posting Permissions

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