Render FOR UPDATE on the inner subquery as well as the outer

The ORM now doubles the "FOR UPDATE" clause within the subquery that
renders in conjunction with joined eager loading in some cases, as it has
been observed that MySQL does not lock the rows from a subquery.   This
means the query renders with two FOR UPDATE clauses; note that on some
backends such as Oracle, FOR UPDATE clauses on subqueries are silently
ignored since they are unnecessary.  Additionally, in the case of the "OF"
clause used primarily with Postgresql, the FOR UPDATE is rendered only on
the inner subquery when this is used so that the selectable can be targeted
to the table within the SELECT statement.

Change-Id: Ie5520d08d82bf0afd9e1bd2d43a0b2a0db0de16d
Fixes: #4246

1 files changed, 10 insertions, 1 deletions

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 6d2b144e3..ea8371f50 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -3493,6 +3493,9 @@ class Query(object):
             order_by=context.order_by,
             **self._select_args
         )
+        # put FOR UPDATE on the inner query, where MySQL will honor it,
+        # as well as if it has an OF so Postgresql can use it.
+        inner._for_update_arg = context._for_update_arg
 
         for hint in self._with_hints:
             inner = inner.with_hint(*hint)
@@ -3510,7 +3513,13 @@ class Query(object):
             [inner] + context.secondary_columns,
             use_labels=context.labels)
 
-        statement._for_update_arg = context._for_update_arg
+        # Oracle however does not allow FOR UPDATE on the subquery,
+        # and the Oracle dialect ignores it, plus for Postgresql, MySQL
+        # we expect that all elements of the row are locked, so also put it
+        # on the outside (except in the case of PG when OF is used)
+        if context._for_update_arg is not None and \
+                context._for_update_arg.of is None:
+            statement._for_update_arg = context._for_update_arg
 
         from_clause = inner
         for eager_join in context.eager_joins.values():