potiuk commented on PR #60166: URL: https://github.com/apache/airflow/pull/60166#issuecomment-3722512943
> Thinking about it, the deadlock isn’t really caused by the subquery, right? Yep as @msumit wrote. It's exactly the subquery. Simply MySQL in this case chooses to first run subquery (with shared lock) and after subquery is run - it runs DELETE (with exclusive lock - while still keeping the shared lock). So the sequence of operations is: * SHARED lock on SELECT SUBQUERY (a) * get list of things to delete # <- here in another process parallell UPDATE operation might happen which tries to acquire EXCLUSIVE lock (b) * EXCLUSIVE lock to DELETE without releasing lock a (b) Classic deadlock. One process keeps lock (a), another process want to get (b) - but can't because of (a) - and the first process tries to get (b) and failes because the other process is waiting for (b). This is the design flaw I've been talking about and the exact reason why we fight with deadlocks in MySQL, because the way how InnoDB engine works has this deep design flaw, that rather than acquiring all locks it needs for a single query, it attempts to acquire those locks separately in a non-atomic operation. Postgres is able to optimize it away and acquires all locks it needs in a single, atomic operation for such query - and that's the reason we see less of those deadlocks. -- 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]
