Summary of some issues about database transaction processing

Some summaries about mysql transaction processing:

1. What is a transaction?

MySQL transactions are mainly used to process data with large operations and high complexity.

For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to the person,

such as mailbox, articles, etc. Wait, in this way, these database operation statements constitute a transaction!

2. Conditions for using transactions

To use transactions in MySQL, it needs the support of the storage engine in MySQL. At present, the built-in storage engines in MySQL that support transactions areInnoDB, NDB cluster,

The third-party storage engines include PBXT and XtrDB.

3. What are the characteristics of transactions?

3-1. Atomicity

A transaction must be treated as an indivisible The smallest unit of work, all operations in each transaction must either succeed or fail.

It is never possible for some operations to fail and some operations to succeed. This is the so-called concept of atomicity.

3-2. Consistency

Consistency is just like the example given above. When an exception occurs, the data is still correct. That is to say, when a transaction fails to execute ,

The data will not be affected by abnormal situations and will always maintain its correctness.

3-3. Isolation

When a transaction has not yet been submitted, each transaction is isolated from each other and is not affected by each other.

3-4. Durability

When a After the transaction is submitted, the changes will be stored in the database forever.

4. Transaction isolation level

4-1. Uncommitted read

Even if the modifications made to the data in one transaction are not committed, the modifications are still visible to other transactions. In this case, dirty reads are prone to occur, affecting the integrity of the data. .

4-2. Read commit

When a transaction starts, only other transactions that have been submitted can be seen. In this case, non-repeatable reads are prone to occur ( The results of two readings are different).

4-3. Repeatable reading

The results of reading records multiple times are consistent. Repeatable reading can Solve the above non-repeatable read situation. But there is a situation where

When a transaction is reading records in a certain range, another transaction inserts a new piece of data in this range. When When the transaction reads data again,

it is found that there is one more record than the first read. This is the so-called phantom read. The results of the two reads are inconsistent.

4-4. Serializable

Serialization is like a queue. Each transaction is queued and waiting for execution. Only after the previous transaction is submitted, the next transaction can be operated.

Although this situation can solve the above phantom reading, it will add a lock to each piece of data, which can easily lead to a large number of lock timeouts and lock competition.

It is especially not suitable for some high-end applications. In concurrent business scenarios.

4-5. Isolation summary

Through the above example, we can easily find that dirty reads and non-repeatable reads focus on updating. data, and then phantom reading focuses on inserting data.

5. Transaction processing methods when using multiple storage engines

According to the conditions used in the above transactions, we can know Some storage engines do not support transactions. For example, MyISAMstorage engines do not support transactions.

If a transactional storage engine and non-transactional storage are used in a transaction, Submission can proceed normally, but rolling back a non-transactional storage engine will display a response error message. The specific information is related to the storage engine.

6. How to use transactions

Transactions in MySQL are enabled implicitly, that is to say, a SQL statement is a transaction. When the SQL statement is executed, the transaction is submitted. During the demonstration, we explicitly enabled it.

7. Automatic submission of mysql

As mentioned above, transactions in MySQL are implicitly enabled, which means that each of our sql is automatically submitted and needs to be closed. You need to set the

autocommit

option.

8. Set the isolation level of the transaction

set session transaction isolation level 隔离级别;

The above are some related information about database transaction processing prepared for you. Question, for more related questions, please visit the relevant articles on the PHP Chinese website. Thanks!

Recommended database related videos:

https://www.php.cn/course/list/51/type/2.html

The above is the detailed content of Summary of some issues about database transaction processing. For more information, please follow other related articles on the PHP Chinese website!