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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to