Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Inserts on production enviroment concurrently

  1. #1

    Default Inserts on production enviroment concurrently

    Good morning, I need your help.


    I am working with 3 tables in my BBDD production enviroment in Mysql.


    In those tables do inserts from several systems concurrently, and one of them is through pentaho PDI.


    I would like to know the best way to avoid having problems by updating the table concurrently from various origins and other tables related to them also in production.

  2. #2

    Default

    I have checked this wiki post:

    https://wiki.pentaho.com/display/EAI...current+access

    But it does not solve my problem.

    I have this flow:

    Step 1.- I take the last id from a table (Table_Origin)
    Step 2.- I make several transformation with data associated to that id
    Step 3.- I insert a row in the Table_Origin, and an inset in another table (Second_Table) with a field called Table_Origin_id_fk

    The problem is that there are several origins which are updating and inserting data in the tables while I'm making transformations in the step 2.

  3. #3

    Default

    By the way I'm using Version 8.0.0.0-28.

    I have check that the option "Make a Job Database Transactional " is only available for Enterprise Edition.

    https://help.pentaho.com/Documentati...d_Job_Rollback

  4. #4
    Join Date
    Aug 2016
    Posts
    290

    Default

    You can make transformation transactional, that's available for free version.

  5. #5
    Join Date
    Jul 2009
    Posts
    476

    Default

    Step 1.- I take the last id from a table (Table_Origin)
    Step 2.- I make several transformation with data associated to that id
    Step 3.- I insert a row in the Table_Origin, and an inset in another table (Second_Table) with a field called Table_Origin_id_fk
    Trying to take the last ID cannot work in a concurrent environment. It's impossible.

    Use a sequence-generated ID value in Table_Origin instead. I'm not very familiar with MySQL, but it looks like it has an AUTO_INCREMENT feature for this purpose. What you need to do is insert the new row in Table_Origin, returning the AUTO_INCREMENT column value for the ID, and then use that value for the insert into your second table.

    Then it won't matter if other processes are inserting other rows into Table_Origin at the same time.

    You will also have to rewrite the code for those other processes that insert using the last ID, so that they use the sequence-generated value instead.

  6. #6

    Default

    Ok, really thanks a million.

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.