notatallshaw-gts commented on PR #72062:
URL: https://github.com/apache/airflow/pull/72062#issuecomment-5701954300
> I'll note that this did fix some of our problems with mysql and deferred
tasks getting orphaned, but not all of them. So if another poor soul is running
into this problem before it gets fixed -- don't just copy this fix and hope it
fixes all of the issues!
This was very helpful @aunderwood24thanks!, I was hitting this, and I can
reproduce this on MySQL 8.0.36.
With two sessions A and B, default REPEATABLE READ, A reads to fix its
snapshot, then runs the candidate select and the delete. B defers a task in one
commit, inserting a trigger and pointing a task instance at it, I see:
| A's snapshot | DELETE re-checks predicates | task_instance |
|---|---|---|
| before B commits | no | **deleted** |
| before B commits | yes | survives |
| after B commits | no | survives, candidate select returns no id |
Taking the review comments from @rjgoyln and my own reproduction this is my
patch:
```diff
# Get all triggers that have no task instances, assets, or
callbacks depending on them and delete them
- ids = select(cls.id).where(
+ unreferenced = (
~cls.assets.any(),
~cls.callback.has(),
~cls.task_instance.has(),
)
+ ids = select(cls.id).where(*unreferenced)
ids = with_row_locks(ids, session, of=cls, skip_locked=True,
key_share=False)
if get_dialect_name(session) == "mysql":
- # MySQL doesn't support DELETE with JOIN, so we need to do it
in two steps
+ # MySQL cannot DELETE with a subquery on the target table, so
the ids are
+ # materialised. The DELETE re-checks the predicates because the
list goes stale
+ # and task_instance.trigger_id is ON DELETE CASCADE:
apache/airflow#72061
ids_list = list(session.scalars(ids).all())
session.execute(
-
delete(Trigger).where(Trigger.id.in_(ids_list)).execution_options(synchronize_session=False)
+ delete(Trigger)
+ .where(Trigger.id.in_(ids_list), *unreferenced)
+ .execution_options(synchronize_session=False)
)
else:
session.execute(
```
I likely won't be able to upstream this because I am leaving my current job
in ~2 weeks opening, and opening PRs to OSS projects is an internal
bureaucratic process.
I'm also writing a DAG watchdog to catch race conditions on tasks that get
deadlocked and automatically recover them.
In general during our testing phase it seems 3.3.1 is a lot less stable than
2.11.2 for us 🙁 .
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
