High-performance MySQL Reading Notes-lock, transaction, isolation level, mysql transaction isolation level

High-performance MySQL Reading Notes-lock, transaction, isolation level, mysql transaction isolation level
1. Why should I lock the lock? Because the database needs to solve the concurrency control problem. At the same time point, multiple clients may operate on the same row of records in the table. For example, some clients may read this row of data and others may try to delete it. To ensure data consistency, the database must control such concurrent operations, so there is a lock concept. 1.1 The lock category is divided from the data operation type (read/write ).

Read locks (shared locks): For the same piece of data, multiple read operations can be performed simultaneously without affecting each other.

Write lock (exclusive lock): Before the current write operation is completed, it will block other write locks and read locks.

Most of the time, the internal management of MySQL locks is transparent.

1.2 Lock granularity (Lock granularity) in order to improve database concurrency as much as possible, the smaller the range of data to be locked, the better, theoretically, the scheme that only locks the data of the current operation will get the maximum concurrency, but the management lock is a resource-consuming task (involving operations such as obtaining, checking, and Releasing locks ), therefore, the database system needs to be balanced between high-concurrency response and system performance, which leads to the concept of "Lock granularity.
One way to improve the sharing and sending of resources is to make the locking object more selective. Try to lock only part of the data to be modified, rather than all resources. The more ideal way is to precisely lock the modified data slices. At any time, the less data is locked for a given resource, the higher the system concurrency, as long as there is no conflict between them. However, locking also consumes resources. Various operations of the lock, including obtaining the lock, checking the lock, checking whether it has been removed, and releasing the lock, will increase the overhead of the system. The so-called lock policy seeks to balance the lock overhead and data security.

Table lock: The minimum management lock overhead and the minimum allowed concurrency lock mechanism. The lock mechanism used by the MyIsam storage engine. When you want to write data, lock the entire table and wait for other read and write actions. In MySql, apart from using this lock policy by the MyIsam storage engine, MySql itself also uses table locks to execute certain specific actions, such as alter table.

In addition, the write lock has a higher priority than the read lock, so a write lock may be inserted before the read lock queue.

Row lock: Supports the maximum concurrency lock Policy (also brings the maximum lock overhead ). Both InnoDB and Falcon adopt this policy. Row-level locks are implemented only at the storage engine layer, but not at the MySQL server layer. The server layer is completely unaware of the lock implementation in the storage engine.

MySql is an open architecture. You can implement your own storage engine and implement your own lock granularity policies. Unlike Oracle, you have no chance to change the lock policy, oracle uses row locks.

1.3 deadlock refers to the illusion that two or more transactions occupy each other on the same resource and request to Lock the resources occupied by the other party, resulting in a vicious circle. A deadlock occurs when multiple transactions lock the same resource at the same time. The database system implements various Deadlock Detection and deadlock timeout mechanisms. InnoDB currently processes deadlocks by rolling back transactions that hold at least row-level exclusive locks.
2. Transaction)

2.1 From the business perspective, the ACID principle of transactions requires four features for a group of database operations:

Atomicity (Atomicity): A transaction must be considered an inseparable minimum unit of work. All operations in the entire transaction must be committed successfully or rolled back. For a transaction, it is impossible to perform only some of the operations, which is the atomicity of the transaction.

Consistency: the database always changes from one consistent state to another. The following bank column will talk about it.

Isolation (Isolation): Generally, modifications made by a firm are invisible to other transactions before they are finally committed. Note that the following transaction isolation level is generally used.

Durability (Durability): Once a transaction is committed, its modifications are permanently saved to the database. At this time, even if the system crashes, the modified data will not be lost. (The Persistence security is also related to the log refresh level. Different levels correspond to different data security levels .)

To better understand ACID, take bank account transfer as an example:

1 start transaction;

2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance-200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;

Atomicity: either full submission (10233276 of the checking balance is reduced by 200, and the savings balance is increased by 200) or full rollback (the balance of both tables does not change)

Consistency: the consistency in this example is shown in the figure that 200 yuan won't crash because the database system has run to 3rd rows and before 4th rows, because the transaction has not been committed.

Isolation: Operation statements in A transaction are allowed to be isolated from statements in other transactions. For example, when transaction A runs after 3rd and before 4th, transaction B queries the checking balance, it can still see the RMB 200 deducted from transaction A (the account money remains unchanged) Because transaction A and transaction B are isolated from each other. Before transaction A is committed, transaction B cannot observe the data changes.

Persistence: This is easy to understand.

Transactions and locks all require a lot of work. Therefore, you can decide whether to need transaction support based on your own needs and select different storage engines.

2.2 Isolation Level)

The SQL standard defines four isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible. Low-level Isolation generally supports higher concurrent processing and lower system overhead.
Read Uncommitted (Uncommitted Read)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications, because its performance is no better than other levels. Read uncommitted data, also known as Dirty Read ).
Read Committed)

This is the default isolation level for most database systems (but not for MySQL ). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports the so-called Nonrepeatable Read, because other instances of the same transaction may have a new commit during the processing of this instance, so the same select may return different results.
Repeatable Read (Repeatable Read)

This is the default transaction isolation level of MySQL,It ensures that multiple instances of the same transaction will see the same data row when reading data concurrently.. However, theoretically, this will lead to another tricky problem:Phantom Read ). In short, phantom read refers to when a user reads data in a certain range, another transaction inserts a new row in this range. When the user reads data in this range, there will be a new Phantom line.The InnoDB and Falcon storage engines solve this problem through the Multi-version Concurrency Control (MVCC, Multiversion Concurrency Control) mechanism.

The MVCC policy: http://blog.csdn.net/xifeijian/article/details/45230053 is introduced here

Serializable (Serializable) 
This is the highest isolation level. It forces transaction sorting to make it impossible to conflict with each other, thus solving the phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition.

The four isolation levels adopt different lock types. If the same data is read, problems may occur. For example:

Dirty Read: a transaction has updated the data, and another transaction has Read the same data at this time. For some reason, the previous RollBack operation is performed, the data read by the other transaction is incorrect.

Non-repeatable read: the data in the two queries of a transaction is inconsistent. This may be because the original data updated by a transaction is inserted in the two queries.

Phantom Read: the number of data records in two queries of a transaction is inconsistent. For example, a transaction queries several rows of data, another transaction inserts several new columns of data at this time. In the subsequent query, the previous transaction will find that several columns of data are not at the beginning.

In MySQL, these four isolation levels are implemented, which may cause the following problems: