Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Talking Unanswered: Informix DB Row Locking

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    7

    Question Locking response

    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

  4. #4
    Join Date
    Jan 2004
    Posts
    71

    <!>

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

  5. #5
    Join Date
    Dec 2003
    Posts
    7

    Unhappy table locking/transactions...

    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

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •