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

What is the mechanism of MySQL database transactions? In recent days, many interviewers have been asked about the database transaction mechanism, isolation level, optimistic and pessimistic locks. Before, we can only say that we have some understanding of these, but they are not particularly profound. Today, the editor summarizes the relevant concepts for you.

I. what is a transaction?

Transaction I understand is a complete business behavior, a business activity may contain multiple actions, this complete action constitutes a transaction. A more classic example is a bank transfer, An account to B account, requires two actions: an account minus, B account plus, must ensure that either do both, or do not do.

Transactions have ACID characteristics, including:

● atomicity (atomicity): atomicity means that transactions are inseparable, either full success or total failure, partial success and partial failure. If you fail halfway, you need to clean the battlefield, that is, data rollback.

● consistency (consistency): consistency is the final result of a transaction to ensure that there are no exceptions on the data. Consistency emphasizes the results and is based on atomicity, that is to say, if atomicity can be guaranteed, there will be consistent results.

● isolation (isolation): isolation means that the transaction is not visible to other transactions before it is committed, and the data between transactions is isolated (of course, different levels of isolation vary).

● persistence (durability): transactions are persisted after they are committed and can be saved for a long time.

II. Transaction isolation level

Before you understand the isolation level of a transaction, you need to understand several concepts of data reading:

● dirty reading: reading data that has not been submitted by others.

● can be read repeatedly: it is two queries within the same thing. If someone modifies the record in the query and submits it, it will not be visible to the second query, and there will be no inconsistency between the two queries of the same record.

● Phantom Reading: two queries within a thing, in which if someone else adds a record and submits it, what can be found in the second query will be inconsistent with the first record.

There are many levels of transaction control, and the level determines the degree of isolation. There are four levels in MySQL:

● read uncommitted: this level is the lowest, the modification of A transaction is visible to B things, there will be dirty reading of data, and this type is not normally used.

● read has been submitted: the modification of A thing is not visible to B until it is submitted. In this case, there will be the problem of data phantom reading, and the results of the two queries are different.

● readable: it is the default level of MySQL. The two queries within the transaction at this level, and the other queries in the middle modify a record, which is invisible to other transactions, ensuring the consistency of the same record in the case of repeated check, but it is visible to other transactions when it is added, so there will still be new phantom reading.

● serializability: transactions are executed serially, every record queried is locked, blocking occurs, and concurrency can cause serious performance problems, so this type is generally not used.

An overview of isolation levels

Third, the realization of transaction isolation

Isolation in transactions is controlled in two ways: one is lock, which achieves isolation through time frustration, and the other is version control, which records multiple versions to achieve isolation.

1. Lock

Read lock and write lock in MySQL, read lock can read the same data at the same time, so it has the nature of sharing; write lock involves the change of data, so it conflicts with other write lock and read lock, and has exclusive nature.

Table-level locks and row-level locks are divided into table-level locks and row-level locks according to the granularity of the lock. Table locks usually occur when the table structure is modified or the whole table is updated, which blocks all read and write operations to the table. Row-level locks generally occur when the specified record is updated. Only the specified record is locked. The smaller the granularity of the lock is, the higher the concurrency is, and the priority row-level lock should be avoided as much as possible, which is the same principle as the granularity of lock in the program.

2. Multi-version concurrency control

MySQL for performance considerations in addition to row-level locking is another way, multi-version concurrency control, in which the control is implemented by the storage engine.

The book describes a simple implementation of InnoDB, which uses a way to record multiple versions, with two hidden columns added to each record, one is to create a version number, the other is to delete a version number, each transaction is assigned a transaction version number, the transaction version number is incremented, and intra-transaction operations are compared according to this version number. The details are as follows:

● query: query the records that existed before the current transaction and the records created by this transaction, and have not been deleted, that is, the created version number, the current version number)

When ● is inserted: the creation version number of the record is the current transaction version number.

When ● is deleted: the delete version number of the update record is the current transaction version number.

When ● updates: insert a new record, create the version number as the current transaction version number, and change the original record deletion version number to the current transaction version number, indicating that it has been deleted. In fact, the update here is equivalent to deleting and adding another record.

3. Optimistic lock and pessimistic lock

From the point of view of use, the lock is divided into pessimistic lock and optimistic lock. Pessimistic lock holds a very pessimistic attitude, thinking that the data I have found may be modified by others, so when querying, lock up this batch of data and do not allow others to operate. Optimistic lock holds a very optimistic attitude, thinking that the data I have found is basically impossible to be modified by others, so I do not lock this data when querying, and confirm whether it has been modified or not when I modify and submit it, which means it is not too late.

Fourth, the realization of optimistic lock and pessimistic lock:

● pessimistic lock can be easily solved at the database level, using select. For update, lock this part of the data when querying.

The implementation of ● optimistic lock is more complex than pessimistic lock, you can update the database in a version number column, the version number is + 1, in order to confirm whether the data I checked out has been modified by others, the modified one is not updated or the program throws an exception.

Use optimistic or pessimistic locks:

From a performance point of view, optimistic locking has better performance, and there is no locking operation during the period from query to update, but it is not as simple as pessimistic locking and may make mistakes. So the factor to consider is whether the concurrency of the system is high? What is the probability of conflict? In the case of high concurrency, it is better to choose optimistic lock, on the contrary, it is better to choose pessimistic lock.

After reading the above, the question of what is the mechanism of MySQL database transactions should be well answered? Take out your notebook and write it down.

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.