I need to lock a ROW in an Informix DB and hold onto that lock until the end of a transaction. My first attempt at this was to begin work, lock, etc. This worked great for the application itself.

However, the problems arose in system testing we found some "external" processes that update Informix tables "in" the transaction, but not locked. They were prevented from doing so and we had to backout the locking change.

Is there any way to accomplish this? Lock a row, hold it and much later, release it without impacting access to other tables in the same transaction?

Thanks,
Bob

Hi,

Sorry, but i don't understand your problem.
For lock a row test:

Begin work;
set isolation to repeatable read;
select .......
where ......

Or

Begin work;
select .......
where .... FOR UPDATE

Gustavo.

Thanks for the post.

The problem isn't with Informix as it's doing the job. The problem is that while this database transaction is active/open, we have a Java process that needs to update one of the tables accessed in the transaction. The Java process fails as that table is "in a transaction".

Is there anyway around this?

Thanks,
Bob

There better not be a way around it! I think its functioning as designed if I understand your question.

Yes, it DOES work within the transaction, but this shop needs to have the Java process access those tables in the transaction.

So, I lock table A, but access tables B-Z also in the transaction to keep the lock. During that time, 1-2 Java processes EXTERNAL to the transaction need to update tables M & R (example), but are not able to.

Any ideas?

Thanks,
Bob

Hi,

There are several things to consider, the way of logging of the database, the concurrence of users and the level of Lock of the table (row or page).
Another case are the parameters of ODBC driver. (Autocommit=1 or 0).

Gustavo.