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

What are the problems with database transaction processing? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Some summary of mysql transaction processing:

1. What is a transaction?

MySQL transactions are mainly used to deal with data with large amount of 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 that person.

Such as mailboxes, articles, etc., so that these database operation statements constitute a transaction!

2. Conditions for transaction use

To use transactions in MySQL, you need storage engine support in MySQL. At present, InnoDB and NDB cluster support transactions in MySQL's built-in storage engine.

Third-party storage engines include PBXT and XtrDB.

3. What are the characteristics of the transaction

3-1. Atomicity

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

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

3-2. Consistency

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

The data will not be affected by abnormal circumstances, and his correctness will always be maintained.

3-3. Isolation

When a transaction is not committed, each transaction is isolated and unaffected.

3-4. Persistence

When a transaction commits, the changes are saved forever in the database.

4. Isolation level of the transaction

4-1. Unsubmitted reading

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

4-2. Read submission

At the beginning of a transaction, only other transactions that have been committed can be seen. In this case, unrepeatable readings are easy to occur (the results of the two readings are different).

4-3. Repeatable

The results of multiple reading records are consistent, and repeatable reading can solve the above non-repeatable reading situation. But there is such a situation.

When one transaction reads a range of records, another transaction inserts a new piece of data in that range, and when the transaction reads the data again

It is found that there is one more record than the first read, which is called phantom reading, and the results of the two reads are inconsistent.

4-4, Serial

Serial is like a queue, each transaction is queued for execution, and only after the previous transaction is committed can the next transaction operate.

Although this situation can solve the illusion above, 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 not suitable for some high concurrency business scenarios.

4-5. Isolation summary

Through the above examples, it is not difficult for us to find. Dirty reading and unrepeatable reading focus on updating data, and then phantom reading focuses on inserting data.

5. how to handle transactions with multiple storage engines

According to the above conditions of transaction use, we can know that some storage engines do not support transactions, such as MyISAM storage engine.

If a transactional storage engine and non-transactional storage are used in a transaction, the commit can proceed normally.

However, rolling back the non-transactional storage engine will display the response error message, which is related to the storage engine.

6. How to use transactions

In MySQL, a transaction is implicitly opened, that is, a sql statement is a transaction, and when the sql statement is executed, the transaction commits. During the demonstration, we explicitly open it.

7. Automatic submission of mysql

As mentioned above, if the transaction in MySQL is implicitly opened, it means that every sql commits automatically. If you need to close it, you need to set the autocommit option.

8. Set the isolation level of the transaction

Set session transaction isolation level isolation level; is it helpful for you to read the above? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.