Shulou(Shulou.com)06/01 Report--

Definition of transaction

Transaction: the smallest unit of work of a database operation, which is a series of operations performed as a single logical unit of work; a transaction is a set of operations (working logical units) that can no longer be divided.

Typical transaction usage scenario: transfer

MySQL starts a transaction:

MySQL enables transactions by default, and you can check the transaction opening status of MySQL through SHOW VARIABLES like 'autocommit';.

In the case of autocommit = ON (autocommit transaction), you can execute the BEGIN; or START TRANSACTION; command and commit the transaction manually instead. After executing the SQL statement, you need to commit the transaction through the COMMIT command or roll back the transaction through the ROLLBACK command.

In the case of autocommit = OFF (manually commit the transaction), after the SQL statement is executed, you need to commit the transaction through the COMMIT command, or roll back the transaction through the ROLLBACK command.

JDBC programming:

Test commands:

Transaction ACID feature

Atomicity (Atomicity): the smallest unit of work, the entire unit of work is either submitted successfully together, or all failed rollback.

Consistency: the data and state changes of operations in a transaction are consistent, that is, the results of writing data must be in full compliance with the preset rules, and will not cause state inconsistencies due to system accidents and other reasons.

Isolation: the visibility of a transaction's data to other transactions before it is committed (generally set to invisible).

Durability: changes made by the transaction are saved permanently without data loss due to system accidents.

What is the difference between atomicity and consistency? ) this blog post talks about the difference between atomicity and consistency.

What are the problems caused by transaction concurrency

Dirty reading

MySQL InnoDB transaction

For example, there is a piece of user data in the user table that performs the following operations:

Transaction B updates the data of id=1, age updates to 18, and the transaction is not committed

Transaction A queries the data of id=1

Transaction B rolls back the update operation just now

At this time, is the value of age 16 or 18 for the id=1 data queried in transaction A?

Non-repeatable

For example, there is a piece of user data in the user table that performs the following operations:

Transaction A queries the data of id=1

Transaction B updates id=1 's data, age updates to 18, and commits the transaction

Transaction A queries id=1 's data again.

At this time, transaction A queries the data of id=1 twice. Is the value of age 16 or 18?

Illusory reading

For example, the following actions are performed in the user table:

Transaction A queries data with age > 15

Transaction B adds a piece of data, age=22, and commits the transaction

Transaction An again queries the data of age > 15

At this time, is the number of data queried twice by transaction A 1 or 2?

Four isolation levels of transaction

SQL92 ANSI/ISO standard: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Read Uncommitted (uncommitted reads)-concurrency issues not resolved

Transaction uncommitted is also visible to other transactions, dirty read (dirty read)

Read Committed (submit read)-- solve the problem of dirty reading

After a transaction starts, you can only see the changes made by the transaction you submitted, and you can't read it again (non repeatable read).

Repeatable Read (repeatable)-solving the problem of non-repeatable readings

The result of reading the same data multiple times in the same transaction is the same, and this isolation level is not defined to solve the problem of phantom reading.

Serializable (serialization)-solve all problems

The highest isolation level, by forcing the serial execution of transactions

Degree of InnoDB engine support for isolation level

Concurrency at transaction isolation level: uncommitted read > committed read > repeatable read > serialization

The default transaction isolation level of the InnDB engine is Repeatable Read, where it also solves the problem of phantom reading. The transaction isolation level in InnDB is realized by lock and MVCC.

View the default transaction isolation level for InnoDB with the following statement:

MySQL InnoDB transaction

MySQL InnoDB transaction

Set the transaction isolation level for InnoDB with the following statement:

MySQL InnoDB transaction

Next, let's test whether the default transaction isolation level (Repeatable Read) of InnoDB solves the problems of dirty, unrepeatable, and phantom reading.

Data preparation:

MySQL InnoDB transaction

Does it solve the problem of dirty reading?

MySQL InnoDB transaction

Open two MySQL clients and simulate the operation of transaction An and transaction B. perform the following steps:

Transaction B starts manual transaction, updates data of id=1, updates age to 18, and does not commit transaction.

Transaction A queries the data of id=1

Transaction B rolls back the update operation just now

The numbers in the figure are the execution steps. From the following figure, we can see that the execution result of transaction An is that the default transaction isolation level of 16MagneInnDB perfectly solves the problem of dirty reading.

MySQL InnoDB transaction

Has the problem of unrepeatable reading been solved?

MySQL InnoDB transaction

Open two MySQL clients and simulate the operation of transaction An and transaction B. perform the following steps:

Transaction A starts manual transaction to query the data of id=1

Transaction B updates id=1 's data, age updates to 18, and commits the transaction

Transaction A queries id=1 's data again.

The number in the figure is the execution step. Through the following figure, we can see that the execution result of transaction An is 16, which is not affected by the update operation of transaction B. the default transaction isolation level of InnDB perfectly solves the problem of non-repeatable reading.

MySQL InnoDB transaction

Has the problem of phantom reading been solved?

MySQL InnoDB transaction

Open two MySQL clients and simulate the operation of transaction An and transaction B. perform the following steps:

Transaction A starts manual transaction and queries data with age > 15.

Transaction B adds a piece of data, age=22, and commits the transaction

Transaction An again queries the data of age > 15

The number in the figure is the execution step. Through the following figure, we can see that the result of the two execution of transaction An is a piece of data, which is not affected by the new operation of transaction B. the default transaction isolation level of InnDB perfectly solves the problem of phantom reading.

MySQL InnoDB transaction

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.