I have the following query:

DELETE proxies, pxpl FROM proxies
JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID
WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR)
ORDER BY pxpl.ProxyListID, proxies.ID

The ordering is needed to avoid DB deadlocks. However, it yields an error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY pxpl.ProxyListID, proxies.ID' at line 4

proxy_xref_proxy_list is a many-to-many relation association table.

  • Is that join (ON proxies.ID=pxpl.ProxyID) on a foreign key? Show us the definitions of the tables, please. – ypercubeᵀᴹ Jun 11 at 11:35
  • @ypercubeᵀᴹ, please, see the definitions of the tables in another my question: dba.stackexchange.com/questions/268954/… . Yes, pxpl.ProxyID is a foreign key into proxies table (column ID). Also, pxpl.ProxyListID is a foreign key. – Serge Rogatch Jun 11 at 17:49

(Serge self-Answered the title question; this Answer tries to look beyond that.)

If the deadlock is caused by the same DELETE being run from multiple threads, then simply ignore the deadlock; the other thread will do the work.

If there are other concerns, there are ways to use subqueries instead of a JOIN. There is even a kludge where an extra level of subquery is a workaround for another error you may encounter.

If you have massive deletes, then see this for tips: http://mysql.rjweb.org/doc.php/deletebig

| improve this answer | |

Apparently, ORDER BY is not supported in a multi-delete query, according to the doc:

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT> cannot be used. A DELETE can also reference tables which are located in different databases; see Identifier Qualifiers for the syntax.

| improve this answer | |

One problem I see is that your DELETE will try to delete from both tables when a row exists in pxpl with WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR). But the related row (through the FK) in proxies maybe related to more rows in pxpl that do not match the criteria. And because the FK has ON DELETE CASCADE, it will try to delete them as well. That maybe one reason for the deadlocks you see (when there is no ORDER BY).

You can try to delete only from the parent table and let the cascade action do its work and delete all the ratled rows in the child table, say with:

DELETE FROM proxies 
  ( SELECT 1 
    FROM proxy_xref_proxy_list AS pxpl 
    WHERE proxies.ID = pxpl.ProxyID
      AND pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR)
  ) ;

You could even use ORDER BY this way, although I don't think you need it.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.