delete and than insert data in vertica database table
the job reads data from file and then deletes data from table XYZ with msisdn as key and then inserts the data into same table XYZ with msisdn as key tfileinputdelimited---->main--->tverticaoutput(delete)------->main------>tverticaoutput(insert) the job is working fine for 200 rows and then getting following error: Exception in component tVerticaOutput_3 java.sql.SQLException: (5156) ERROR: Unavailable: initiator locks for query - Locking failure: Timed out I locking Tableublic.AKKI_TEST. X held by . Your current transaction isolation level is SERIALIZABLE at com.vertica.util.ServerErrorData.buildException(Unknown Source) at com.vertica.dataengine.VQueryExecutor.readCopyStartResponse(Unknown Source) at com.vertica.dataengine.VQueryExecutor.handleExecuteResponse(Unknown Source) at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source) at com.vertica.jdbc.SPreparedStatement.executeBatch(Unknown Source) at master_project.ip_bill_kpi_delete_0_1.IP_Bill_KPI_DELETE.tWaitForFile_3Process(IP_Bill_KPI_DELETE.java:50733) at master_project.ip_bill_kpi_delete_0_1.IP_Bill_KPI_DELETE$7.run(IP_Bill_KPI_DELETE.java:51266) Caused by: com.vertica.util.ServerException: (5156) ERROR: Unavailable: initiator locks for query - Locking failure: Timed out I locking Tableublic.AKKI_TEST. X held by . Your current transaction isolation level is SERIALIZABLE ... 7 more
Re: delete and than insert data in vertica database table
This is a bad design for a column based database like vertica. I suggest keep all data for the insert into a file and insert them in a new subjob and not in the same flow. Column based database like Vertica does not work well if you trying delete or insert dataset one by one. Vertica should always be filled with batch mode and if you delete and insert into the same table similar this causes problems.