Compatibility broken with newer MariaDB versions / --innodb-snapshot-isolation=ON

[meisterT]

MariaDB version 11.6.2 (and perhaps other versions) set --innodb-snapshot-isolation=ON which causes multiple queries in DOMjudge to fail.

#2843 is likely part of a fix, perhaps #2844 also helps

Some more references:

• https://mariadb.com/resources/blog/isolation-level-violation-testing-and-debugging-in-mariadb/
• https://jira.mariadb.org/browse/MDEV-26642?orderby=created+DESC%2C+priority+DESC%2C+updated+DESC
• https://jira.mariadb.org/browse/MDEV-35124?orderby=created+DESC%2C+priority+DESC%2C+updated+DESC
• https://jepsen.io/analyses/mysql-8.0.34

[meisterT]

Examples how this can manifest itself:

• An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry \'1541319\' for key \'PRIMARY\'',
• An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1020 Record has changed since last read in table 'judgetask'
• SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
• An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1020 Record has changed since last read in table 'queuetask'

[meisterT]

To verify whether you are affected, you can run something like:

$ echo 'SHOW VARIABLES LIKE "innodb_snapshot_isolation"' | sudo mysql mysql
Variable_name	Value
innodb_snapshot_isolation	ON

[tuupke]

The flag causes problems when 2 transactions, update data that has been 'read-locked' (not the actual term). See the following interleaving of events:

1. Start transaction T1
2. Select some row R, adding it to the read-view of T1.
3. Start a second, parallel, transaction T2.
4. Update R within T2.
5. Commit T2.
6. Within T1 update R.
7. Depending on the value of innodb-snapshot-isolation this results in:
   0: The update query is executed. R has not been 'read-locked'.
   1: The update query fails. R has been 'read-locked'.

i.e. this flag causes issues when: a transaction selects some row R, which is then updated using a different transaction, and then updated in the first transaction.

Within the context of REPEATABLE READ the behavior of innodb-snapshot-isolation=1 can be considered to be more sensible since it ensures that data that has been read once is guaranteed to not change during a transaction. Consider a 'simple' bank app as an example. It is desirable to - within a transaction - retrieve account info, do some verification (taking up time), and only deducting funds when the balance is sufficient. Without innodb-snapshot-isolation=1 it would be possible for the balance to be after the initial retrieval, but before the updated balance gets committed and thus "invalidating" the verification since that verification was executed on stale data.