Shulou(Shulou.com)05/31 Report--

This article shows you what the transaction of mysql database refers to, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. what is a transaction?

Database transaction (Transaction) is a sequence of database operations that access and possibly manipulate various data items, which are either performed or not performed, and are an inseparable unit of work. A transaction consists of all database operations performed between the beginning and the end of the transaction.

For example, transfer money from account A to account B, corresponding to the following two sql statements: update from account set money=money+100 where name='b';update from account set money=money-100 where name='a'

These two update statements should be run as a whole to ensure that they succeed or fail at the same time; there should be no one success or one failure.

Database open transaction command

We can use the following statement to start or end a transaction.

Start transaction starts a transaction

Rollback rollback transaction

Commit commit transaction

For example, we can execute the update statement of the bank transfer above in a transaction.

Start transaction;update from account set money=money+100 where name='b';update from account set money=money-100 where name='a';commit

Third, the characteristics of the transaction: ACID

Atomicity (Atomicity): atomicity means that a transaction is an indivisible unit of work, and either all or none of the operations in the transaction occur.

Consistency: transactions must transform the database from one consistency state to another.

Isolation: the isolation of transactions is that when multiple users visit the database concurrently, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions, and multiple concurrent transactions should be isolated from each other.

Durability: persistence means that once a transaction is committed, its changes to the data in the database are permanent, and then even if the database fails, it should not have any impact on it.

IV. Isolation level of the transaction

When multiple threads open the data in their respective transaction operation database, the database system is responsible for isolating the operation to ensure the accuracy of each thread in obtaining the data.

If isolation is not considered, the following problems may arise:

Dirty read: when one transaction reads data that is not committed by another transaction.

Non-repeatable read: read a row of data in a table within a transaction, and the results are different multiple times.

Virtual reading (phantom reading): refers to reading data inserted by another transaction within one transaction, resulting in inconsistent readings.

The database defines four isolation levels:

Serializable: can avoid dirty reading, non-repeatable reading and virtual reading. (serialization) Repeatable read (the default value of MySQL): can avoid dirty reading and unrepeatable reading. (repeatable) Read committed: can avoid dirty reading (read submitted). Read uncommitted: the lowest level, none of the above can be guaranteed. (read not submitted)

We can view the transaction isolation level and set the isolation level for MySQL through the following statement:

Set (GLOBAL | SESSION) transaction isolation level sets the transaction isolation level select @ @ tx_isolation queries the current transaction isolation level

The following statement looks at the current transaction isolation level of the MySQL database.

The following statement sets the transaction isolation level of MySQL to read committed, under which dirty reads will occur.

The above is what the transaction of mysql database refers to. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.