[MCOL-2070] pentaho kettle adapter lock session with bulk columnstore and dml transaction(delete/update operation). Created: 2019-01-08  Updated: 2023-10-26  Resolved: 2019-03-02

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.1.6, 1.2.2
Fix Version/s: 1.1.7, 1.2.3

Type: Bug Priority: Blocker
Reporter: Nicola Assignee: Zdravelina Sokolovska (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Vmware 6.5 with 3 Performance Module and 2 user module.
Os : Oracle Linux 7
Pentaho : 8.1 with repository


Attachments: JPEG File job_pentaho.jpg    
Sprint: 2019-01, 2019-02

 Description   

Hi,
I've a job with Pentaho with some dml transaction (dml) and as the last step the bulk loader columnstore is launched.
The job failed caused by this error :
step until steps finish.0 - Finished processing (I=0, O=0, R=11, W=11, U=0, E=0)
2019/01/08 09:36:02 - Delete 2.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Error in step, asking everyone to stop because of:
2019/01/08 09:36:02 - Delete 2.0 - Error inserting/updating row
2019/01/08 09:36:02 - Delete 2.0 - (conn=79) Internal error: IDB-2009: Unable to perform the delete operation because mcsapi with PID 7599 is currently holding the table lock for session 103737.

In my opinion, when the job starts the bulk loader starts immediately and performs the exclusive lock of the table even if it will start as the last step, in fact, if the bulk step is disabled, the job is executed successfully.

This is very blocking for us because it binds us to perform complex jobs on pentaho.
Thanks,
Regards
Nicola Battista

 Comments   
Comment by Jens Röwekamp (Inactive) [ 2019-01-29 ]

I moved the initialization of ColumnStoreDriver and ColumnStoreBulkInsert from the step's init() method to the step's processRow() method.
This way the target table isn't blocked any more from the beginning of the step's execution, but only after the first row is processed.

I further added a test case to the regression test suite that tests this.

For QA:

  • Execute the regression test suite
  • I uploaded the Windows build to the folder MCOL-2070 in our Google Team Drive
  • Once the pull request is merged I will upmerge it into develop-1.2 and create a new Windows build for the 1.1.7 release.
Comment by Jojo [ 2019-05-29 ]

Just FYI for anyone who might be searching for a way to get rid of the lock on the table because of a hung mcsimport/cpimport job. The lock can be removed by restarting mysqld process.

$> pwd
 
/usr/local/mariadb/columnstore/bin
 
$> ./viewtablelock
 
 There is 1 table lock
 
 
 
  Table                              LockID  Process   PID    Session   Txn  CreationTime              State    DBRoots
 
  schema.data_table                     547     cpimport  15553  BulkLoad  n/a  Wed May 29 14:40:50 2019  LOADING  1
 
$> mcsadmin restartProcess mysqld pm1
 
restartprocess   Wed May 29 15:24:48 2019
 
 
 
This command restarts the processing of an application on a Module within the MariaDB ColumnStore System
 
           Do you want to proceed: (y or n) [n]: y
 
 
 
   Successful restart of Process mysqld
 
$> ./viewtablelock
 
 No tables are locked in the database.

Generated at Thu Feb 08 02:33:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.