Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    74

    Arrow Unanswered: About the lock,sathyaram_s help me

    HI,All

    I know each SQL statement automatically commit changes resulting in CLP. I have turned off the AUTOCOMMIT option(db2set db2options=+c) in my CLP. There is a table in my database. The name of the table is "student" and the contents of the table is

    name sex age
    Tom m 11
    John m 12
    ...

    First I open a CLP window and invoke the following command
    db2 connect to my_database user db2admin using db2admin
    db2 "delete from student where name='Tom'"
    I don't commit the transaction

    Secondly I open another CLP window and invoke the following command
    db2 connect to my_database user db2admin using db2admin
    db2 "select * from student"

    I find that the second CLP window doesn't output anything and the focus of the second CLP window is waiting. If I commit the transaction(invoke "db2 commit") in the first CLP window the result appearance in the second CLP window.

    How can I make the delete SQL statement not to lock the whole table?

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: About the lock,sathyaram_s help me

    Well, first of all I am not Sathyaram..... but here are my two cents;

    by default DB2 has the isolation level which only allows you to see the commited data. When you doing delete * you are locking the whole table!!!

    If you don't like the cursor waiting in CLP, then disconnect from the database and issue the following statement:

    db2 change sqlisl to UR

    This will change the default isolation level and now you will be able to see uncommitted records.

    Hope this helps..

    dollar

    Originally posted by zhouhaiming
    HI,All

    I know each SQL statement automatically commit changes resulting in CLP. I have turned off the AUTOCOMMIT option(db2set db2options=+c) in my CLP. There is a table in my database. The name of the table is "student" and the contents of the table is

    name sex age
    Tom m 11
    John m 12
    ...

    First I open a CLP window and invoke the following command
    db2 connect to my_database user db2admin using db2admin
    db2 "delete from student where name='Tom'"
    I don't commit the transaction

    Secondly I open another CLP window and invoke the following command
    db2 connect to my_database user db2admin using db2admin
    db2 "select * from student"

    I find that the second CLP window doesn't output anything and the focus of the second CLP window is waiting. If I commit the transaction(invoke "db2 commit") in the first CLP window the result appearance in the second CLP window.

    How can I make the delete SQL statement not to lock the whole table?

Posting Permissions

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