Mysql detailed isolation-level operation process (cmd), mysqlcmd

Mysql detailed isolation-level operation process (cmd), mysqlcmd

Read uncommitted example operation procedure-Read uncommitted

1. Open the command lines of the two MySql Databases and check that the current table contains the same data as follows:

2. select @ tx_isolation at both ends of A and B. Check the default isolation level.

Repeatable Read-(in the current transaction, repeated Read of the data that has been Read for the first time is called Repeatable Read .)

3. Modify the isolation level of end A to readuncommitted-read uncommitted. This means that you can read data not submitted by others.

set transactionisolation level read uncommitted;

In green MySql5.5, execute:

Set sessiontransaction isolation level read uncommitted;

Then check whether the change has occurred:

4. Start transactions at both ends of A and B.

starttransaction;

5. modify a row of data on side B, for example:

update stud setname='Jhon' where id=1;

Then execute the query on end A: select * from stud;

6. At this time, the B-end executes the rollback operation again.

Rollback;

Then perform A query on end A, and the result shows that the data is returned to the previous data. This isDirty read:

7. For the new data written by the B end, it can be queried even if the end is not submitted. This is calledPhantom read.

 Read COMMITTED operation process:-read COMMITTED

1. Check whether the two ends of A and B are consistent:

2. Modify the isolation level of end A (left) to readcommitted;

set transactionisolation level read committed;

Start the transaction on Client:

Starttransaction;

Start transaction on client B

3. query on Client:

Select * fromstud;

Modify and submit a row of records on client B

Update stud setname='itcast' where id=1;

Return to end A for query and find that the two queries have different results in the same transaction:

 Repeatable Read

1. Check whether the isolation level of end A is Repeatableread:

Select @ tx_isolation;

2. query in the transaction that is enabled on client A first.

Then, modify the database content on client B.

Finally, the query results are consistent in the same transaction of end.

 Serializable is the highest isolation level

1. Set the isolation level to Serializable on end.

set transactionisolation level serializable;

Start A transaction on end A and query the stud table.

Start a transaction on client B and write a row of records. At this time, it is found that B's code is not executed because it is executed only after A commits it.

Similar to thread synchronization

The four isolation levels adopt different lock types. If the same data is read, problems may occur. For example:

Dirty Read: a transaction has updated the data, and another transaction has Read the same data at this time. For some reason, the previous RollBack operation is performed, the data read by the other transaction is incorrect.

Non-repeatable read: the data in the two queries of a transaction is inconsistent. This may be because the original data updated by a transaction is inserted in the two queries.

Phantom Read: the number of data records in two queries of a transaction is inconsistent. For example, a transaction queries several rows of data, another transaction inserts several new columns of data at this time. In the subsequent query, the previous transaction will find that several columns of data are not before it.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!