A detailed introduction to the lock mechanism in MySQL database

This article gives you the content is about the MySQL database lock mechanism of the detailed introduction, there is a certain reference value, the need for friends can refer to, I hope you have some help.

Mysql lock shared

Pessimistic lock and optimistic lock:
Pessimistic lock: As the name implies, is very pessimistic, every time to take the data when they think others will change, so every time when the data are locked, so that others want to take this data will block until it gets the lock. Traditional relational database in the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are in operation before the lock.

Mysql lock

Optimistic lock: As the name implies, is very optimistic, every time to take the data when they think others will not be modified, so will not be locked, but in the update will be judged in the period when others have to update this data, you can use the version number and other mechanisms. Optimistic locking is useful for multi-read application types, which can improve throughput, such as the fact that a database provides an optimistic lock similar to the write_condition mechanism.

Mysql update lock 

Table level: Engine MyISAM, locks the entire table directly, and other processes cannot write to the table during your lock. If you are writing a lock, other processes are not allowed to read

Mysql show locks 

Page level: Engine BDB, table-level lock speed, but more conflict, less row-level conflict, but slow. So take a compromised page level and lock adjacent sets of records at once

Mysql select lock

Row level: Engine INNODB, which locks only the specified records so that other processes can operate on other records in the same table.

The characteristics of the above three types of locks can be broadly summarized as follows: database locks mysql 
1) Table-level lock: Low overhead, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, the concurrency is the lowest.
2) 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.
3) Row-level lock: Overhead, locking slow, deadlock, lock granularity is the least, the probability of lock conflict is the lowest, the concurrency is the highest.

Three kinds of locks have their own characteristics, if only from the point of view of the lock, table-level lock is more suitable for query-oriented, only a small number of index conditions to update data applications, such as Web applications, row-level locks are more suitable for a large number of index conditions to update a small number of different data, but also have concurrent query applications, such as some online transaction processing (OLTP) systems.

There are two modes of MySQL table-level lock:
1, table shared read lock (table read lock). When the MyISAM table is read, it does not block other users from reading requests to the same table, but blocks writes to the same table;
2, table exclusive write lock (table write lock). Writes to the MyISAM table will block read and write operations on the same table by other users.

The read and write of the MyISAM table is serial, that is, the read operation cannot be written, and vice versa. However, under certain conditions, the MyISAM table also supports the concurrency of queries and insertions, and its mechanism is done by controlling a system variable (Concurrent_insert), and when its value is set to 0 o'clock, concurrent insertions are not allowed; When its value is set to 1 o'clock, If there are no holes in the MyISAM table (that is, rows that are not deleted in the table), MyISAM allows a record to be inserted at the end of the table while another process is reading the table, and when its value is set to 2 o'clock, it is allowed to insert records concurrently in the footer, regardless of whether there are holes in the MyISAM table.

How the MyISAM lock dispatch is implemented is also a key issue. For example, when a process requests a read lock on one of the MyISAM tables and another process requests a write lock on the same table, will MySQL be treated as a priority process? Research shows that the write process will acquire the lock first (even if the read request is first to the lock waiting queue). But this also creates a big flaw, that a large number of writes can make the query operation difficult to obtain a read lock, which can cause permanent blocking. Fortunately, we can adjust the scheduling behavior of MyISAM through some settings. By specifying the parameter low-priority-updates, we can make the MyISAM default engine give the read request priority rights, set its value to 1 (set Low_priority_updates=1), and lower the priority.

The maximum difference between a innodb lock and a MyISAM lock is:
1, is the support transaction (trancsaction).
2, is the use of row-level lock.

We know that a transaction is a logical processing unit consisting of a set of SQL statements that has four properties (the acid attribute, for short), respectively:
Atomicity (atomicity): A transaction is an atomic operating unit whose modification of the data is either performed entirely or is not executed;
Consistency (consistent): data must be in a consistent state at the beginning and completion of a transaction;
Isolation (Isolation): The database system provides a certain isolation mechanism to ensure that transactions are performed in an "independent" environment that is not affected by external concurrency operations;
Persistence (Durable): After a transaction is complete, its modification to the data is permanent, even if a system failure occurs.

Problems caused by concurrent transaction processing
Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources, improve the transaction throughput of database system, so as to support more users. However, concurrent transaction processing also brings some problems, including the following situations.
1, update lost (Lost update): When two or more transactions select the same row, and then update the row based on the originally selected value, because each transaction is unaware of the presence of other transactions, a missing update problem occurs-the last update overwrites updates made by other firms. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who last saved its change copy overwrites the changes made by another editor. This problem can be avoided if another editor cannot access the same file until one of the editors finishes and commits the transaction.
2, Dirty Read (Dirty Reads): A transaction is modifying a record, before the transaction is completed and submitted, the data of this record is in an inconsistent state, then another transaction to read the same record, if not control, the second transaction read the "dirty" data, and further processing will result in uncommitted data dependencies. This phenomenon is visually called "Dirty reading".
3, non-repeatable read (non-repeatable Reads): A transaction after reading some data, some time after reading the previously read data, but found that its read data has changed, or some records have been deleted! This phenomenon is called "non-repeatable reading".
4. Phantom Read (Phantom Reads): A transaction re-reads the previously retrieved data in the same query condition, but finds that other transactions have inserted new data that satisfies its query criteria, which is called "Phantom reading".

Transaction ISOLATION LEVEL
In the problems associated with concurrency transactions mentioned above, "update loss" is usually completely avoided. However, preventing updates from being lost cannot be solved by the database transaction controller alone, requiring the application to fix the necessary locks for the data to be updated, thus preventing updates from being lost should be the responsibility of the application.
"Dirty reading", "non-repeatable reading" and "phantom reading" are all database read consistency problems, which must be solved by the database to provide a certain transaction isolation mechanism. The way the database implements transaction isolation is basically divided into the following two types.
1, one is to read the data before, lock it, prevent other transactions to modify the data.
2, the other is not to add any locks, through a mechanism to generate a data request point-in-time consistent data snapshot (Snapshot), and use this snapshot to provide a certain level (statement-level or transaction level) consistent read. From the user's point of view, it seems that the database can provide multiple versions of the same data, so this technique is called data multi-version concurrency control (multiversion Concurrency Control, abbreviated MVCC or MCC) and is often called a multi-version database.

The stricter the transaction isolation of the database, the smaller the concurrency side-effect, but the greater the cost, because transaction isolation is essentially the "serialization" of transactions, which is obviously contradictory to "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation, for example, many applications are not sensitive to "non-repeatable reads" and "phantom reads" and may be more concerned about the ability of data to be accessed concurrently.
In order to resolve the contradiction between "isolation" and "concurrency", Iso/ansi SQL92 defines 4 transaction isolation levels, each with different levels of isolation, different side-effects allowed, and applications can balance "isolation" and "concurrency" by selecting different isolation levels according to their business logic requirements. Table 20-5 provides a good overview of the characteristics of these 4 isolation levels.

Read data consistency and allowed concurrency side effects
Isolation level read Data consistency dirty read non-repeatable read Phantom read
Uncommitted reads (READ UNCOMMITTED) the lowest level, only guaranteed not to read physically corrupted data Yes Yes Yes
Committed degree (Read committed) statement level no Yes Yes
REPEATABLE READ (repeatable Read) transaction level no No Yes
Serializable (Serializable) highest level, transaction level no no No

The last thing to note is that each specific database does not necessarily fully implement the above 4 isolation levels, for example, Oracle only provides read committed and serializable two standard isolation levels, and also provides its own defined read only isolation level; SQL In addition to supporting the 4 isolation levels defined by the above Iso/ansi SQL92, server supports an isolation level called snapshot, but strictly it is a serializable isolation level implemented with MVCC. MySQL supports all 4 isolation levels, but when implemented, there are some features, such as the use of MVCC consistent read at some isolation levels, but in some cases it is not
InnoDB has two modes of row lock:
1) shared Lock (S): Allows one transaction to read a line, preventing other transactions from acquiring an exclusive lock on the same data set.
(Select * FROM table_name where ... lock in share mode)
2) exclusive Lock (X): Transactions that allow exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks of the same data set. (SELECT * FROM table_name where.....for update)
In order to allow the coexistence of row and table locks, a multi-granularity locking mechanism is implemented, and there are two kinds of intent locks (all table locks) used internally, namely intent-shared and intent-exclusive locks.
1) Intent Shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.
2) Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before it is given an exclusive lock on the data row.
InnoDB line lock mode compatibility List
Request Lock mode
is compatible
Current lock mode X IX S is
X Conflict conflict Conflict
IX Conflict compatible conflict compatible
S Conflict Compatibility compatible
Is conflict compatible compatible compatible
If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released.
The intent lock is innodb automatically and does not require user intervention. 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, and the transaction can be displayed to the recordset with shared or exclusive locks.
1. Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.
2. Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.
InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!
In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance.

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

Mysql> Show status like ' table% '; + ——————— –+ ——-+ | 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.
InnoDB Row lock contention:
You can analyze the contention for row locks on the system by examining the Innodb_row_lock state variables:

Mysql> Show status like ' innodb_row_lock% '; + ——————————-+ ——-+ | Variable_name                 | Value | + ——————————-+ ——-+ | Innodb_row_lock_current_waits | 0     | | Innodb_row_lock_time          | 0     | | Innodb_row_lock_time_avg      | 0     | | Innodb_row_lock_time_max      | 0     | | Innodb_row_lock_waits         | 0     | + ——————————-+ ——-+ 5 rows in Set (0.01 sec)

MyISAM Write lock Experiment:
The read operation on the MyISAM table does not block other users from reading requests to the same table, but it blocks write requests to the same table, and writes to the MyISAM table will block the read and write operations of the same table by other users, the read and write operations of the MyISAM table, and the write operations are serial! According to the example shown in table 20-2, when a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations on other threads wait until the lock is released.
USER1:

mysql> lock table Film_text write;

The current session can perform query, update, and insert operations on the locked table:

Mysql> Select Film_id,title from film_text where film_id = 1001;

USER2:

Mysql> Select Film_id,title from film_text where film_id = 1001;

Wait
USER1:
Release Lock:

mysql> unlock tables;

USER2:
To obtain the lock, the query returns:
Shared lock experiment of INNODB storage engine

user1:mysql> Set autocommit = 0; user2:mysql> Set autocommit = 0;

USER1:
The current session actor_id=178 record plus share mode's shared lock:

Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER2:
The other session can still query the record, and can also add share mode to the shared lock:

Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER1:
The current session updates the locked record, waiting for the lock:

Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178;

Wait
USER2:
Other sessions also update the record, which causes a deadlock to exit:

Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178;

ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction
USER1:
After the lock is acquired, it can be successfully updated:

Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched:1  changed:1  warnings:0

Example of an exclusive lock for the InnoDB storage engine

user1:mysql> Set autocommit = 0; user2:mysql> Set autocommit = 0;

USER1:
The current session of the actor_id=178 record plus an exclusive lock for update:

Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2:
Other sessions can query the record, but cannot add a shared lock to the record and wait for the lock to be acquired:

Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178;

USER1:
The current session can update the locked record and release the lock after the update:

Mysql> Update actor Set last_name = ' MONROE T ' where actor_id = 178;

USER2:
The other session gets the lock and gets the records submitted by the other session:

Mysql> Select Actor_id,first_name,last_name from actor where actor_id = 178 for update;

Several important parameters for updating performance optimizations
Bulk_insert_buffer_size
Bulk INSERT cache size, this parameter is for the MyISAM storage engine. For increased efficiency when inserting 100-1000+ records at once. The default value is 8M. It can be doubled for the size of the data volume.
Concurrent_insert
Concurrent insertions, when a table has no holes (deleted records), and in the case of a process acquiring a read lock, other processes can insert at the end of the table.
The value can be set to 0 does not allow concurrent insertions, 1 when the table does not have holes, to perform concurrent insertions, 2 regardless of whether there are holes to perform concurrent insertions.
By default, 1 is set for the deletion frequency of the table.
Delay_key_write
Defer updating the index for the MyISAM storage engine. means that when the update record is updated, the data is first up to disk, but not up, the index is in memory, and when the table is closed, the memory index is written to disk. A value of 0 does not turn on, and 1 is turned on. By default.
Delayed_insert_limit, Delayed_insert_timeout, delayed_queue_size
Delay the insertion, hand over the data to the memory queue, and then slowly insert it. However, these configurations, not all of the storage engine support, at present, the commonly used InnoDB is not supported, MyISAM support. According to the actual situation, the general default is sufficient.