query limit and with_for_update of clause(oracle) v1.1.02b
When i use LIMIT and FOR UPDATE OF, I got the invalid sql query
1. If I use only with_for_update(of=column), then i get valid sql.
For example, query:
rs.query(table)\
.select_from(schema.SyncFlags)
.outerjoin(table, schema.SyncFlags.row_id ==table.__table__.primary_key.columns.values()[0])
.filter(
schema.SyncFlags.cache_group_id == cg_id,
schema.SyncFlags.table_name == table.__tablename__
)\
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)
valid SQL:
SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1 FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED
2. When I add LIMIT clause, then query builder add parent query and my FOR UPDATE OF column reference become invalid. It is valid in subquery, but invalid in parent query.
Example:
rs.query(table)\
.select_from(schema.SyncFlags)\
.outerjoin(table, schema.SyncFlags.row_id==table.__table__.primary_key.columns.values()[0])\
.filter(
schema.SyncFlags.cache_group_id == cg_id,
schema.SyncFlags.table_name == table.__tablename__
)\
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)\
.limit(1000)
Invalid SQL:
SELECT user_account_id_user_acc_1, user_account_id_type, user_account_id_user, user_account_id_predpr
FROM (SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1)
WHERE ROWNUM <= :param_1 FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED
3. Work around(not portable): use filter criteria:
rs.query(table)
.select_from(schema.SyncFlags)
.outerjoin(table, schema.SyncFlags.row_id == table.__table__.primary_key.columns.values()[0])
.filter(
schema.SyncFlags.cache_group_id == cg_id,
schema.SyncFlags.table_name == table.__tablename__,
text("rownum <= :rownum").bindparams(bindparam('rownum', value=5, type_=Integer))
)
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)
Valid SQL:
SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1 AND rownum <= :rownum FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED
Comments (11)
-
repo owner -
repo owner nope
for_update_clause
The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries.
-
repo owner -
repo owner -
reporter On orale-10.2, for update do not permitted in subquery.
Second work around:
select needed_columns..., column_for_update_inner_alias
from (
select needed_columns..., column_for_update as column_for_update_inner_alias
from ....
)
where rownum <= 1000
for update of column_for_update_inner_alias
On Sqlalchemy:
.with_for_update(of=column_for_update_inner_alias???)
-
reporter Thanks
-
repo owner - changed status to resolved
Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes:
#3741→ <<cset f2ee514c757f>>
-
repo owner Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes:
#3741(cherry picked from commit d23797943786914cced58ed71ffb7b749e64c449)→ <<cset d99ec752c752>>
-
repo owner Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes:
#3741→ <<cset d23797943786>>
-
repo owner Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach to accommodate for the "OF" clause in a "FOR UPDATE" phrase. The column expressions must be added to the selected subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1 Fixes:
#3741(cherry picked from commit d23797943786914cced58ed71ffb7b749e64c449)→ <<cset 271d4e9e712b>>
-
reporter Great! Thank you, Michael!
- Log in to comment
can you clarify this please? As I was not given a model here to work with, can you please test if the "FOR UPDATE" clause can now be specified in subqueries ? Oracle has not allowed this in the past. thanks.