potiuk edited a comment on pull request #15714: URL: https://github.com/apache/airflow/pull/15714#issuecomment-841810275
I think the change is needed mostly because of the locking behavior in those different isolation levels. We are using row-level locking in MySQL 8 with HA scheduler and if you look at the behaviour of the default REPEATABLE READ if some search criteria are used when locking the row, MYSQL will also lock the gaps between locked rows matching the criteria not only the locked rows, which likely causes the deadlocs mentioned by @SamWheating . In case of READ COMMITTED the gaps between rows are never locked. There is a problem that inserts could create phantom rows in this case, but this is really not the case in our way of using the locks (they are read/update locks, not inserts). Even if more rows are initially locked during the WHERE clause, after the Where clause is evealuated and rows are selected for locking, for the rows that are not selected, the locks are freed. SERIALIZABLE behaves more like REPEATABLE READ but it converts the selects into SELECT FOR SHARE so those are "red allowed /write locked". But I think this does not solve the problem because in case of HA schedules we have several schedules trying to run SELECT FOR UPDATE on the same table with some selection criteria, which means that the deadlocks are still possible. As far as I see the change is sound :) More information here: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html @SamWheating @ashb - did I get this correctly ? @alippai - does it sound convincing? -- 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org
