Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: logging and transaction deadlock

  1. #1
    Join Date
    Feb 2011
    Posts
    152

    Default logging and transaction deadlock

    Whe running multiple transformations in parallel that have logging enabled, and have the property set for 'log record timeout (in days)', I see deadlock issues when the steps try to delete old records. Is there a way around this (as in a needed database setting) or is this really a bug?

    We are using Kettle 4.1 and MSSQL 2008 for the log repository.

    Errors:

    Unable to perform logging at the end of the transformation
    Unable to write to the log channel information table
    Unable to clean up old log records from log table channel
    Couldn't execute SQL: DELETE FROM "dbo"."channel" WHERE "LOG_DATE" < ?
    Transaction (Process ID 83) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

  2. #2
    Join Date
    Jun 2011
    Posts
    102

    Default

    Hi,
    all the transformation write to the same log? if that's the case, it may be that one trasformation is trying to delete the old records in the same time that another one is writing its own log?

    Bye,
    Andrea

  3. #3
    Join Date
    Feb 2011
    Posts
    152

    Default

    Yes, that is exactly what is happening. I want all transformations to log to the same step and channel tables. Is there a way to make that happen. I really do not want separate log tables for each transformation.

  4. #4
    Join Date
    Jun 2011
    Posts
    102

    Default

    I don't know how to do that with multiple transformation running in parallel, but, for istance, when i've got one trasformation with multiple "flows", i use the "block this step until steps finish" step to select which step have to write first, second and so on.
    the two ideas that i'm thinkin' about are:
    1- write multiple log and create a transformation that merge all of them in one file;
    2- instead of multiple transformation, create only one that will contain all of yours, and use the "block" step.
    HTH,
    Andrea

  5. #5

    Default

    Hi,

    I am trying to switch our logging to tables, too.
    As I got the following error while WRITING with two processes into the logging table ( oracle based ) I assume that it isn't a good idea to have one single logging table :

    ERROR 31-08 15:19:21,742 - xxxx - A serious error occurred during job execution:
    Unable to begin processing by logging start in logtable DWH_LOG_JOB

    Couldn't execute SQL: LOCK TABLE DWH_LOG_JOB IN EXCLUSIVE MODE
    I did not have 'log record timeout (in days)' checked.

    As there are always some independant jobs running parallel in my jobnet I don't
    feel that implementing an own concurrency handling with 'blocking steps' very handy.

    Did you try with kettle 4.2 ? I found some solved bugs regarding logging
    in jira that should be fixed by now.

    Regards,
    Peter

  6. #6
    Join Date
    Feb 2011
    Posts
    152

    Default

    I have not tried 4.2 yet. I see the RC has been out for a while, so I thought I would wait until GA came out. Any ideas as to how much longer that will be?

  7. #7

    Default

    yes - i saw on twitter that 4.2GA shall be available next weekend

  8. #8
    Join Date
    Feb 2011
    Posts
    152

    Default

    I will wait for 4.2GA on this and try again. Will post back with my findings.

  9. #9
    Join Date
    Feb 2011
    Posts
    152

    Default

    I just tried with 4.2GA and I get the same results. Also I'm having trouble loading the transformations in parallel from the DB repository, not just cleaning out the log tables.

  10. #10

    Default

    We had the same issui woth the logging tables in 4.2.0 and DB2 (LUW). It seems to be a problem of the batch_id / id_batch.
    If you do not need the batch_id, remove this field from the log settings. With DB2, everything works fine (so far, we started testing a few days before), even with high parallelism in jobs .

  11. #11
    Join Date
    Nov 1999
    Posts
    9,729

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.