Isolation Level - DB2 9.5

edwin_fredrick · #1 (**permalink**) 09-14-11, 08:41

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

przytula_guy · #2 (**permalink**) 09-14-11, 08:49

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..

Marcus_A · #3 (**permalink**) 09-14-11, 09:34

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.

edwin_fredrick · #4 (**permalink**) 09-14-11, 09:39

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?

edwin_fredrick · #5 (**permalink**) 09-14-11, 09:43

Quote:

Originally Posted by Marcus_A

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.

Marcus_A · #6 (**permalink**) 09-14-11, 09:51

Quote:

Originally Posted by edwin_fredrick

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.

edwin_fredrick · #7 (**permalink**) 09-14-11, 10:09

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?

Marcus_A · #8 (**permalink**) 09-14-11, 10:15

Quote:

Originally Posted by edwin_fredrick

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.

n_i · #9 (**permalink**) 09-14-11, 10:18

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.

Marcus_A · #10 (**permalink**) 09-14-11, 10:21

Quote:

Originally Posted by n_i

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.

edwin_fredrick · #11 (**permalink**) 09-15-11, 00:02

Quote:

Originally Posted by Marcus_A

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.

Marcus_A · #12 (**permalink**) 09-15-11, 00:42

Quote:

Originally Posted by edwin_fredrick

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 .........."

edwin_fredrick · #13 (**permalink**) 09-15-11, 01:02

Quote:

Originally Posted by n_i

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

Marcus_A · #14 (**permalink**) 09-15-11, 03:41

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.

Marcus_A · #15 (**permalink**) 09-15-11, 03:48

Quote:

Originally Posted by edwin_fredrick

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech