Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Isolation Level - DB2 9.5

    We are having an issue while reading a record after it is updated in our application.

    We tried to update record in a table and then read it is the subsequent statement and it is not getting executed. We tried setting different isolation level using SET CURRENT ISOLATION command and we are still facing the issue.

    The following example we tried in Command Editor.

    --------------------------------
    SET CURRENT ISOLATION = CS;

    update table1 set sname = 'ABC' where id = 601;

    select * from table1 where id = 601;
    --------------------------------

    SELECT statement just keeps executing endlessly.

    Regards

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you are probably in lock wait
    verify with list applications
    try to commit the update first
    or select .... with ur
    if update is not committed - no application can change it anyhow - so why reading what you just introduced and has not changed..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Isolation level only affects how long Share Locks (SELECT, etc) are held, and does not affect Exclusive Locks (Update, etc).

    In addition to comments above from Guy, I would consider changing the LOCKTIMEOUT db cfg parm to something other than -1 (wait forever). Typically it is set to about 30 seconds in most databases. Also, you may want to turn auto-commit on in the Command Editor.
    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
    Oct 2004
    Posts
    60
    The application logic does not allow us to commit before running the select statement as it needs to be in a transaction.

    We are able to do the same in Oracle 10g & SQL Server 2008. Only in DB2 9.5 i am facing this issue. Is there any parameter to set the proper isolation level to resolve this issue?

  5. #5
    Join Date
    Oct 2004
    Posts
    60
    Quote Originally Posted by Marcus_A View Post
    Isolation level only affects how long Share Locks (SELECT, etc) are held, and does not affect Exclusive Locks (Update, etc).

    In addition to comments above from Guy, I would consider changing the LOCKTIMEOUT db cfg parm to something other than -1 (wait forever). Typically it is set to about 30 seconds in most databases. Also, you may want to turn auto-commit on in the Command Editor.
    I am having this issue in C++ application. To simulate the same in command editor i have disabled auto commit.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by edwin_fredrick View Post
    I am having this issue in C++ application. To simulate the same in command editor i have disabled auto commit.
    You will have to explicitly commit in the C++ application, and although you can turn on auto-commit with some clients, it is usually not advisable in application programs that have multiple SQL statements in one logical unit of work.

    If the same application connection tries to read a row that it itself has updated (as opposed to another application connection) then there should not be a lock contention between the various SQL statements. Whether or not you have two different application connections into DB2 within the same C++ application, I can't say.
    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
    Oct 2004
    Posts
    60
    Only in DB2 9.5 i have this issue. The application works fine with Oracle 10g & SQL Server 2008.

    Is there any parameter / isolation level to set, so that i could make it work like in SQL Server 2008 & Oracle 10g?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by edwin_fredrick View Post
    Only in DB2 9.5 i have this issue. The application works fine with Oracle 10g & SQL Server 2008.

    Is there any parameter / isolation level to set, so that i could make it work like in SQL Server 2008 & Oracle 10g?
    DB2 has a different type of locking (pessimistic) than in Oracle and SQL Server (optimistic). As I mentioned, isolation level only affects how long the lock is held for a select, and your problem is how long the lock is held for the update.

    In DB2 9.7 the default for new databases is to use something similar to optimistic locking, whereby DB2 obtains the previously committed data in the transaction log to do the select if the current row is locked. But this is not available in 9.5.

    I don't understand why the application that did the update cannot COMMIT? That is usually a sign of poor application design or poor coding technique.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The scenario you have described works as one would expect:
    Code:
    D:\t>db2 +c
    db2 => create table table1 (sname char(3), id smallint)
    DB20000I  The SQL command completed successfully.
    db2 => commit
    DB20000I  The SQL command completed successfully.
    db2 => set current isolation cs
    DB20000I  The SQL command completed successfully.
    db2 => insert into table1 (sname,id) values ('XYZ',601)
    DB20000I  The SQL command completed successfully.
    db2 => commit
    DB20000I  The SQL command completed successfully.
    db2 => update table1 set sname = 'ABC' where id = 601
    DB20000I  The SQL command completed successfully.
    db2 => select * from table1 where id = 601
    
    SNAME ID
    ----- ------
    ABC      601
    
      1 record(s) selected.
    
    db2 =>
    Obviously, you do something different, but unless you post the actual output from your actual test, I doubt anyone would be able to help you.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i View Post
    The scenario you have described works as one would expect:
    Code:
    D:\t>db2 +c
    db2 => create table table1 (sname char(3), id smallint)
    DB20000I  The SQL command completed successfully.
    db2 => commit
    DB20000I  The SQL command completed successfully.
    db2 => set current isolation cs
    DB20000I  The SQL command completed successfully.
    db2 => insert into table1 (sname,id) values ('XYZ',601)
    DB20000I  The SQL command completed successfully.
    db2 => commit
    DB20000I  The SQL command completed successfully.
    db2 => update table1 set sname = 'ABC' where id = 601
    DB20000I  The SQL command completed successfully.
    db2 => select * from table1 where id = 601
    
    SNAME ID
    ----- ------
    ABC      601
    
      1 record(s) selected.
    
    db2 =>
    Obviously, you do something different, but unless you post the actual output from your actual test, I doubt anyone would be able to help you.
    As I mentioned above, he must be submitting the two SQL statements (update and select) from different application connections. If it were the same connection, then it would work as you described. A single DB2 connection cannot have lock contention with itself.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Oct 2004
    Posts
    60
    Quote Originally Posted by Marcus_A View Post
    As I mentioned above, he must be submitting the two SQL statements (update and select) from different application connections. If it were the same connection, then it would work as you described. A single DB2 connection cannot have lock contention with itself.

    It is a single connection, In Command Editor, I updated a row and try to select the same row in the next statement, it willl get struck endlessly.

    Even from other session if i query that row, i am not getting any output.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by edwin_fredrick View Post
    It is a single connection, In Command Editor, I updated a row and try to select the same row in the next statement, it willl get struck endlessly.

    Even from other session if i query that row, i am not getting any output.
    I don't know how the Command Editor works when it talks to DB2 and how many connections it creates. The real question is how do you do it in the C++ application program: are there two different connections or just one?

    If you want to test this outside of the C++ program, use the Command Line interface and you can turn off auto-commit with the +c option:

    db2 +c "update ........."
    db2 "select * from .........."
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Oct 2004
    Posts
    60
    Quote Originally Posted by n_i View Post
    The scenario you have described works as one would expect:
    Code:
    D:\t>db2 +c
    db2 => create table table1 (sname char(3), id smallint)
    DB20000I  The SQL command completed successfully.
    db2 => commit
    DB20000I  The SQL command completed successfully.
    db2 => set current isolation cs
    DB20000I  The SQL command completed successfully.
    db2 => insert into table1 (sname,id) values ('XYZ',601)
    DB20000I  The SQL command completed successfully.
    db2 => commit
    DB20000I  The SQL command completed successfully.
    db2 => update table1 set sname = 'ABC' where id = 601
    DB20000I  The SQL command completed successfully.
    db2 => select * from table1 where id = 601
    
    SNAME ID
    ----- ------
    ABC      601
    
      1 record(s) selected.
    
    db2 =>
    Obviously, you do something different, but unless you post the actual output from your actual test, I doubt anyone would be able to help you.
    I tried it in Command Editor with Auto-Commit disabled, and the select statement is getting struck.

    But if i run with

    select * from table1 where id = 601 WITH UR;

    it executes successfully.

    Best Regards

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I tried it with the command editor with auto-commit off and both statements worked. Note that my rollback and then select statement is included to prove that auto-commit is off:

    Code:
    ------------------------------ Commands Entered ------------------------------
    select * from dept where deptno = 'D21';
    update dept set location = 'TEST' where deptno = 'D21';
    select * from dept where deptno = 'D21';
    rollback;
    select * from dept where deptno = 'D21';
    ------------------------------------------------------------------------------
    select * from dept where deptno = 'D21'
    
    DEPTNO DEPTNAME                             MGRNO  ADMRDEPT LOCATION        
    ------ ------------------------------------ ------ -------- ----------------
    D21    ADMINISTRATION SYSTEMS               000070 D01                      
    
      1 record(s) selected.
    
    
    update dept set location = 'TEST' where deptno = 'D21'
    DB20000I  The SQL command completed successfully.
    
    select * from dept where deptno = 'D21'
    
    DEPTNO DEPTNAME                             MGRNO  ADMRDEPT LOCATION        
    ------ ------------------------------------ ------ -------- ----------------
    D21    ADMINISTRATION SYSTEMS               000070 D01      TEST            
    
      1 record(s) selected.
    
    
    rollback
    DB20000I  The SQL command completed successfully.
    
    select * from dept where deptno = 'D21'
    
    DEPTNO DEPTNAME                             MGRNO  ADMRDEPT LOCATION        
    ------ ------------------------------------ ------ -------- ----------------
    D21    ADMINISTRATION SYSTEMS               000070 D01                      
    
      1 record(s) selected.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by edwin_fredrick View Post
    But if i run with

    select * from table1 where id = 601 WITH UR;

    it executes successfully.

    Best Regards
    The WITH UR will read the uncommited update, even if still locked, and assume it will be eventually updated (it returns the updated values). This is different behavior than Oracle and DB2 9.7 with CUR_COMMIT enabled, which if the row has an exclusive lock, it will read the previous state of the data before the update happened (unless you are in the same application connection and there is no lock contention).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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