SEO

Hello,

I 'm working with an IBM DB2 V9 database via ODBC/CLI.
I've got a problem with different lock behaviour in the following
constellation / configuration.

1) connection C1 and connection C2 access table T1
2) STMT1 is a statementhandle of C1 and STMT2 is a statementhandle of
C2
3) the TXN_ISOLATION_LEVEL is SQL_TXN_READ_COMMITTED (Cursor
Stability) for both connections
4) both connections work in AUTOCOMMIT_MODE
5) table T1 contains of three Rows (R1, R2, R3)

6) the two connections executes actions in the following time order:

a) C1: opens a result set / cursor with "SELECT * FROM T1"

b) C1: fetch of row R1 with open cursor on STMT1

c) C1: fetch of row R2 with open cursor on STMT1

d) C2 (!!!): DELETE-Stmt for row R2 in T1 (actually fetched by
C1,STMT1)

Now it depends on the statementHandle-configuration whether the DELETE
of R2 produces a deadlock-situation or not.

If i configure STMT1 and STMT2 as follows then it works fine and there
is no deadlock.

first config (no deadlock):
SQL_ATTR_CURSOR_SENSITIVITY set to SQL_INSENSITIVE
SQL_ATTR_CURSOR_TYPE set to SQL_CURSOR_STATIC
SQL_ATTR_CONCURRENCY set to SQL_CONCUR_READ_ONLY

But the second variant causes a deadlock.

second config (deadlock):
SQL_ATTR_CURSOR_SENSITIVITY set to SQL_INSENSITIVE,
SQL_ATTR_CURSOR_TYPE set to SQL_CURSOR_FORWARD_ONLY
SQL_ATTR_CONCURRENCY set to SQL_CONCUR_READ_ONLY

I can't use the first config because it is to slow (SQL_CURSOR_STATIC
is scrollable in DB2, that's slow).
So i have to use the first config but i can't understand the
difference of the cursor attributes that causes the deadlock.

Has anyone an idea how to use SQL_CURSOR_FORWARD_ONLY cursors without
a deadlock in the described situation?

Thanks,

Marc