1

My gear:

  • Pentaho BI Server 8.0
  • AS400 V7

My scenario:

I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to finish.

Because I can't predict the time when this ETL finishes updating the rows, I must insert new rows from other process that match the conditions of the update ETL that was running previously.

Until this post, I had to stop both processes and run them manually because they never finish (24h+).

In the simultaneous UPDATE/INSERT scenario, what is the expected behavior from SQL database?, the new incoming rows from the insert are gonna be part of the previous update?, is there a race condition where the update process can process the new inserted rows?, are both processes independent, so, the update process will take in count only the rows that were in the table before the insert?, are gonna get locks activated at row level here by default?

| improve this question | |
  • 1
    What isolation level are you running at? dbatodba.com/db2/how-to-do/what-are-the-db2-isolation-levels. One way that I've resolved this issue in MSSQL is to do the ETL in small batches, which lets other processes get in and do work in between. Depending on what each process is doing, that may work for you. – Jonathan Fite May 3 '18 at 14:59
  • @JonathanFite actually I'm using "read-uncommited". Actually I'm using 10000 rows batches (I do almost 10M inserts in each process), but it seems like the database knows that there is a transaction on the table and keeps seeking it, because never ends until I stop the insert ETL process. – ChoCho May 3 '18 at 16:07
  • I'm not familiar enough with DB2 to offer more specific guidance, but are you running COMMIT (or equivalent) after each batch of 10K? Have you tried turning the number of rows per batch down? Maybe a sleep? In MSSQL working on a large set of records can escalate the lock to the entire table. – Jonathan Fite May 4 '18 at 12:27
  • Yes, Pentaho Spoon (ETL tool) pushes commit after each batch. I will try reducing the number of rows per batch, seems like there is row level lock. But under row level lock I think there is nothing I can do until the lock gets released, or am I missing some concept there? – ChoCho May 4 '18 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.