Hi
I'm using V8.1.7 on windows.
I can't figure out what is wrong here. In my test suite (CPP program through ODBC) I start a transaction and insert some rows. I read the rows again without comitting the transaction. Success as expected.
I start another transaction without committing the first transaction. I issue a query against the table that I inserted the rows in before. I would expect that the query returned empty because the first transaction is not yet comittet (using isolation level Repeatable reads). Instead the query statement fails after aprox. 20 sec. and returns "HY008..... Processing was cancelled due to an interrupt". It seams like the table is locked by the first transaction or something ??
The same test performs as expected when testing against Oracle and Mssql.
Is there something wron with my sql or can I configure the DB2 somehow to act as I expect it to do ?
The Sql is a very simple query that joins two tables and uses primary key as search predicate

There's nothing wrong here. DB2 implements concurrence in a different way. When you insert a row it remains locked in an exclusive mode until committed. Whenever you attempt to read an exclusively locked row you get a lock wait and eventually a timeout. An exception to that would be an uncommitted read (SELECT ... WITH UR), which wouldn't block on a locked row but would instead read an uncommitted version of it.

I don't think you'll be able to achieve the behaviour you want on DB2.

You will get a locktimeout (-911 reason code 68) if locktimeout db parm is set to something other than -1 (default).

So whats the whole story about isolation level in DB2 if the locking mechanism works like that ?
I guess that for inserts the table will always be locked unless you actually want to read uncomitted data no matter what isolation level you specify. I guess ACID is down the drain or am I missing something?
Thanks for the reply I got the info that I needed but I not sure I understand DB2

The "whole story" is given in much detail in DB2 Administration Guide: Performance. It says, in particular, that an insert doesn't generally lock the entire table but only the inserted row.

By the way, I fail to see how all of the above contradicts the "ACID principle": a transaction is still atomic, consistent, isolated, and durable.