BigBob said:

Try again. :o)

I really can't understand the problem -- are you using LOCK MODE (ROW) on
all these tables? What ISOLATION LEVEL are you using?

In individual programs that will do anything to a data base, including
external Java programs,

   set isolation to committed read;
   set lock mode to wait;

Seems like that ought to do it.

Two ways ---

1) issue a dummy update on the row (i.e. set col1 = col1 where xxxx)
2) use repeatable read isolation.

Bob-
    What you may be missing is when you declare your cursor, be sure to include "FOR UPDATE" in your SELECT statement.  That way the engine will lock all rows of your cursor as it reads them, rather than waiting until you issue the UPDATE statement.
    Or, if you're only dealing with one row, you could do something like this:

BEGIN WORK

SELECT * INTO foo_bar.* FROM my_table
WHERE whatever = whatever_else

UPDATE my_table SET * = foo_bar.*

...What ever your process is here...

UPDATE my_table SET * = foo_bar.*

IF ( SQLCA.SQLERRD[3] = 1 AND SQLCA.SQLCODE = 0 ) THEN
   COMMIT WORK
ELSE
   ROLLBACK WORK
END IF

This would force the engine to lock your row and hold it until you issue COMMIT WORK.

            --EEM

sending to informix-list

how many rows are present in your tables?, it may be that your queries are doing a sequential scan on the table which may result in the problem that you are experiencing, you may do the following to verify

1. make sure the table is created with the row level locking
2. run the queries in dbaccess and capture the explain plan and may sure they are not doing a sequential scan on the tables
3. make sure you have "lock mode to wait <max # of seconds>" in your code
4. update the statistics for the tables
5. try using an INDEX optimizer directive in the query and see if you experience the same problem.

-----Original Message-----
From: owner-informix-list@iiug.org
[mailto:owner-informix-list@iiug.org]On Behalf Of BigBob
Sent: Wednesday, December 29, 2004 6:08 PM
To: informix-list@iiug.org
Subject: Informix locking & transactions

I need to lock a ROW in an Informix table and hold onto that lock until
the end of a transaction.  During this transaction, 2 seperate Java
processes MIGHT start and want to update a table held in that
transaction before it is released.  Is there any way to accomplish
this?

Let me explain...

An online application issues a "begin work" then opens a cursor with a
select for update going after a single row in a table (e.g. Table A).
The business requires that no one else be allowed to access 'child
tables' linked to that particular row until they back-out(rollback) or
complete the transaction (commit).  This works well within the
application.

However, the problems arose in system testing when we found some
"external" processes in Java that need to update a table (e.g. Table M)
"in" the transaction, but not the particular table locked (e.g. Table
A).  Since Table M is held by the transaction, the Java processes are
prevented from doing the updates.

So, I need to be able to "lock" a row in Table A but not release it
until the commit which could be after they access Table Z, but allow
the external Java processes to update Table M.

Is this possible?  Is there a different design that I could use?
Thanks in advance for the help!

sending to informix-list