delete and than insert data in vertica database table

_AnonymousUser — Thu, 28 Nov 2013 06:26:03 GMT

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 Table ublic.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 Table ublic.AKKI_TEST. X held by . Your current transaction isolation level is SERIALIZABLE
... 7 more

Re: delete and than insert data in vertica database table

Anonymous — Thu, 28 Nov 2013 22:55:04 GMT

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.

topic delete and than insert data in vertica database table in Talend Studio

delete and than insert data in vertica database table

Re: delete and than insert data in vertica database table