MySQL transaction learning -- & gt; isolation level _ MySQL

MySQL transaction learning -- amp; gt; isolation level bitsCN.com

MySQL transaction learning --> isolation level

6. transaction isolation level

Purpose

In database operations, the transaction isolation level is proposed to effectively ensure the correctness of concurrent data reading.

Databases are shared by customers. the following uncertainties may occur during database operations.

Update loss

Both transactions update a row of data at the same time, but the second transaction fails to exit, resulting in invalid modification to both data. This is because the system does not execute any lock operations, so concurrent transactions are not isolated.

Dirty read

A transaction starts to read a row of data, but another transaction has updated the data but cannot be committed in time. This is quite dangerous, because it is very likely that all operations are rolled back.

Non-repeatable Reads)

A transaction reads data from the same row twice but returns different results. It includes the following situations:

(1) after transaction T1 reads a data, transaction T2 modifies it. when transaction T1 reads the data again, it gets a different value from the previous one.

(2) Phantom Reads: The transaction performs two queries during the operation, the results of the second query contain data not found in the first query or missing data in the first query (the SQL statements for the two queries are not required to be the same here ). This is because another transaction inserts data during the two queries.

Solution

To avoid the preceding situations, four transaction isolation levels are defined in the standard SQL specification. different isolation levels process different transactions.

Unauthorized read

Read Uncommitted: allows dirty reading, but does not allow loss of updates. If a transaction has started to write data, the write operation of another transaction is not allowed at the same time, but other transactions are allowed to read this row of data. This isolation level can be achieved through the exclusive write lock.

Authorized read

Read Committed: Read requests cannot be Read repeatedly, but dirty reads are not allowed. This can be achieved through "instant shared read lock" and "exclusive write lock. Transactions that read data allow other transactions to continue to access this row of data, but uncommitted write transactions will prohibit other transactions from accessing this row.

Repeatable reading

Repeatable Read: prohibit repeated reading and dirty reading, but sometimes phantom data may appear. This can be achieved through the "shared read lock" and "exclusive write lock. The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions.

Serialization

Serializable: provides strict transaction isolation. It requires the transaction to be serialized. The transaction can only be executed one by one, but cannot be executed concurrently. If Transaction Serialization is not possible only through the "row-level lock", other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just performs the query operation.

The higher the isolation level, the more data integrity and consistency can be guaranteed, but the greater the impact on concurrency performance. For most applications, the isolation level of the database system can be set to Read Committed. It can avoid dirty reading and has good concurrency performance. Although it may cause non-repeated read, virtual read, and second-type update loss concurrency problems, in some scenarios where such problems may occur, it can be controlled by applications using pessimistic or optimistic locks.

There are two ways to set the transaction isolation level in MySQL:

1 in my. cnf settings, in mysqld options set [mysqld] transaction-isolation = READ-COMMITTED2 in mysql window reset [SQL] mysql> set global tx_isolation = 'repeatable-read' with the set Command '; query OK, 0 rows affected (0.01 sec) mysql> to Query the current session transaction level, you can use: [SQL] mysql> select @ tx_isolation; + ---------------- + | @ tx_isolation | + ------------------ + | READ-COMMITTED | + ---------------- + 1 row in set (0.00 sec) mysql> query the global transaction isolation level, you can use [SQL] mysql> s Elect @ global. tx_isolation; + --------------------- + | @ global. tx_isolation | + --------------------- + | READ-COMMITTED | + ----------------------- + 1 row in set (0.00 sec) mysql> in Serializable mode. [SQL] mysql> system cat/usr/local/mysql 56m2/my. cnf | grep transaction-isolation = READ-COMMITTED mysql> in SERIALIZABLE mode, the Innodb storage engine automatically adds Lock in sharedmode to each select statement and adds a shared Lock to each read operation. Therefore, at this isolation level, the read lock is occupied, and consistent non-locked reads are no longer supported. Because the Innodb storage engine has reached the 3-degree isolation in the repeatable read mode, the serializable isolation level is generally not used in local transactions, the transaction isolation level of serializable is mainly used for Distributed Transactions of the innodb storage engine. In Read committed isolation mode, apart from the uniqueness constraint check and foreign key constraint check, the innodb storage engine does not use the Gap lock algorithm. However, you need to pay attention to some issues when using the read committed isolation level. in MySQL, the transaction isolation level of Read committed can only be in the row format of replication binary by default. if the binary is in statement mode, the following error is reported: [SQL] mysql> select @ version; + ------------- + | @ version | + ------------- + | 5.5.25a-log | + ------------- + 1 row in set (0.00 sec) mysql> select @ binlog_format; + ----------------- + | @ binlog_format | + ----------------- + | STATEMENT | + ----------------- + 1 row in set (0.0 0 sec) mysql> select @ tx_isolation; + ----------------- + | @ tx_isolation | + ------------------- + | REPEATABLE-READ | + --------------- + 1 row in set (0.00 sec) mysql> set tx_isolation = 'read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> use test; Database changed mysql> create table a (B int, primary key (B) engine = innodb; ERROR 1050 (42S01): Table 'A' already exists mysql> select @ tx_ I Solation; + ---------------- + | @ tx_isolation | + ------------------ + | READ-COMMITTED | + ---------------- + 1 row in set (0.00 sec) mysql> begin->; Query OK, 0 rows affected (0.00 sec) mysql> insert into a select 100000; ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-basedloggi Ng. innoDB is limited to row-logging when transaction isolation level isREAD COMMITTED or read uncommitted. ERROR 1665 (HY000): Cannotexecute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-basedlogging. innoDB is limited to row-logging when transaction isolation level isREAD COMMITTED or read uncommitted. [Not E]: this is the case in mysql5.1 and mysql5.6 modes. It may be known that by setting innodb_locks_unsafe_for_binlog to 1, binlog can use readcommitted transaction isolation level in statement: [SQL] mysql> select @ innodb_locks_unsafe_for_binlog; + response + | @ innodb_locks_unsafe_for_binlog | + response + | 0 | + response + 1 row in set (0.00 sec) mysql> set global innodb_locks_unsafe_for_binlog = 1; ERROR 1238 (HY000): Variabl E 'innodb _ locks_unsafe_for_binlog 'is a readonly variable mysql> this parameter is in read-only mode and needs to be modified to restart my. cnf. In my. in cnf, add [mysqld] to [mysqld] innodb_locks_unsafe_for_binlog = 1, restart it, and then check to simulate a transaction operation, as shown below: [SQL] mysql> select @ innodb_locks_unsafe_for_binlog; + rows + | @ innodb_locks_unsafe_for_binlog | + rows + | 1 | + ------------------------------------ + 1 row in set (0.00 sec) mysql> use test; reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A Database changed mysql> select @ tx_isolation; + ---------------- + | @ tx_isolation | + ------------------ + | READ-COMMITTED | + ---------------- + 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 15; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> commit; query OK, 0 rows affected (0.00 sec) mysql> select * from t; + -------- + | id | + -------- + | 1 | 12 | 15 | 11111 | 111110 | + -------- + 5 rows in set (0.00 sec)

BitsCN.com