Are subqueries supported in updates?

[Tim]

Are there any known issues using subqueries for updates?

I have some statements in this form: 

    UPDATE `totals_table` SET `value` = ( SELECT SUM(`value`) FROM `subtotals_table` );

Similarly, with deletes:

   DELETE FROM `some_table` WHERE `id` NOT IN ( SELECT `id` FROM `other_table`);

I get various deadlocks in my application, but retrying transactions sometimes causes tables to lock up, as I reported separately here:
https://groups.google.com/forum/#!starred/codership-team/5NrPJtpzxAQ

I'm just trying to work out whether there are certain types of queries causing problems that need to be rewitten. The examples above seem to be involved in more deadlocks than others.

[lorraine.p...@percona.com]

Hi Tim

Wondering if this recent blog post might help explain / answer?

https://www.percona.com/blog/2017/09/25/avoid-shared-locks-from-subqueries-when-possible/ 

[Seppo Jaakola]

Galera supports subqueries. In fact, for Galera replication, it does matter how rows are modified by a transaction,
be it regular transaction, subquery, stored procedure, prepared statement or trigger - they all modify some database rows and populate a write set for replicating these changes. However, there can be difference in how and which rows are locked during the transaction processing. With subqueries, the updated/deleted rows are write locked (as usual), but on top of that also the selected rows are locked with shared lock.

And, for multi-master conflicts, only thing what matters is which rows are locked by the transaction at commit time and how long these rows are locked. These two factors add up as vulnerability for multi-master conflict. If some other transaction, in some other node, writes over the locked rows, during this time window - then you will see a multi-master conflict. And for subqueries, this vulnerability is higher because of wider locked rows target.

If multi-master conflicts cause trouble, you might want to think it over if locking the selected rows is necessary for application logic.

-seppo