Saturday, March 21, 2020

Clearing table locks in MariaDB ColumnStore (MCS)

If you encounter the following error running MCS:

Internal error: CAL0009: Truncate table failed:  IDB-2009: Unable to perform the cpimport operation because 30084 with PID -1 is currently holding the table lock for session .  

It's due to locks placed on the objects.  Use the following command to view locked objects in MCS:

$ /usr/local/mariadb/columnstore/bin/viewtablelock

 There are 3 table locks

 Table                                   LockID  Process   PID    Session   Txn    CreationTime              State    DBRoots  
 db.table1  159107  DMLProc   43294  614       79082  Thu Mar 19 03:40:37 2020  LOADING  1        
 db.table2            159120  DMLProc   43294  602       79085  Thu Mar 19 05:00:43 2020  LOADING  1        
 db2.table3      159129  cpimport  30084  BulkLoad  n/a    Thu Mar 19 05:15:53 2020  LOADING  1        

Now that you have the list of locked objects, clear the table locks by LockID:

$ /usr/local/mariadb/columnstore/bin/cleartablelock 159129
Rolling back and clearing table lock for table db2.table3; table lock 159129

Sending rollback request to PM1...
Successful rollback response from PM1
Sending cleanup request to PM1...
Successful cleanup response from PM1

Table lock 159129 for table db2.table3 is cleared.