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

The following is mainly to bring you MySQL database transaction knowledge, I hope these contents can bring you practical use, this is also the main purpose of my editing MySQL database transaction knowledge this article. Okay, no more nonsense, let's go straight to the following.

I. Foreword

Only the InnoDB engine supports transactions. The following contents take the InnoDB engine as the default condition.

2. Common concurrent problems 1. Dirty reading

One transaction reads uncommitted data from another transaction

2, can not be repeated reading

A transaction reads the same data inconsistently. Two reads modified by other transactions in between

3. Fantasy reading

Phantom reads refer to transactions that read a range of data because operations by other transactions result in inconsistent results between two reads. The difference between magic reading and non-repeatable reading is that non-repeatable reading is for a certain row of data, while magic reading is for uncertain multi-row data. Thus phantom reads usually appear in range queries with query conditions

Transaction isolation level 1. READ UNCOMMITTED

Dirty reading, non-repeatable reading and phantom reading may occur

2. READ COMMITTED

Avoid dirty reading, may produce non-repeatable reading, phantom reading

REPEATABLE READ (mysql default isolation level)

Avoid dirty reading, do not repeat reading. Avoid phantom reading by interval locking technique

4. Serialization (SERIALIZABLE)

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

What are database logs? 1. undo log

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

UNDO LOG is divided into two types, one is INSERT_UNDO (INSERT operation), record the unique key value inserted;

One is UPDATE_UNDO (containing UPDATE and Delete operations), which records the unique key value modified and the old column record.

2. redo log

mysql records all sq in a transaction to redo log, and then synchronizes records from redo log to data file

It can bring these benefits:

Crash occurs when dirty pages in buffer pool have not been refreshed to disk. After service is started, redo log can be used to find records that need to be refreshed to disk files.

The data in the buffer pool is flushed directly to the disk file, which is a random IO, and the efficiency is poor, while the data in the buffer pool is recorded to the redo log, which is a sequential IO, which can improve the speed of transaction submission.

3. binlog

Records used for database master-slave replication are in binary format. A disk write occurs after the transaction commits.

Note the difference between redo logs and binary logs, redo logs are generated by the storage engine layer, and binary logs are generated by the database layer. Suppose a large transaction, tba do 100,000 lines of record insertion, in this process, has been continuously redo log sequence records, and binary log will not record, until the transaction committed, will be written to the binary log file at a time

V. Database transaction control

1. By default, automatic transaction submission is enabled. Each sql execution corresponds to a transaction commit.

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

VI. ACID characteristics of transactions 1. Atomicity

All operations in a transaction as a whole are as indivisible as atoms and either all succeed or all fail.

Consistency (Consistency)

The result of a transaction execution must move the database from one consistent state to another. Consistency state refers to:1. The state of the system satisfies the integrity constraints of the data (primary code, reference integrity,check constraints, etc.) 2. The state of the system reflects the real state of the real world that the database should describe, such as the sum of the amounts of the two accounts before and after the transfer should remain unchanged. https://wenku.baidu.com/view/6bb581fdae45b307e87101f69e3143323868f5eb

3. Isolation

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

4. Durability

Once a transaction commits, its updates to the database are persistent. No transaction or system failure results in data loss.

5. redo log and undo log achieve atomicity, consistency, and persistence 6. lock mechanism achieves isolation 6.1. snapshot read

What is read is the snapshot version, which is the historical version. A normal SELECT is a snapshot read.

6.2 Currently Reading

Read the latest version. UPDATE, Delete, INSERT, SELECT … LOCK IN SHARE MODE, SELECT … FOR UPDATE is the current read.

6.3 lock read

In a transaction, the standard SELECT statement is unlocked, with two exceptions. SELECT … LOCK IN SHARE MODE AND SELECT … FOR UPDATE.

SELECT ... LOCK IN SHARE MODE

Assume shared locks for records, so that other transactions can only read and cannot modify until the current transaction commits

SELECT ... FOR UPDATE

Lock the index record. In this case, it is the same as locking the UPDATE record.

For the above knowledge about MySQL database transactions, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

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.