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

This article mainly explains "what is the implementation principle of database transaction". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the principle of the implementation of database transactions?"

Database transactions have four characteristics: atomicity, isolation, persistence, and consistency. The definition of these four characteristics and how to implement them in the InnoDB engine are described below.

Atomicity

Define

One operation is inseparable, either all succeed or all fail. For example, in our transfer operation, the issuer is not allowed to succeed and the cashier fails, either successfully or frequently, and it is impossible to have an intermediate state.

Realize

The InnoDB engine uses undo log (rollback log) to ensure atomic operations, and every data change you make to the database (INSERT, DELETE, UPDATE) is recorded in undo log, such as the following operations:

When you insert a record, at least write down the primary key value of the record, and then just delete the record corresponding to the primary key value when you roll back.

If you delete a record, at least write down all the contents of the record, so that when you roll back, you can insert the record made up of these contents into the table.

If you modify a record, at least write down the old value before changing the record, so that you can update the record to the old value when you roll back later.

When the transaction fails or the rollback method is called, the rollback event is triggered, using the record in undo log to roll back the data to what it looked like before the modification.

For more information about undo log, sign in with a separate article later.

Isolation

Define

When multiple transactions are executed concurrently, the operations within the transaction are isolated from other transactions, and the transactions executed concurrently cannot interfere with each other.

Realize

Isolation may introduce problems such as dirty reading (dirty read), unrepeatable reading (non-repeatable read), phantom read and so on. In order to solve these problems, the concept of "isolation level" is introduced.

SQL standard transaction isolation levels include read uncommitted (read uncommitted), read commit (read committed), repeatable read (repeatable read), and serialization (serializable):

Read uncommitted: when a transaction is not committed, its changes can be seen by other transactions.

Read commit: after a transaction commits, its changes will not be seen by other transactions.

Repeatable: the data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, under the repeatable readable isolation level, uncommitted changes are not visible to other transactions.

Serialization: as the name implies, for the same row of records, "write" adds "write lock" and "read" adds "read lock". When there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

According to the SQL standard, concurrent transactions can have different severity problems according to different isolation levels, as follows:

These are the concurrency problems that can occur at several isolation levels, but it's important to say that the more tightly isolated you are, the less efficient you will be.

How does the InnoDB engine ensure isolation? Using lock and MVCC mechanism. Here is a brief introduction to the MVCC mechanism, also known as multi-version concurrency control. Under transactions with two isolation levels, READ COMMITTD and REPEATABLE READ, each record records a rollback operation when it is updated, which forms a version chain and accesses the record version chain when performing ordinary SELECT operations. In this way, read-write and write-read operations of different transactions can be executed concurrently, thus improving system performance.

Persistence

Define

Once a transaction is committed, its changes to the database should be permanent. Other operations or failures that follow should not affect it in any way.

Realize

To ensure persistence is very simple, that is, every time a transaction commits, the data is brushed to disk, which must ensure security, but you should know that if you write data to disk every transaction commit, frequent IO operations will cost too much and the performance of the database is extremely low, so this approach is not advisable.

How does the InnoDB engine solve it? the InnoDB engine introduces a middle tier to solve this persistence problem, which we call redo log.

Why the introduction of redo log?redo log can ensure persistence and database performance. Compared with flushing disk directly, redo log has the following two advantages:

Redo log is small in size, after all, it only records which page has been modified and what has been modified, so it is small in size and quick to swipe.

Redo log is appended all the way to the end and belongs to sequential IO. Efficiency is obviously faster than random IO.

How does the InnoDB engine do it? When there is a record that needs to be updated, the InnoDB engine will first write the record to redo log and update the memory, and the update is complete. When the database is down and restarted, the contents of redo log will be restored to the database, and then the data will be rolled back or submitted based on the contents of undo log and binlog.

Consistency

Define

To put it simply, consistency means that the data must be in a legal state before and after the execution, for example, the ID card number cannot be repeated, the sex can only be male or female, the score of the college entrance examination can only be between 0,750, and the traffic light has only three colors. The house price cannot be negative, and so on. Only the data that meets these restrictions are valid. For example, when a child tells you that he scored 1000 in the college entrance examination, you can tell his nonsense as soon as you hear it. The database world is only a mapping of the real world, and the constraints that exist in the real world should also be reflected in the database world. If all the data in the database conforms to real-world constraints (all defined rules), we say that the data is consistent, or consistent.

Realize

To ensure the data consistency of the database, efforts should be made in the following two aspects:

Take advantage of some features of the database to ensure some consistency requirements, such as declaring a column NOT NULL to deny that NULL is worth inserting, etc.

Most of it still needs to be guaranteed by us programmers when writing business code.

At this point, I believe you have a deeper understanding of "what is the principle of the implementation of database transactions?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.