What is wrong with this syntax of a multi-table DELETE...ORDER BY?

Question

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.

Answer 1

(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

Answer 2

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.

Answer 3

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 
WHERE EXISTS
  ( 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.