Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Autocommit in CLP

    Hi All,

    [ DB2 V9.7.1 on Centos 5.2 x32 ]

    Can some one suggest me how can i simulate deadlock for generating deadlock event ??

    I could not do it. I followed instructions at Event monitor sample output .

    Anyone might have tried and succeeded. Please help !

    Regards
    DBFinder

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about -c- or +c option?

    Table1 summarizes the CLP option flags. These options can be specified in any sequence and combination. To turn an option on, prefix the corresponding option letter with a minus sign (-). To turn an option off, either prefix the option letter with a minus sign and follow the option letter with another minus sign, or prefix the option letter with a plus sign (+).
    For example, -c turns the auto-commit option on, and either -c- or +c turns it off.
    These option letters are not case sensitive, that is, -a and -A are equivalent.
    Command line processor options - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    As Tonkuma suggested you can use +c to turn off autocommit. Open two xterm and connect to the db from both:

    X1:
    db2 connect to db
    X2:
    db2 connect to db

    X1:
    db2 +c "update t1 set ..."
    X2:
    db2 +c "update t2 set ..."
    X1:
    db2 +c "update t2 set ..."
    X2:
    db2 +c "update t1 set ..."

    the deadlockdetector kicks in every 10 seconds (I think), so you might have to increase LOCKTIMEOUT value to avoid locktimeout (rc=68 instead of deadlock rc=2)
    --
    Lennart

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by tonkuma View Post
    seen this already !

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by lelle12 View Post
    As Tonkuma suggested you can use +c to turn off autocommit. Open two xterm and connect to the db from both:

    X1:
    db2 connect to db
    X2:
    db2 connect to db

    X1:
    db2 +c "update t1 set ..."
    X2:
    db2 +c "update t2 set ..."
    X1:
    db2 +c "update t2 set ..."
    X2:
    db2 +c "update t1 set ..."

    the deadlockdetector kicks in every 10 seconds (I think), so you might have to increase LOCKTIMEOUT value to avoid locktimeout (rc=68 instead of deadlock rc=2)
    Well I have tried this couple of times. That's why posted.

    I will try one more time; why not holding X lock on table t1 may be some change in client behaviour in recent versions.


    Session 1:

    login as: db2inst1
    Welcome to centos

    db2inst1@192.168.13.23's password:
    Last login: Wed Apr 4 12:36:06 2012 from cpe602ad0854272-cm602ad085426f.cpe.net.cable.roge
    [db2inst1@centos ~]$ db2 connect to test1

    Database Connection Information

    Database server = DB2/LINUX 9.7.1
    SQL authorization ID = DB2INST1
    Local database alias = TEST1

    [db2inst1@centos ~]$ echo $$
    21042
    [db2inst1@centos ~]$ db2 select * from try1
    SQL0104N An unexpected token "bin cronlogs crsp data db2inst1" was found
    following "select ". Expected tokens may include: "<space>". SQLSTATE=42601
    [db2inst1@centos ~]$ db2 "select * from try1"

    ID
    -----------

    0 record(s) selected.

    [db2inst1@centos ~]$ db2 "insert into try1 values (12)"
    DB20000I The SQL command completed successfully.
    [db2inst1@centos ~]$ db2 "select * from try1"

    ID
    -----------
    12

    1 record(s) selected.

    [db2inst1@centos ~]$



    [db2inst1@centos ~]$ db2 +c "update try2 set id=33"
    DB20000I The SQL command completed successfully.
    [db2inst1@centos ~]$ db2 +c "update try2 set id=66"
    DB20000I The SQL command completed successfully.
    [db2inst1@centos ~]$
    Session 2:

    login as: db2inst1
    Welcome to centos

    db2inst1@192.168.13.23's password:
    Last login: Mon Apr 23 12:49:29 2012 from 192.168.13.11
    [db2inst1@centos ~]$ echo $$
    21529
    [db2inst1@centos ~]$ db2 connect to test1

    Database Connection Information

    Database server = DB2/LINUX 9.7.1
    SQL authorization ID = DB2INST1
    Local database alias = TEST1

    [db2inst1@centos ~]$ db2 "insert into try2 values (33)"
    DB20000I The SQL command completed successfully.
    [db2inst1@centos ~]$ db2 "select * from try2"

    ID
    -----------
    33

    1 record(s) selected.

    [db2inst1@centos ~]$

    [db2inst1@centos ~]$ db2 +c "update try1 set id=44"
    DB20000I The SQL command completed successfully.
    [db2inst1@centos ~]$ db2 +c "update try1 set id=55"
    DB20000I The SQL command completed successfully.
    [db2inst1@centos ~]$

    See nothing happened !


    Regards

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In 9.7 there is a new database config parm that can circumvent locking contention if enabled. If you upgrade from a previous release it will be DISABLED, but for a newly created 9.7 databases it will be ENABLED.

    Currently Committed (CUR_COMMIT) = DISABLED

    Read the doc to understand how it works:
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What do you expect to happen ?

    In session 1, you have updated try2 and in session2 you have updated try1
    No lock situation as far as I can see.

    Probably am missing something!!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by DBFinder View Post
    Well I have tried this couple of times. That's why posted.

    I will try one more time; why not holding X lock on table t1 may be some change in client behaviour in recent versions.

    Regards
    As Marcus_A point's out, if CUR_COMMIT is enabled readers won't block writers and vice versa. In my example I used two writers, so that should force a deadlock anyhow. You can force a deadlock in your example by using a stricter isolation level. Example:

    db2 "select * from try1 with rr"

    If I remember correctly CUR_COMMIT is in effect for ur and cs, so you can use rs as well
    --
    Lennart

Posting Permissions

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