Re: [PR] Enforce READ COMMITTED isolation when using mysql [airflow]

["saolof (via GitHub)" <gi...@apache.org>]

saolof commented on PR #15714:
URL: https://github.com/apache/airflow/pull/15714#issuecomment-2173923907

   Reading this in hindsight, this change _is_ weird. 
   
   Postgres uses read committed as the default primarily because of backwards compatibility, it throws exceptions at higher isolation levels if it detects a write conflict, and the race detector was added more than a decade after the initial release because it required a huge amount of work. The postgres read committed implementation technically provides MAV (it guarentees a constant view of the database within any single statement) which is stronger than mysql's read committed (which guarentees only that each row is constant within any statement, so a mysql select can see some rows from before a multi-row update and some from after the update). 
   
   Mysql picked repeatable read isolation as the default because its read committed implementation, while technically being ANSI-compliant, offers much weaker consistency guarentees than other databases, so you should bump it up one level up from what you would use in other DBs.
   
   If you see concurrency issues at repeatable read, the way to fix them should be to bump the isolation level to SERIALIZABLE in all integration tests against postgres and fix all 40001 exceptions that postgres gives you, even if it that requires changing the schema to facilitate it. The actual problem here seems to be that mysql is bad at propagating error codes from a detected race condition to the user so that airflow used to continue after a transaction was aborted, while postgres + sqlalchemy will give you an explicit exception at higher isolation levels. Fixing the errors raised by postgres should also fix the concurrency issue that caused mysql to ignore transactions.


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org