Skip to main content
Question

How to overcome "DDL statement interfered with this statement"

  • January 9, 2022
  • 4 replies
  • 10 views

kfruchtman
Forum|alt.badge.img+2

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

4 replies

mosheg
Forum|alt.badge.img+2
  • Participating Frequently
  • January 9, 2022

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
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • January 26, 2022

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
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • January 26, 2022

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
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • January 26, 2022

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