Database isolation level and lock mechanism (including MySQL testing)

The ANSIISOSQL92 standard defines the isolation level for some database operations: When transaction A of A updating database does not commit, another transaction B is reading the updated

ANSI/ISO SQL92 standard defines the isolation level for some database operations: When transaction A of A updating database does not commit, another transaction B is reading the updated

The ANSI/ISO SQL92 standard defines the isolation levels for some database operations:

1. uncommitted read (read uncommitted)

2. read committed)

3. repeatable read)

4. serializable)

Lock Mechanism:

Shared lock: other transactions can be read but cannot be modified.

Exclusive lock: other transactions cannot be read.

Lock granularity: generally divided into: Row lock, table lock, database lock

Explanation:

1. uncommitted read (read uncommitted)

When Transaction A of A updating database does not commit, another Transaction B is reading the update record of Transaction A, which may cause dirty reading. This is because Transaction A starts DB Transaction, when some DML operations are performed, the records are stored in the memory. At this time, transaction B reads the data committed by transaction A in the memory, resulting in dirty reads.

2. read committed)

Data modifications are read back only after commit. Opposite to 1.

3. repeatable read)

When the database isolation level is set to repeatable read, transaction B can modify the data read by A in the select statement of transaction A. When A executes the same SQL statement 2nd times, returns the same data as the previous one to eliminate repeated reads.

Note: I personally think that only after the isolation level is adopted for transaction A, the image of the database at the start time of the transaction is read, all operations after this time point will not affect the query in transaction A. It is based on the subsequent experiments in this article. If you have any questions, please note.

4. serializable)

When the database isolation level is set to Serializeable, the select statement in transaction A locks the data (in the data result set returned by the select statement ), the data cannot be modified (can be read). If transaction B performs an UPDATE operation on the data, it will be in the waiting state to eliminate Phantom reads.

Note: Transaction B can UPDATE the locked data in transaction A, which can be proved by subsequent experiments.

Experiment: (remember to use set autocommit = off before Mysql command line client test; submit and close automatically)

View the default database isolation level mysql> SELECT @ global. tx_isolation;

View the current session isolation level mysql> SELECT @ tx_isolation;

Modify the default database isolation level mysql> set global transaction isolation level read committed;

Modify the current session isolation level mysql> set session transaction isolation level read committed;

1 read uncommitted Test

Enable two MySql Command Line Client a B, set A to read uncommitted, and B to the default repeatable read;

Set session transaction isolation level read uncommitted;

Insert a record to the database table through B, but do not commit the transaction

Insert into test. user (user_id, name, age) values (4, 'hangpin6', 25 );

Execute select * from test. user in A. The newly inserted record is displayed, which indicates that the isolation level of read uncommitted creates A dirty read problem.

2 read committed Test

The scenario is the same as test 1. set the isolation level of A To read committed (mysql> set session transaction isolation level read committed), and select * from test. user; no records inserted by B are displayed. After submitting data (mysql> commit;) in B, A displays the data inserted by B. This shows that A uses read committed to avoid dirty reading.

3 repeatable read Test

This test uses a java client to connect to MySql. The specific code is as follows:

Public static void getResult () throws Exception {
Thread t = new Thread (new MySqlTest (). new ThreadTest ());
T. start ();
Connection mySqlCon = getConn (); // obtain the database Connection
MySqlCon. setTransactionIsolation (Connection. TRANSACTION_REPEATABLE_READ); // you can specify the isolation level.
MySqlCon. setAutoCommit (false );
String SQL = "select * from test. user where user_id in (1, 3, 2, 8 )";
PrintResult (mySqlCon, SQL); // print the output result
T. sleep (20000); // sleep for 20 seconds (during this process, update the data update test. user set where user_id = 1) (1)
System. out. println ("thread sleep finashed ");
String sql2 = "select * from test. user where user_id in (1, 3, 2, 8 )";
PrintResult (mySqlCon, sql2 );
String sql3 = "select * from test. job ";
PrintResult (mySqlCon, sql3 );
MySqlCon. commit ();
}

First, we set the transaction isolation level to TRANSACTION_REPEATABLE_READ, which is the repeatable read in the corresponding database. Then we start to query the USER whose USER_ID is ,.

When the thread is suspended (1), data in the USER_ID table of the USER table is updated through the MySql client (which can be considered as transaction B, update the table JOB data at the same time. After the thread continues execution, the USER table is printed as the data before the update, the JOB table is the data before the update, and the operation of transaction B has no effect on transaction.

It is inferred from the above conclusion that the repeatable read isolation level reads the database image at the time point starting with transaction.

4 serializable Test

Use the same test code as 3 to change the isolation level to mySqlCon. setTransactionIsolation (Connection. TRANSACTION_SERIALIZABLE); // set the isolation level

TRANSACTION_SERIALIZABLE: serializable of the database.

Update table JOB data at (1), update table USER where USER_ID In (), update table USER where USER_ID In ), during the execution of transaction B, the first two SQL statements run normally, and the operations for updating USER_ID in () are in the waiting state. After transaction A ends, transaction B can also end normally. At the same time, the output result of transaction A contains the Modification result of transaction B.

From the above experiment, we can infer that the serializable isolation level is to share the lock on the data scanned in transaction A after transaction A. If transaction B wants to modify the locked data, wait for end of. If transaction B has modified some data before A scans (which will be scanned later, then A will scan the latest data (B's modified data)