Mysql-innodb transaction isolation mechanism

1. Transaction and ACID properties

A transaction is a logical processing unit consisting of a set of SQL statements with four properties, commonly referred to as the ACID property of the transaction.Copy the code

• Actomicity: A transaction is an atomic unit of operation in which all or none of the modifications to data are performed
• Consistent: Data must be Consistent at the beginning and completion of a transaction. This means that all relevant data rules must be applied to transaction modifications to maintain integrity; At the end of the transaction, all internal data structures (such as b-tree indexes or bidirectional linked lists) must also be correct
• Isolation: The database system provides some Isolation mechanism to ensure that transactions are executed in a “separate” environment that is not affected by external concurrent operations. This means that intermediate states during transaction processing are not visible to the outside world and vice versa.
• Durable: Changes to data after transactions are finished are permanent and can be maintained even in the case of system failures.

REPEATABLE READ by default

3. Provide four isolation levels. You can specify transaction isolation levels for all connections using — transaction-Isolation

   transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
Copy the code

4. Theoretical knowledge atlas

4. Isolation level description

• Uncommitted reads: Dirty reads are allowed, that is, data that may be read from uncommitted transaction changes in other sessions
• Committed read: Only committed data can be read
• Repeatable read: Reading data repeatedly within the same transaction can ensure data consistency, but there is still a phantom read
• Serializable: Each read requires a table-level shared lock, and reads and writes block each other

5. Dirty reading, unrepeatable reading, magic reading introduction

• Dirty read: When a transaction is accessing data and making changes to the data that have not been committed to the database, another transaction also accesses the data and then consumes the data. Only Read uncommitted reads can be dirty

session1: MariaDB [blog]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) MariaDB (blog) > start transaction; 1 row in set (0.00 SEC) insert into articles(title, content) values("a", "ZZZ "); 1 row in the set (0.00 SEC) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- session2: MariaDB [blog]> set session transaction isolation level read uncommitted; MariaDB [blog]> select * from articles; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 1 | a | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) MariaDB (blog) > set session transaction isolation level REPEATABLE READ; Query OK, 0 rows affected (0.00 SEC) MariaDB [blog]> select * from articles; The Empty set (0.00 SEC)Copy the code

• Non-repeatable read: a device that reads the same piece of data multiple times from one object, during which the data is modified by another object, resulting in inconsistent data read by the former

session1: MariaDB [blog]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) ----------------------------------------------------------------------------- session2: MariaDB [blog]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) MariaDB (blog) > start transaction; Query OK, 0 rows affected (0.00 SEC) MariaDB [blog]> select * from articles where id = 2; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 2 | z | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) ----------------------------------------------------------------------------- session1: MariaDB [blog]> update articles set title = "b" where id = 2; Query OK, 1 row affected (0.01sec) Rows matched: 1 Changed: 1 Warnings: 0 ----------------------------------------------------------------------------- session2: MariaDB [blog]> select * from articles where id = 2; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 2 | b | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) ----------------------------------------------------------------------------- session1: MariaDB [blog]> start transaction; Query OK, 0 rows affected (0.00 SEC) MariaDB [blog]> select * from articles where id = 2; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 2 | b | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) ----------------------------------------------------------------------------- session2: MariaDB [blog]> update articles set title = 'a' where id = 2; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0 ----------------------------------------------------------------------------- session1: MariaDB [blog]> select * from articles where id = 2; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 2 | b | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

• Phantom read: one item modifies all the data in the table, and the query results in unmodified data due to new data added by other items

session1: MariaDB [blog]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) MariaDB (blog) > start transaction; Query OK, 0 rows affected (0.00 SEC) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- session2: MariaDB [blog]> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 SEC) MariaDB [blog]> start transaction; Query OK, 0 rows affected (0.00 SEC) MariaDB [blog]> Update articles set title = 'ZZZ '; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [blog]> select * from articles; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 2 | zzz | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) ----------------------------------------------------------------------------- session1: MariaDB [blog]> insert into articles(title) values("as"); Query OK, 1 row affected, 2 warnings (0.01 SEC) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- session2: MariaDB [blog]> select * from articles; +----+-------+---------+---------------------+---------------------+ | id | title | content | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 2 | zzz | zzz | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 9 | as | NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | + - + - - - - - - - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code