CLI and locking in transaction problem.

Hi, I have a locking problem using Informix CLI. I use INFORMIX-Universal Server Version 9.14.UC6 Here's details. There are two process, P1, P2, which handle DB tablesimultaneaously, say 'A'. First, process P1 has changed the value of a record from 1 to 2 in transaction 'T1'. Before the transaction T1 is committed or rollbacked, process P2 is trying to select the value of the record, then process is blocked in READ(A) function. ---------------------------------------------------------- Process1(Tran1) Process2(Trans2) 1. initial value of record A: 1 2. WRITE(A, 2) 3. READ(A) ---> blocked here. 4. COMMIT 5. COMMIT ---------------------------------------------------------- I don't know how I can avoid this blocking. As far as I know, in Oracle, Process2 can read the former value of the record A, that is, P2 read the value as 1. Is there any special option or configuration in Informix? Followings are parts of our source code. ---------------------------------------------------------------------------- -- SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); SQLSetConnectOption(hdbc, SQL_TXN_ISOLATION, SQL_TXN_READ_COMMITED); SQLSetConnectOption(hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_DRIVER); ... SQLSetStmtOption(hstmt, SQL_CONCURRENCY, SQL_CONCUR_VALUES); SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC); ... SQLExecDirec(hstmt, (UCHAR*) "SET LOCK MODE TO WAIT", 22); ---------------------------------------------------------------------------- -- Thanks in advance.

Depending on whether you are using an Informix mode database with transactions (logging mode BUFFERED LOG or UNBUFFERED LOG) or an ANSI mode database with transactions (logging mode ANSI) the default isolation level is either COMMITTED READ or REPEATABLE READ. Both of these modes lock the updated row and even readers must wait. You have 2 options. The reader application, if it does not do updates, can set it's ISOLATION LEVEL to DIRTY READ which will not attempt to acquire a shared lock on the rows being read and so will not block. If the app does any updates, however, this can be dangerous as you can update with values that have been updated by another user in the interrum. The better option is to do: SET LOCK MODE TO WAIT <nseconds>as you are already doing and block if the P2 app is doing updates. If these lock waits are taking too long look into redesigning the update process (P1?) so that it does not hold locks for so long. One method is to fetch the data without locking (ie not FOR UPDATE) and use timestamps in the row to detect whether another user has updated the row between fetch and update. When you refetch the timestamp you would do so FOR UPDATE which will lock the row and if the timestamps match do the update otherwise ROLLBACK to release the lock. Art S. Kagel Changyu Park wrote: > > Hi, > > I have a locking problem using Informix CLI. > I use INFORMIX-Universal Server Version 9.14.UC6 > > Here's details. > > There are two process, P1, P2, which handle DB tablesimultaneaously, say > 'A'. > First, process P1 has changed the value of a record from 1 to 2 in > transaction 'T1'. > Before the transaction T1 is committed or rollbacked, process P2 is trying > to > select the value of the record, then process is blocked in READ(A) function. > > ---------------------------------------------------------- > Process1(Tran1) Process2(Trans2) > > 1. initial value of record A: 1 > 2. WRITE(A, 2) > 3. READ(A) ---> blocked here. > 4. COMMIT > 5. COMMIT > ---------------------------------------------------------- > > I don't know how I can avoid this blocking. As far as I know, in Oracle, > Process2 can read the former value of the record A, that is, P2 read the > value as 1. > > Is there any special option or configuration in Informix? > > Followings are parts of our source code. > > ---------------------------------------------------------------------------- > -- > SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); > SQLSetConnectOption(hdbc, SQL_TXN_ISOLATION, SQL_TXN_READ_COMMITED); > SQLSetConnectOption(hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_DRIVER); > ... > SQLSetStmtOption(hstmt, SQL_CONCURRENCY, SQL_CONCUR_VALUES); > SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC); > ... > SQLExecDirec(hstmt, (UCHAR*) "SET LOCK MODE TO WAIT", 22); > ---------------------------------------------------------------------------- > -- > > Thanks in advance.