MYSQL demonstrates the isolation level of a relational database-MySQL

MYSQL demonstrates the isolation level of the relational database. the concurrent access to the relational database has the following problems:

Dirty read: For two things T1 and T2, T1 reads fields that have been updated by T2 .. If T2 is rolled back, the content read by T1 is temporary and invalid.

Non-repeated read: T1 and T2 read a field, and T2. Then T1 reads the same field again, and the value is different.

Phantom read: For two things T1 and T2, T1 reads a field from a table, and T2 inserts some new rows in the table. Then, if T1 reads the same table again, there will be several more rows.

Database transaction isolation:

The database system must be able to isolate and run transactions concurrently so that they do not affect each other and avoid various concurrency problems. The degree to which a transaction is isolated from other transactions is calledIsolation level. The database specifies multiple transaction isolation levels. different isolation levels correspond to different levels of interference. the higher the isolation level, the better the data consistency, but the weaker the concurrency.

Database isolation level:

The MYSQL demo is as follows:

View the isolation level of the current mysql database:

Disable automatic submission of mysql databases (1 by default ):

The following operations are performed in the isolation table of the test database:

1. at the read uncommitted isolation level:

1. set the database isolation level to read uncommitted:

2. start two transactions T1 and T2:

T1:

T2:

3. modify the name = guanyunchang of id = 00002 in T2 and do not submit the statement for query in T1:

T2:

T1:

Dirty reading occurs.

4. T2 rollback:

T2:

T1:

2. read committed isolation:

1. set the database isolation level to read committed:

2. start two transactions T1 and T2:

T1:

T2:

3. modify the name = guanyunchang of id = 00002 in T2 and do not submit the statement for query in T1:

T2:

T1:

The dirty read problem does not exist.

4. T2 submission:

T2:

T1:

The problem of non-repeated reading occurs.

III. at the repeatable read isolation level:

1. set the database isolation level to repeatable read:

2. start two transactions T1 and T2:

T1:

T2:

3. in T2, modify the name = guanyunchang of id = 00002.

If T2 is not submitted, query in T1:

The dirty read problem does not exist.

T2 submit, query in T1:

The non-repeatability issue does not exist.

4. Insert a data id = 00004, name = zhugeliang in T2 and submit

T2:

T1:

Phantom read problems.

4. at the SERIALIZABLE isolation level:

1. set the database isolation level to serializable:

2. start two transactions T1 and T2:

T1:

T2:

3. in T2, modify the name = zhangyide of id = 00003:

During the duration of transaction T1, T2. insertion, modification, and deletion operations are not allowed.