MySQL Lock explanation

A lock is a mechanism by which a computer coordinates multiple processes or threads concurrently accessing a resource . In a database, data is a resource shared by many users in addition to contention for traditional computing resources such as CPU, RAM, I/O, and so on. How to guarantee the consistency and validity of data concurrent access is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of database concurrent access.

MySQL has 3 kinds of locking mechanisms, features can be broadly summarized as follows.

• Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock collision is the highest probability, 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.

• Page Lock: Overhead and lock time are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

Compared to 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), BDB storage engines use page locks (page-level locking), but table-level locks are also supported The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

From the above characteristics can be seen, it is difficult to generally say which kind of lock better, only for the characteristics of the specific application which kind of lock more suitable! Only from the angle of the lock:

Table-level locks are more suitable for applications that are primarily query-based and have only a small number of update data by index criteria, such as Web applications;

Row-level locks are more suitable for applications that have a large number of simultaneous updates of different data by index conditions, and concurrent queries, such as some online transaction processing (OLTP) systems.

Overhead, lock, deadlock, lock granularity, lock conflict, concurrency. concurrent insertions of MyISAM tables

MyISAM table Locks are described below

Querying table-level lock contention

You can analyze table lock contention on the system by examining the table_locks_waited and table_locks_immediate state variables:

+-----------------------+-------+ | variable_name         | Value | +-----------------------+-------+ | Table_locks_immediate | 2979  | | table_locks_waited    | 0     | +-----------------------+-------+ 2 rows in Set (0.00 sec))

If the value of the table_locks_waited is higher, then there is a more serious table-level lock contention condition.

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

Table lock compatibility in MySQL

Request Lock mode is compatible Current lock mode	None	Read lock	Write lock
Read lock	Is	Is	no
write lock	Yes	no	no

The reading and writing of the MyISAM table is serial, but this is in general. Under certain conditions, the MyISAM table also supports concurrency of query and insert operations.

The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 0, 1, or 2, respectively.

• Concurrent insertions are not allowed when Concurrent_insert is set to 0 o'clock.

• When Concurrent_insert is set to 1 o'clock, if there are no holes in the MyISAM table (that is, rows in the middle of the table are not deleted), MyISAM allows a process to read the table while another process inserts records from the end of the table. This is also the default setting for MySQL.

• When Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.

Lock scheduling for MyISAM:

The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So, one process requests a read lock on a MyISAM table, and another process requests a write lock on the same table, how does mysql handle it? The answer is that the write process gets the lock first. Not only that, even if the read request goes to the lock waiting queue, and the write request is reached, the write lock is inserted before the read lock request! This is because MySQL considers writing requests to be generally more important than reading requests. This is why the MyISAM table is not well suited for applications with a large number of update operations and query operations, because a large number of update operations can cause query operations to be difficult to obtain read locks, which can be blocked forever. This situation can sometimes get very bad! Fortunately we can adjust the scheduling behavior of MyISAM by some settings.

• Enable the MyISAM engine to give priority to read requests by specifying the startup parameter low-priority-updates.

• Reduce the priority of update requests made by this connection by executing the command set Low_priority_updates=1.

• Reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.

Although the above 3 methods are either update first or query first method, but still can use it to solve the query of relatively important applications (such as user logon system), read lock waiting for serious problems.

Above has discussed the writing priority scheduling mechanism brings the problem and the solution. It is also important to emphasize that some long-running query operations can also cause the write process to "starve"! Therefore, the application should try to avoid long-running query operations, do not always want to use a SELECT statement to solve the problem, because this seemingly clever SQL statement, often more complex, the execution time is longer, where possible, by using the intermediate table and other measures to do a certain "decomposition" of SQL statements, Enables each step of the query to be completed in a shorter time, thereby reducing lock collisions. If complex queries are unavoidable, you should try to schedule them during the database idle time, such as some periodic statistics that can be scheduled for nightly execution.

Deadlock:

The so-called deadlock <deadlock>: Refers to two or more than two processes in the course of execution,

As a result of competing for resources, a kind of waiting phenomenon, if there is no external force, they will not be able to proceed.

At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always aulympic each other, are known as deadlock processes.

This article is from the "program ape of those Years" blog, please be sure to keep this source http://uyuyuuy.blog.51cto.com/6190986/1534277