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

This article is about how to deeply analyze MySQL database transactions. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

I. Preface

Only the InnoDB engine supports transactions, and the following takes the InnoDB engine as the default condition

Second, common concurrency problems 1. Dirty reading

One transaction reads uncommitted data from another transaction

2. Non-repeatable

The result of a transaction's reading of the same data is inconsistent. The middle of the two reads was modified by other transactions

3. Illusory reading

Illusory reading means that when a transaction reads a certain range of data, the results of the two readings are inconsistent because of the operations of other transactions. The difference between phantom reading and unrepeatable reading is that unrepeatable reading is for a certain row of data, while phantom reading is for uncertain multiple rows of data. Therefore, illusory reading usually occurs in range queries with query conditions.

Transaction isolation level 1, read uncommitted (READ UNCOMMITTED)

May produce dirty reading, unrepeatable reading, and phantom reading.

2. Read submitted (READ COMMITTED)

Avoid dirty reading, which may lead to unrepeatable reading and phantom reading

3. Readable (REPEATABLE READ) (mysql default isolation level)

Avoid dirty reading and do not repeat it. The illusion is avoided by the interval lock technology.

4. Serialization (SERIALIZABLE)

Serialization can avoid all possible concurrency exceptions, but it will greatly reduce the concurrent processing capacity of the system.

What are the database logs? 1. Undo log

The undo log is used to store the value before the data is modified.

There are two types of UNDO LOG, one is INSERT_UNDO (INSERT operation), the unique key value of record insertion

One is UPDATE_UNDO (including UPDATE and DELETE operations), which records the unique key value of the modification and the old column record.

2. Redo log

Mysql records all sq in a transaction to redo log before synchronizing records from redo log to data files.

It can bring these benefits:

When the dirty page in buffer pool has not been flushed to disk, crash occurs. After starting the service, you can find the records that need to be refreshed to disk files through redo log.

The data in buffer pool is directly flush to disk file, which is a random IO and inefficient, while recording the data in buffer pool to redo log is a sequential IO, which can improve the speed of transaction commit.

3. Binlog log

The records used for master-slave replication in the database are in binary format. Make a disk write after the transaction is committed.

Note the difference between redo log and binary log. Redo log is generated by the storage engine layer, while binary log is generated by the database layer. Suppose a large transaction inserts 100000 rows of records into tba, and in the process, it keeps recording sequentially to redo log, but binary log does not record it, until the transaction commits, it is written to the binary log file at once.

5. Database transaction control

1. By default, the transaction auto-commit function is enabled. Each sql executed corresponds to the commit of a transaction

2. Spring sets the autocommit feature of the underlying connection to false. Use manual submission

6. ACID characteristics of transactions 1. Atomicity

All operations in a transaction as a whole are as inseparable as atoms, either all succeed or all fail.

2. Consistency (Consistency)

The execution result of the transaction must move the database from one consistency state to another. The consistency state refers to: 1. The state of the system satisfies the data integrity constraints (master code, referential integrity, check constraints, etc.) 2. The state response database of the system should have described the real state of the real world, such as the sum of the amount of the two accounts before and after the transfer should remain unchanged.

3. Isolation (Isolation)

Transactions executed concurrently do not affect each other, and their impact on the database is the same as when they are executed serially. For example, if multiple users transfer money to one account at the same time, the final result of the account should be the same as the result of their transfer in order.

4. Persistence (Durability)

Once a transaction is committed, its update to the database is persistent. No transaction or system failure will result in data loss.

5. Redo log and undo log realize atomicity, consistency and persistence. 6. Lock mechanism achieves isolation 6.1 and snapshot reading.

What is read is the snapshot version, that is, the historical version. Ordinary SELECT is snapshot reading.

6.2. Current read

The latest version is read. UPDATE 、 DELETE 、 INSERT 、 SELECT... LOCK IN SHARE MODE 、 SELECT... FOR UPDATE is currently read.

6.3. Lock read

In a transaction, the standard SELECT statement does not lock, but there are two exceptions. SELECT... LOCK IN SHARE MODE and SELECT... FOR UPDATE .

SELECT... LOCK IN SHARE MODE

Assume a shared lock for the record, so that other transactions can only be read and cannot be modified until the current transaction commits

SELECT... FOR UPDATE

Lock the index record, which is the same as that of UPDATE

6.4. Consistent unlocked read

Consistent read (consistent read), InnoDB uses multiple versions to provide a snapshot of the query database at a certain point in time. If the isolation level is REPEATABLE READ, then all consistency reads in the same transaction read the first such read snapshot in the transaction; if it is READ COMMITTED, then each consistency read in a transaction reads its own refreshed snapshot version. Consistent read (consistent read) is the default mode for normal SELECT statements under READ COMMITTED and REPEATABLE READ isolation levels. Consistent reads do not lock the tables it accesses, so other transactions can modify them concurrently.

The above is how to make an in-depth analysis of MySQL database transactions, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.