How to overcome "DDL statement interfered with this statement"

kfruchtman

Hi Experts!
I am experiencing many issues with my 10.x vertica.I get a lot of failed queries per day with the reason
"DDL statement interfered with this statement"
what is a good way to deal with this in Vertica?

The backround:
I am pumping data to the db every 5 minutes to a specific table from other databases. so I can imagine that sometimes it is trying to run DDL statements on the same table and this issue is happening.
Is there a way to workaround this problem? e.g - put the queries in some kind of queue in Vertica maybe?

Thanks in advance
Keren

mosheg

Hello Keren,
An object acquires an O lock (Owner lock) in some DDL operations, such as DROP_PARTITIONS, TRUNCATE TABLE, and ADD COLUMN. When an object is locked in O mode, it cannot be locked simultaneously by another transaction in any mode.
For many files or bulk-loads it is advised to use one COPY statement and for trickle feed to use INSERT statements.
An Insert (I) lock is required to insert data into a table and multiple transactions can lock an object in Insert mode simultaneously, enabling multiple inserts and bulk loads to occur at the same time.

kfruchtman

Hi!
Thx for the answer.I am still a bit confused and cannot overcome this issue.
I am using MERGE statements every5 minutes and getting this error.No other transaction is happening at the same time other than a simple select statement.
So I am not sure how to debug this issue further.Can I get some more hints?
Keren

kfruchtman

I am attaching the failed queries log from the vertica mc maybe that can help.
I think sometimes 2 MERGE statements happen at the same time.should it be the problem?
file attached.
Please advise!
Keren

kfruchtman

Here I attach the vertica.log, maybe it can help understand whey the DDL error occurres?