High-performance MySQL reading notes-locks, transactions, isolation levels

1. LockWhy do I need a lock?because the database wants to solve concurrency control problems. At the same time, there may be multiple clients working on the same row of records in the table, such as some reading the row data, and other attempts to delete it. In order to ensure the consistency of data, the database is to control this concurrency, so there is the concept of lock. 1.1 Classification of the lock from the type of the data operation (read \ Write) points

Read lock (Shared lock): for the same piece of data, multiple read operations can be performed simultaneously without affecting each other.

Write lock (Exclusive lock): It blocks other write and read locks until the current write operation is complete.

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

1.2 Lock granularity (lock granularity)in order to increase the concurrency of the database as much as possible, the smaller the data range of each lock is better, in theory each time only the current operation of the data scheme will get the maximum concurrency, but the management of the lock is very resource-intensive things (involving acquisition, inspection, release locks and other actions), Therefore, the database system needs to balance high concurrent response and system performance, thus creating the concept of "lock granularity".
one way to increase the concurrency of shared resources is to make locked objects more selective. Try to lock only part of the data that needs to be modified, not all resources. The ideal way to do this is to precisely lock the data slices that will be modified. At any time, the less data is locked on a given resource, the higher the concurrency of the system, as long as there is no conflict between the other. However, locking also needs to consume resources. The various operations of the lock, including acquiring the lock, checking the lock and whether it has been lifted, releasing the lock, and so on, will increase the system overhead. The so-called locking strategy is to find a balance between the cost of the lock and the security of the data.

Table Locks : The cost of managing locks is minimal, while the allowable concurrency is also the smallest locking mechanism. MyISAM the lock mechanism used by the storage engine. When the data is to be written, the entire table is locked, while the other read and write actions are all waiting. In MySQL, in addition to the MyISAM storage engine's use of this locking strategy, MySQL itself uses table locks to perform certain actions, such as ALTER TABLE.

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

row lock : The maximum concurrent lock policy can be supported (and the maximum lock cost is also present). This strategy is used by the InnoDB and Falcon two storage engines. Row-level locks are implemented only at the storage engine layer, and the MySQL server layer is not implemented. The server layer does not understand the lock implementation in the storage engine at all.

MySQL is an open architecture where you can implement your own storage engine and implement your own lock granularity strategy, unlike Oracle, where you don't have the opportunity to change the lock strategy, Oracle is using row locks.

1.3 Deadlock (Dead Lock) A deadlock is the illusion that two or more transactions occupy each other on the same resource, and requests that the resource be locked by the other, causing a vicious circle. Deadlocks can also occur when multiple transactions lock the same resource at the same time. The database system implements various deadlock detection and deadlock timeout mechanisms, and InnoDB currently handles deadlocks by rolling back transactions that hold the fewest row-level exclusive locks.

2. Transactions (Transaction)

2.1 Transaction acid principle from a business perspective, a set of operational requirements for a database maintains 4 characteristics:

Atomicity (atomicity): A transaction must be considered an indivisible minimum unit of work, all operations in the entire transaction either commit successfully or all fail back, and for a transaction it is not possible to perform only a subset of the operations, which is the atomicity of the transaction.

Consistency (consistency): The database is always transitioning from one consistent state to another. The bank listed below will speak.

Isolation (Isolation): In general, changes made by an office are not visible to other transactions until they are finally submitted. Notice here that the "usual", behind the transaction isolation level will be said.

Durability (persistent): Once a transaction commits, its modifications are persisted to the database. When the system crashes, the modified data is not lost. (There is also a relationship between persistent security and the refresh log level, which corresponds to different levels of data security.) ）

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 commit (10233276 checking balance reduced 200,savings balance increased by 200), or full rollback (two table balances are not changed)

Consistency: The consistency of this example is reflected in the fact that 200 yuan does not go missing because the database system runs to line 3rd and crashes before line 4th because things have not yet been committed.

Isolation: Allows an action statement in one transaction to be isolated from the statement of another transaction, such as when transaction a runs to line 3rd, before line 4th, when transaction B queries the checking balance, it still sees the 200 dollars subtracted from transaction A (account money is unchanged). Because transactions A and b are isolated from each other. Before transaction a commits, transaction B observes no changes to the data.

Persistence: This is very well understood.

Transactions require a lot of work as well as locks, so you can choose a different storage engine based on your own needs to decide if you need transactional support.

2.2 Isolation levels (Isolation level)

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.
READ UNCOMMITTED (UNCOMMITTED)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).
Read Committed (submit reading)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.
REPEATABLE READ (Repeatable Read)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data . In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. the InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

Here is an introduction to MVCC strategy: http://blog.csdn.net/xifeijian/article/details/45230053

Serializable (Serializable)
This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another . In short, it is a shared lock on every data row read. at this level, a large number of timeouts and lock competitions can result.

These four isolation levels are implemented with different lock types, which can be problematic if the same data is being read. For example:

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.

non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process.

Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.

In MySQL, these four isolation levels are implemented, each of which can cause problems as follows:

High-performance MySQL reading notes-locks, transactions, isolation levels