MySQL Learning Note (v): MySQL table-level lock and row-level lock

One: Overview

Compared with other databases, MySQL's locking mechanism is relatively simple, and its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and memory storage engines use table-level locks (table-level locking), InnoDB storage engines support row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

MySQL main two types of lock characteristics can be broadly summarized as follows:
? Table-level Lock: Low overhead, lock fast, no deadlock (because MyISAM will get all the locks required by SQL), locking granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest.
? Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.

Consider the above characteristics, table-level lock use and concurrency is not high, to query the main, a small number of newer applications, such as small web applications, and row-level locks for high-concurrency environments, transaction integrity requirements of high-quality systems, such as online transaction processing system.

II: MyISAM Lock Detail

(1). Lock mode

There are two modes of table-level lock for MySQL: Table shared read lock (tables read lock) and table exclusive write locks (table write lock).

(2). How to Lock

When MyISAM executes the query statement, it automatically gives the table a read lock, and when the update operation is performed, the lock is written. Of course users can also use the lock TABLE to explicit locking. An explicit lock-up is typically applied to a consistent read of multiple tables at a point in time, otherwise, when the first table is read, the other tables are not automatically locked because they have not yet been read, and the data may change. And when the lock is displayed, only the locked table can be accessed and no other tables can be accessed.

(3). Concurrent Insert

The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of concurrent insertions and can take 0, 1, 2.

0 means that concurrent insertions are not allowed, and 1 indicates that rows that are not deleted in the middle of the table can be inserted at the end of the table and 2 indicate that they can always be inserted

Generally if the concurrency requirements are high, you can set to 2, you can always insert, and then periodically optimize the table during database idle time.

(4). Lock scheduling

Note that the read operation does not block other users ' read requests to the same table, but blocks write requests to the same table, and when the write lock and read lock are applied simultaneously, the write lock is preferred, which is why the lock collision probability of the table lock is the highest because the write lock may block the read lock all the time. So it is not suitable for working in an environment with lots of writing operations. This problem can be reduced by setting the Low-priority-updates this startup parameter to lower the write priority.
Although a write lock takes precedence over a read lock acquisition, a lengthy query operation may starve the write operation, so try to avoid a SQL statement that executes all of the queries and should perform the necessary decomposition.

Three: InnoDB lock detail

Because InnoDB supports transactions and uses row-level locks by default, there is a pretty big difference between innodb lock problems and MyISAM lock issues.

(1). Lock mode

A shared lock (S) and an exclusive lock (X) are similar to MyISAM read and write locks, respectively. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement.

(2). How to Lock

An explicit lock can be explicitly added with lock in share mode, and an exclusive lock is explicitly added with the for update.

It is important to note that if thread a adds a shared lock, thread B adds a shared lock to the same table, and a deadlock occurs when two threads require an update operation. Therefore, it is best to add an exclusive lock when updating operations.

(3). InnoDB the implementation of the row lock--Index plus lock

This is different from Oracle, so this also means (important): 1. Only row-level locks are used by InnoDB when data is retrieved through index criteria, or table-level locks are used. 2. Even if you are accessing a record that is not in the same row, a lock conflict occurs if you are using the same index key. 3. If the data table has multiple indexes, you can lock different rows by different indexes.

(4). Gap Lock

InnoDB support transactions, in order to meet the isolation level requirements, INNODB has a gap lock, when using scope lookup, InnoDB will meet the key range requirements, but the actual does not exist record lock. For example: the SELECT * from user where ID > updata will give the id>100 record an exclusive lock, which satisfies this range, but does not exist in the record, will be added Gap lock, so as to avoid the Phantom read, avoid inserting the record that satisfies the condition when reading.

(5). Isolation level and lock

In general, the higher the isolation level, the more stringent the locking. In this way, the probability of a lock collision is greater, in general practice, by optimizing the application logic, the choice of the level can be submitted to read enough. For some transactions that do require a higher isolation level, the SET session transaction isolation level+ "level" to dynamically change the satisfaction requirements.

Four: Deadlock

MyISAM is not a deadlock problem, because he will get all the locks at once. InnoDB can be automatically detected after a deadlock occurs, and a transaction is freed and rolled back, another transaction gets a lock, and the transaction continues to complete.

In the application, you can avoid deadlocks as much as possible in the following ways:

(1) If a different program accesses multiple tables concurrently, you should try to agree to access the table in the same order, which can greatly reduce the chance of deadlock generation.

(2) In the process of batch processing of data, if the data sorted in advance, to ensure that each thread in a fixed order to process the records, it can greatly reduce the possibility of deadlock.

MySQL Learning Note (v): MySQL table-level lock and row-level lock