Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Help!!! DeadLock Issue

    Dears,
    My multi-thread AP run with Informix IDS 11.5. And sometimes got deadlock(-143) error. Does any body have suggestions? or any tools I can identify the error in my code.

    1. Programs are written by EC. (multi-thread)
    2. Using Dynamic SQL in EC ( PREPARE, DECLARE, OPEN, FETCH, CLOSE, FREE....)
    3. onconfig param, OPTCOMPIND = 2
    4. following pseudo code run on more than two threads concurrently:
    - set lock wait to 30 secs.
    - begin transaction.
    - lock one record in table_a (by select ... for update, set isolation to committed read retain update locks)
    - sel data from table_b into c data structure memory(set isolation to cursor stability; then select data without for update)
    - update these records. (select from previous step)
    - commit transaction. (and set isolation to committed read)
    - update these records. (select from previous step)

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    you must identify which locks are set on which objects with onstat -k or a query on dbmaster / syslocks.

    these tools will help you to detect which sessions are holding the locks and which want the locks

    Stupid questions:
    1)are all the involved tables created as "lock mode row"?
    2) do the update statement use indexes ?

    Eric

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Dear Eric,

    I can not get this error every time. And I can not set DEAD_LOCK_TIMEOUT on system. The deadlock error just returned immediately when error occur.
    onstat -k and syslocks are real time tools for detecting error.

    I try to avoid this situation by SET ISOLATION to COMMITTED READ LAST COMMITTED before select , and EXEC SQL SET ENVIRONMENT OPTCOMPIND '0'. But I still did not know the reason of deadlock.

    And these tables created with lock mode row statement.
    update statement use unique index.

    Gary

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Dear Gary,

    multi-threaded programmation has this risk to get deadlocks, you already know this. According to your pseudo code, thread #1 holds a lock on table a OR b, and another thread wants to take a lock on the same object.

    Deadlock detection is always immediate when you set a set lock mode to wait.

    I am not aware of any code parse or whatever that would be smart enough to understand the logic of your code and predecting how other applications ( or threads) would generate all the possible conflicting scenarios.

    You must first understand which thread sets the lock on which SQL statement, and which thread does what to try to put the lock on the same object, same for the "crossed referenced" locks ( #1 holds lock A and wants lock B, and #2 holds lock B and wants lock A).

    Isolation levels only determine the behaviour for accessing the rows ( SELECT or even UPDATE and DELETE). An update statement will always fail if the impacted row has any type of lock on it, even a shareable lock, and even if LAST COMMITTED.

    onstat -k and syslocks are the tools used to track this kind of situation, there is no other way to track the locks, and you need to indentify how locks are set in your application. use it in a loop and write the results to a file.

    Also you can onstat -g sql in a loop, which will tell you which session receive a sql error. If lucky you can trap the errored session as well as which locks are held by who.

    You may also add trace code in your .ec, writing the primary keys of the rows locked and updated, only if you detect a lock. Heavy, but the most efficient way for me.

    Best would be to provide a customized error handling procedure in your .ec code, for this type of situation, and use predictive checks whether you can set the locks or no. More code, but this is the drawback of the multi-threading programmation.


    If your version is 11.50 xc6 or above, you can also use
    Last edited by begooden-it; 03-29-12 at 02:25.

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Dear Eric,

    I think deadlock is imposible, but it occurred.

    - set lock wait to 30 secs.
    - begin transaction.
    - lock one record in table_a (by select ... for update, set isolation to committed read retain update locks) <= keep only one thread running this transaction
    - sel data from table_b into c data structure memory(set isolation to cursor stability; then select data without for update) <= deadlock here
    - update these records. (select from previous step)
    - commit transaction. (and set isolation to committed read)

    - update these records. (select from previous step) <= sometimes deadlock here

  6. #6
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Deadlock is possible only when you set a lock mode to wait.

    I am not sure I understand your pseudo code, but when you commit, all the locks are released ( if not this is a bug ). so the very last update should not in theory have a conflict with any of the rows touched by the former transaction...

    You should set a trace and monitor the locks as I said.
    Maybe to minimize trace log, write primary key values only when you trap the error 143..

Posting Permissions

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