Bug #117797 | Lost Update WITH CONSISTENT SNAPSHOT | ||
---|---|---|---|
Submitted: | 26 Mar 5:55 | Modified: | 4 Jun 2:18 |
Reporter: | Huicong Xu | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
Version: | 9.2.0 | OS: | Any (Ubuntu 22.04) |
Assigned to: | CPU Architecture: | Any |
[26 Mar 5:55]
Huicong Xu
[27 Mar 19:51]
MySQL Verification Team
Hi, I believe this is not a bug but I will verify so that optimizer team can doublecheck.
[22 Apr 14:26]
Jakub Lopuszanski
Posted by developer: Hi, I believe this is not a bug - MySQL seems to be using a different definition of REPEATABLE READ than Maria DB. In MySQL's implementation of InnoDB, a REPEATABLE READ transaction sees one of two worlds depending on the nature of particular query: - for non-locking SELECTS we use a read-view - for UPDATEs,DELETEs,INSERTs and locking SELECTs we use the most recent state of the DB More importantly, we make no promise or effort to check that the two worlds are consistent with each other. Apparently Maria DB goes an extra mile to verify that the rows which are accessed during UPDATEs have not been updated since the read-view creation. We don't. IMO it would be a major effort to modify InnoDB to do that properly, as it wouldn't be backward-compatible. As such, I don't think we can address such feature request via bug report/bug fix. Note that the behaviour you see is documented: https://dev.mysql.com/doc/refman/9.3/en/innodb-transaction-isolation-levels.html > It is not recommended to mix locking statements (UPDATE, INSERT, DELETE, or SELECT ... FOR ...) with non-locking SELECT statements in a single REPEATABLE READ transaction, because typically in such cases you want SERIALIZABLE. This is because a non-locking SELECT statement presents the state of the database from a read view which consists of transactions committed before the read view was created, and before the current transaction's own writes, while the locking statements use the most recent state of the database to use locking. In general, these two different table states are inconsistent with each other and difficult to parse.
[4 Jun 2:18]
Huicong Xu
We judge this bug based on the definition of lost update, which is really inconsistent with the MySQL implementation. In the example above, we do see the lost update occurring. Similar to the bug mentioned in the paper with Semantic Conformance Testing of Relational DBMSs.