delete and than insert data in vertica database table

One Star

delete and than insert data in vertica database table

hi

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 TableSmiley Tongueublic.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 TableSmiley Tongueublic.AKKI_TEST. X held by . Your current transaction isolation level is SERIALIZABLE
... 7 more
Seventeen Stars

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.