https://jepsen.io/analyses/mysql-8.0.34 highlights that the
transaction isolation levels in the InnoDB storage engine do not
correspond to any widely accepted definitions, such as
"Generalized Isolation Level Definitions"
https://pmg.csail.mit.edu/papers/icde00.pdf
(PL-1 = READ UNCOMMITTED, PL-2 = READ COMMITTED, PL-2.99 = REPEATABLE READ,
PL-3 = SERIALIZABLE).
Only READ UNCOMMITTED in InnoDB seems to match the above definition.
The issue is that InnoDB does not detect write/write conflicts
(Section 4.4.3, Definition 6) in the above.
It appears that as soon as we implement write/write conflict detection
(SET SESSION innodb_snapshot_isolation=ON), the default isolation level
(SET TRANSACTION ISOLATION LEVEL REPEATABLE READ) will become
Snapshot Isolation (similar to Postgres), as defined in Section 4.2 of
"A Critique of ANSI SQL Isolation Levels", MSR-TR-95-51, June 1995
https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf
Locking reads inside InnoDB used to read the latest committed version,
ignoring what should actually be visible to the transaction.
The added test innodb.lock_isolation illustrates this. The statement
UPDATE t SET a=3 WHERE b=2;
is executed in a transaction that was started before a read view or
a snapshot of the current transaction was created, and committed before
the current transaction attempts to execute
UPDATE t SET b=3;
If SET innodb_snapshot_isolation=ON is in effect when the second
transaction was started, the second transaction will be aborted with
the error ER_CHECKREAD. By default (innodb_snapshot_isolation=OFF),
the second transaction would execute inconsistently, displaying an
incorrect SELECT COUNT(*) FROM t in its read view.
If innodb_snapshot_isolation=ON, if an attempt to acquire a lock on a
record that does not exist in the current read view is made, an error
DB_RECORD_CHANGED (HA_ERR_RECORD_CHANGED, ER_CHECKREAD) will
be raised. This error will be treated in the same way as a deadlock:
the transaction will be rolled back.
lock_clust_rec_read_check_and_lock(): If the current transaction has
a read view where the record is not visible and
innodb_snapshot_isolation=ON, fail before trying to acquire the lock.
row_sel_build_committed_vers_for_mysql(): If innodb_snapshot_isolation=ON,
disable the "semi-consistent read" logic that had been implemented by
myself on the directions of Heikki Tuuri in order to address
https://bugs.mysql.com/bug.php?id=3300 that was motivated by a customer
wanting UPDATE to skip locked rows that do not match the WHERE condition.
It looks like my changes were included in the MySQL 5.1.5
commit ad126d90e019f223470e73e1b2b528f9007c4532; at that time, employees
of Innobase Oy (a recent acquisition of Oracle) had lost write access to
the repository.
The only reason why we set innodb_snapshot_isolation=OFF by default is
backward compatibility with applications, such as the one that motivated
the implementation of "semi-consistent read" back in 2005. In a later
major release, we can default to innodb_snapshot_isolation=ON.
Thanks to Peter Alvaro, Kyle Kingsbury and Alexey Gotsman for their work
on https://github.com/jepsen-io/ and to Kyle and Alexey for explanations
and some testing of this fix.
Thanks to Vladislav Lesin for the initial test for MDEV-26643,
as well as reviewing these changes.
Tests with checking metadata or that cannot be run with
the view-protocol are excluded from --view-protocol.
For tests that do not allow the use of an additional connection,
the util connection is disabled with "--disable_service_connection".
Also cases with bugs for --view-protocol are disabled.
Remove usage of deprecated variable storage_engine. It was deprecated in 5.5 but
it never issued a deprecation warning. Make it issue a warning in 10.5.1.
Replaced with default_storage_engine.
'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
automatically after the this patch.
BUG#55617 is fixed by this patch too.
This is the 5.5 part.
It implements:
- 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
anything and binlog anything if the table already exists.
It only generate a warning that table already exists.
- A couple of test cases for the behavior changing.
into moksha.local:/Users/davi/mysql/push/bugs/25164-5.1
sql/lock.cc:
Auto merged
mysql-test/include/deadlock.inc:
Auto merged
mysql-test/r/deadlock_innodb.result:
Auto merged
WL#3397 Refactoring storage engine test cases (for falcon)
It contains also fixes according to code review.
Contents: Testcases which were in history dedicated to InnoDB or MyISAM only.
Modifications:
1. Shift the main testing code into include/<testing field>.inc
Introduce $variables which can be used to omit tests for features which are not supported by
certain storage engines.
2. The storage engine to be tested is assigned within the toplevel script (t/<whatever>_<engine>.test)
via variable $engine_type and the the main testing code is sourced from
include/<testing field>.inc
3. Some toplevel testscripts have to be renamed to
- avoid immediate or future namespace clashes
- show via filename which storage engine is tested
4. Minor code cleanup like remove trailing spaces, some additional comments ....
mysql-test/t/unsafe_binlog_innodb-master.opt:
Rename: mysql-test/t/innodb_unsafe_binlog-master.opt -> mysql-test/t/unsafe_binlog_innodb-master.opt
mysql-test/r/read_many_rows_innodb.result:
Rename: mysql-test/r/innodb-big.result -> mysql-test/r/read_many_rows_innodb.result
mysql-test/t/cache_innodb-master.opt:
Rename: mysql-test/t/innodb_cache-master.opt -> mysql-test/t/cache_innodb-master.opt
mysql-test/t/concurrent_innodb-master.opt:
Rename: mysql-test/t/innodb_concurrent-master.opt -> mysql-test/t/concurrent_innodb-master.opt
BitKeeper/deleted/.del-index_merge.result:
Delete: mysql-test/r/index_merge.result
BitKeeper/deleted/.del-index_merge_innodb.result:
Delete: mysql-test/r/index_merge_innodb.result
BitKeeper/deleted/.del-index_merge_innodb2.result:
Delete: mysql-test/r/index_merge_innodb2.result
BitKeeper/deleted/.del-index_merge_ror.result:
Delete: mysql-test/r/index_merge_ror.result
BitKeeper/deleted/.del-index_merge_ror_cpk.result:
Delete: mysql-test/r/index_merge_ror_cpk.result
mysql-test/r/index_merge_innodb.result:
BitKeeper file /home/matthias/Arbeit/mysql-5.1-engines/src-1/mysql-test/r/index_merge_innodb.result
mysql-test/t/index_merge_innodb.test:
BitKeeper file /home/matthias/Arbeit/mysql-5.1-engines/src-1/mysql-test/t/index_merge_innodb.test
mysql-test/t/index_merge_myisam.test:
BitKeeper file /home/matthias/Arbeit/mysql-5.1-engines/src-1/mysql-test/t/index_merge_myisam.test
mysql-test/include/concurrent.inc:
1. This file contains now the main testing code of the former t/innodb_concurrent.test.
2. It is now sourced by t/concurrent_innodb.test.
mysql-test/include/deadlock.inc:
1. This file contains now the main testing code of the former t/innodb-deadlock.test
2. It is now sourced by t/deadlock_innodb.test.
mysql-test/include/handler.inc:
1. This file contains now the main testing code of the former t/innodb_handler.test + t/handler.test.
2. It is now sourced by t/handler_myisam.test and t/handler_innodb.test.
mysql-test/include/index_merge1.inc:
1. This file contains now the main testing code of the former t/index_merge.test.
2. It is now sourced by t/index_merge_myisam.test.
mysql-test/include/index_merge2.inc:
1. This file contains now the main code of t/index_merge_innodb.test.
2. It is sourced by t/index_merge_myisam.test and t/index_merge_innodb.test.
mysql-test/include/index_merge_2sweeps.inc:
1. This file contains now the main code of the former t/index_merge_innodb2.test.
2. It is sourced by t/index_merge_myisam.test and t/index_merge_innodb.test.
mysql-test/include/index_merge_ror.inc:
1. This file contains now the main code of the former t/index_merge_ror.test.
2. It is sourced by t/index_merge_myisam.test.
mysql-test/include/index_merge_ror_cpk.inc:
1. This file contains now the main testing code of the former t/index_merge_ror_cpk.test.
2. It is now sourced by t/index_merge_myisam.test and t/index_merge_innodb.test.
mysql-test/include/mix1.inc:
1. This file contains now the main testing code of the t/innodb_mysql.test
2. The name mix1.inc was used because the test contains subtests for different fields.
3. It is sourced by t/innodb_mysql.test.
4. Fixes:
- Assign $other_engine_type instead of hardcoded MyISAM.
- improve comment
- remove redundant subtest
- analyze table t4 instead of wrong table t1
- remove not needed "eval set storage_engine = $engine_type;"
mysql-test/include/mix2.inc:
1. This file is a copy of the main testing code of the t/innodb.test
A copy has to be used, because t/innodb.test is to be maintained by INNOBASE only.
2. The name mix2.inc was used because the test contains subtests for different fields.
3. It is sourced by t/mix2_myisam.test.
4. Fixes:
- improved comment
- additional "eval SET SESSION STORAGE_ENGINE = $other_engine_type;" at beginning of tests
- assign $other_engine_type instead of hardcoded MyISAM or HEAP
- assign $other_engine_type where it is needed to preserve test logics
- correct logical bugs
- improve(extend) "checksum table" test
mysql-test/include/query_cache.inc:
1. This file contains now the main testing code of the former t/innodb_cache.test.
2. It is now sourced by t/cache_innodb.test
mysql-test/include/read_many_rows.inc:
1. This file contains now the main testing code of the former t/innodb_big.test.
2. It is now sourced by t/read_many_rows_innodb.test.
mysql-test/include/rowid_order.inc:
1. This file contains now the main testing code of t/rowid_order_innodb.test.
2. It is now sourced by t/rowid_order_innodb.test.
mysql-test/include/unsafe_binlog.inc:
1. This file contains now the main testing code of the former t/innodb_unsafe_binlog.test.
2. It is now sourced by t/unsafe_binlog_innodb.test.
mysql-test/r/cache_innodb.result:
Updated result
mysql-test/r/concurrent_innodb.result:
Updated result
mysql-test/r/deadlock_innodb.result:
Updated result
mysql-test/r/handler_innodb.result:
Updated result
mysql-test/r/handler_myisam.result:
Updated result
mysql-test/r/index_merge_myisam.result:
Updated result
mysql-test/r/innodb_mysql.result:
Updated result
mysql-test/r/mix2_myisam.result:
Updated result
mysql-test/r/rowid_order_innodb.result:
Updated result
mysql-test/r/unsafe_binlog_innodb.result:
Updated result
mysql-test/t/cache_innodb.test:
1. Renaming of t/innodb_cache.test to t/cache_innodb.test
2. Main code is now sourced from include/query_cache.inc.
mysql-test/t/concurrent_innodb.test:
1. Renaming of t/innodb_concurrent.test to t/concurrent_innodb.test
2. Main code is now sourced from include/concurrent.inc.
Attention: This test fails even in the old version. (BUG#21579).
--> added to t/disabled.def
mysql-test/t/deadlock_innodb.test:
1. Renaming of t/innodb_deadlock.test to t/deadlock_innodb.test
2. Main code is now sourced from include/deadlock.inc.
mysql-test/t/disabled.def:
Add the test concurrent_innodb because of
BUG#21579 2006-08-11 mleich innodb_concurrent random failures with varying differences
mysql-test/t/handler_innodb.test:
1. Renaming of t/innodb_handler.test to t/handler_innodb.test
2. Main code is now sourced from include/handler.inc.
include/handler.inc = united code of former t/handler.test and t/innodb_handler.test
mysql-test/t/handler_myisam.test:
1. Renaming of t/handler.test to t/handler_myisam.test
2. Main code is now sourced from include/handler.inc.
include/handler.inc = united code of former t/handler.test and t/handler_innodb.test.
mysql-test/t/innodb_mysql.test:
1. Main code is now sourced from include/mix1.inc.
2. Test was not renamed because t/innodb.test refers to it.
mysql-test/t/mix2_myisam.test:
New test: MyISAM variant of mix2 ( = t/innodb.test)
mysql-test/t/read_many_rows_innodb.test:
1. Renaming of t/innodb_big.test to t/read_many_rows_innodb.test
2. Main code is now sourced from include/read_many_rows.inc.
mysql-test/t/rowid_order_innodb.test:
Main code is now sourced from t/rowid_order.inc.
mysql-test/t/unsafe_binlog_innodb.test:
1. Renaming of t/innodb_unsafe_binlog.test to t/unsafe_binlog_innodb.test
2. Main code is now sourced from include/unsafe_binlog.inc.
