MySQL detailed explanation of isolation level operation process (cmd)

Read uncommitted example operation process - Read uncommitted

1. Open two MySql command prompt lines, enter the same database, and check that the current table contents are the same data as follows:

2. Execute select @@tx_isolation on both ends A and B; check the current default isolation level and you can find that both are

Repeatable Read – Repeatable Read - (Repeatedly reading the data that was read for the first time within the current transaction is called repeatable read.)

3. Change the isolation level of end A to readuncommitted – read uncommitted. This means that you can read data that others have not submitted.

set transactionisolation level read uncommitted;

On the green MySql5.5, please execute:

Set sessiontransaction isolation level read uncommitted;

Then check to see if the changes have occurred:

4. Start transactions on both ends A and B

starttransaction;

5. Modify a row of data on the B side such as:

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

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

6. At this time, the B side performs the rollback operation again

Rollback;

Then query on end A and find that the data has returned to the previous data. This is a dirty read :

7. For new data written by the B side, if it is not submitted, the A side can also query it. This is called phantom read .

Read committed operation process: -read COMMITTED

1. Check whether A and B are consistent:

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

set transactionisolation level read committed;

Start a transaction on side A:

starttransaction;

Start a transaction on side B

3. Query on end A:

Select * fromstud;

Modify a row of records on the B side and submit

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

Go back to end A to query and find that the results of the two queries in the same transaction are different:

Repeatable Read Example

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

Select@@tx_isolation;

2. First query on the A side in the open transaction.

Then modify the contents of the database on the B side.

Finally, query again in the same transaction on end A and find that the results are consistent.

Serializable is the highest level of isolation

1. Set the isolation level to Serializable on end A

set transactionisolation level serializable;

Open a transaction on the A side and query the stud table.

Open a transaction on the B side and write a row of records. At this time, it is found that B's code has not been executed because it is waiting for A to submit before it is executed.

Similar to the concept of thread synchronization

These four isolation levels are implemented using different lock types, and problems are likely to occur if the same data is read. For example:

Dirty Read: A transaction has updated a piece of data, and another transaction reads the same data at this time. For some reason, the previous transaction RollBacks the operation, and the data read by the latter transaction will be incorrect.

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

Phantom Read: The number of data entries in two queries of a transaction is inconsistent. For example, one transaction queries several rows of data, while another transaction inserts several new rows of data at this time. In the next query, the previous transaction will find several rows of data that it did not have before.

Thank you for reading, I hope it can help you, thank you for your support of this site!