MySQL database Transaction Isolation Level (Transaction Isolation Level)

Today, when I was studying JDBC, I saw a problem about the isolation level of MySql transactions. I felt that the content was quite advanced. So I recorded an article for later use.

There are four database isolation levels, as described in the high-performance mysql book:

 

Then let's talk about how to modify the transaction isolation level:

1. Modify the global settings, modify the mysql. ini configuration file, and add

1 # optional parameters include: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.2 [mysqld] 3 transaction-isolation = REPEATABLE-READ

Here the global default is REPEATABLE-READ, in fact, MySQL is also the default level

2. modify the current session. After logging on to the mysql client, run the following command:

Set session transaction isolation level read uncommitted;

 

Remember that mysql has an autocommit parameter. The default value is on. Its function is that each individual query is a transaction, which starts automatically and is automatically submitted. (It ends automatically after execution, if you want to apply select for update without manually calling start transaction, the row lock mechanism of this for update is useless because the row lock is released after automatic submission ), therefore, even if you do not explicitly call start transaction, the transaction isolation level and lock mechanism are also applicable to a separate query statement. Pay attention to this when analyzing the operation of the lock.

 

Let's talk about the lock mechanism:
Shared lock: The lock added by the read Table operation. After the lock is applied, other users can only obtain the shared lock of the table or row, but cannot obtain the exclusive lock. That is to say, they can only read and write data.

Exclusive lock: The lock added by the write table operation. After the lock is applied, other users cannot obtain any lock for the table or row. It is typically used in mysql transactions.

Start transaction;

Select * from user where userId = 1 for update;

After this sentence is executed

1) when other transactions want to obtain a shared lock, such as a transaction with a transaction isolation level of SERIALIZABLE, execute

Select * from user;

Will be suspended because the select statement of SERIALIZABLE needs to obtain the share lock.

2) When other transactions are executed

Select * from user where userId = 1 for update;

Update user set userAge = 100 where userId = 1;

Will also be suspended,Because for update will obtain the exclusive lock for this row of data, you need to wait until the previous transaction releases the exclusive lock to continue.

 

Lock range:

Row lock:Lock a row of records

Table lock:Apply a lock to the entire table

In this way, row-level shared locks, table-level shared locks, row-level exclusive locks, and table-level exclusive locks are combined.

The following describes the effects of instances with different transaction isolation levels. In this example, InnoDB is used to enable two clients A and B, and the transaction isolation level is modified in A. Enable transactions and modify data in B, then, check the transaction modification effect of transaction B in transaction A (the two clients are equivalent to two connections, and modifying the parameter variable value in one client does not affect the value of another client ):

 

1. READ-UNCOMMITTED (read uncommitted content) level

1) A modifies the transaction level and starts the transaction, and queries the user table once.

　　　

 

2) B. Update a record.

　　　

 

3) at this time, transaction B has not been committed. Transaction A performs A query in the transaction and finds that the query result has changed.

　　　

 

4) B. Perform transaction rollback

　　　

 

5) A performs another query, and the query result is changed back.

　　　

 

6) Table A modifies the user table data.

　　　

 

7) after the transaction starts again in Table B, the user table record is modified and the modification is suspended until the transaction times out. However, the modification to another data item is successful, note: The modification of A adds A row share lock to the data rows in the user table (because select can be used)

　　　

 

It can be seen that the READ-UNCOMMITTED isolation level, when two transactions are performed at the same time, even if the transaction is not committed, the modifications made will affect the query in the transaction, this level is obviously not safe. However, when the table modifies a row, it will add a row share lock to the row.

 

2. READ-COMMITTED (READ submitted content)

1) set the transaction isolation level of A and enter the transaction for A query

　　　

 

2) B. Start the transaction and modify the record.

　　　

 

3) A then queries the user table and finds that the record is not affected.

　　　

 

4) B. Submit the transaction

　　　

 

5) A then queries the user table and finds that the record is modified.

　　　

 

6) A. Modify the user table

　　　

 

7) B. Start the transaction again and modify the same entry in the user table. The modification is suspended until the transaction times out, but the modification to another record is successful, the row share lock is applied to the user table in the case of A modification (because the user table can be selected)

　　　

　　　

 

READ-COMMITTED transaction isolation level. It affects another transaction only after the transaction is COMMITTED. When the table is modified, a row share lock will be added to the table data row.

 

3. REPEATABLE-READ (repeatable)

1) A sets the transaction isolation level and queries the transaction once.

　　　

 

2) B starts the transaction and modifies the user table

　　　

 

3) A. Check the data in the user table. The data has not changed.

　　　

 

4) B. Submit the transaction

　　　

 

5) when A queries again, the results remain unchanged.

　　　

 

6) After A commits the transaction, check the result and the result is updated.

　　　

 

7) A re-starts the transaction and modifies the user table

　　　

　　　

8) in table B, start the transaction again and modify the user table. The modification is suspended until the transaction times out, but the modification to another record is successful, this indicates that A adds the row share lock (select) to table modification)

　　　

　　　

 

At the REPEATABLE-READ transaction isolation level, when two transactions are performed at the same time, one of the transactions modifying data does not affect the other transaction, even if the modified transaction has been committed, it does not affect the other transaction.

Modifying a record in a transaction adds a row share lock to the record until the transaction ends.

 

4. SERIERLIZED (serializable)

1) modify the transaction isolation level of A and make A query

　　　

 

2) B. query the table and obtain the result normally. We can see that the user table can be queried.

　　　

 

3) B starts the transaction and modifies the record. Because transaction A is not committed, the modification of transaction B is in the waiting state, waiting for transaction A to end and finally timeout, note that A adds A shared lock to the table after performing A query operation on the user table

　　　

 

The SERIALIZABLE transaction isolation level is the strictest. Shared locks are applied to tables or rows during query. Other transactions can only perform read operations on the table, but not write operations.