MySQL row-level lock, table-level lock, page-level lock Detailed introduction

This article mainly introduces the MySQL row-level lock, table-level lock, page-level lock detailed introduction, while enumerating some examples, the need for friends can refer to the following

Page level: Engine BDB.
Table level: Engine MyISAM, understood to lock the entire table, can read at the same time, write No
Row level: Engine INNODB, single row of records plus lock

Table level, which 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
Row level, only locks the specified records so that other processes can operate on other records in the same table.
Page level, table-level lock speed, but more conflicts, row-level conflict is small, but slow. So we took a compromised page level and locked a contiguous set of records at once.

MySQL 5.1 supports table-level locking of MyISAM and Memory tables, page-level locking of BDB tables, row-level locking of InnoDB tables.
The table locking method used for Write,mysql is as follows:
If there is no lock on the table, put a write lock on it.
Otherwise, the lock request is placed in the write lock queue.

The locking method used for Read,mysql is as follows:
If there is no write lock on the table, put a read lock on it
Otherwise, the lock request is placed in the read lock queue.

InnoDB uses row locking, BDB uses page locking. There may be deadlocks for both of these storage engines. This is because, during SQL statement processing, InnoDB automatically obtains row locks and bdb to obtain page locks rather than when the transaction starts.

Advantages of row-level locking:
· There are only a few locking conflicts when accessing different rows in many threads.
· There are only a few changes when rolling back.
· You can lock a single row for a long time.

Disadvantages of row-level locking:
· Consumes more memory than page-level or table-level locking.
· When used in most of the table, it is slower than page-level or table-level locking because you have to get more locks.
· If you frequently perform group by operations on most data or you must scan the entire table frequently, it is significantly slower than other locks.
· With high-level locking, you can easily adjust your application by supporting different types of locking, because its lock cost is less than row-level locking.

Table locking takes precedence over page-level or row-level locking in the following cases:
· Most of the statements in the table are used for reading.
· With strict keywords read and updated, you can update or delete a row that can be extracted with a single read keyword:
· UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
· DELETE from Tbl_name WHERE unique_key_col=key_value;
· SELECT combines parallel INSERT statements with few update or DELETE statements.
· There are many scan or group by operations on the entire table, and there is no write operation.

/* ========================= MySQL lock table type and unlock statement ========================= */

If you want to do a large number of insert and SELECT operations on a table, but parallel insertions are not possible, you can insert the records into the staging table and then periodically update the data in the staging table to the actual tables. This can be accomplished with the following command:

mysql> LOCK TABLES real_table Write, insert_table write;
Mysql> INSERT into real_table SELECT * from insert_table;
mysql> TRUNCATE TABLE insert_table;
Mysql> UNLOCK TABLES;

The advantages of row-level locks are:
Reduce conflict locks when many threads request different records.
Reduce change data when a transaction is rolled back.
Making it possible to lock a single row of records for a long time.

The disadvantages of row-level locks are:
Consumes more memory than page-level and table-level locks.
A lock is a mechanism by which a computer coordinates multiple processes or threads concurrently accessing a resource, and the locking mechanism of a different database is similar. Because the database resource is a kind of resource that can be shared by many users, how to guarantee the consistency and validity of data concurrency is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of database concurrent access. Understanding the locking mechanism not only makes us more efficient in exploiting database resources, but also enables us to better maintain the database, thus improving the performance of the database.

MySQL's lock 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 are page locks (page-level-locking), and table-level locks are supported; The InnoDB storage engine supports row-level locks. Table-level locks are also supported, and row-level locks are used by default.

The characteristics of the above three types of locks can be broadly summarized as follows:
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) 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.
3) 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.

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 for MySQL table-level locks: table-Shared read lock and table write lock. What do you mean, it means that when you read a MyISAM table, it does not block other users from reading requests for the same table, but it blocks writes to the same table, and writes to the MyISAM table blocks other users from reading and writing to the same table.

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 biggest difference between InnoDB lock and MyISAM lock is: One is support transaction (trancsaction) and the other is 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.

InnoDB has two modes of row lock:

1) Shared Lock: Allows a transaction to read one 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: Transactions that allow exclusive locks are updated to prevent 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.
InnoDB row locks are implemented by locking the index entries, which means that only the data is retrieved through the index criteria, InnoDB uses row-level locks, or the table lock is used!

In addition: several important parameters of INSERT, update performance optimization

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. This means that when the update record is logged, the data is first up to disk, but not up, the index exists 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 turns on. Enabled 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, for the time being, commonly used INNODB does not support, MyISAM support. According to the actual situation, the general default is sufficient

/* ==================== MySQL InnoDB lock table with lock line ======================== */

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data sample) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).

For example: Suppose there is a form products with ID and name two fields, ID is the primary key.

Example 1: (explicitly specify the primary key, and there is this information, row lock)

SELECT * FROM Products WHERE id= ' 3 ' for UPDATE;
SELECT * FROM Products WHERE id= ' 3 ' and type=1 for UPDATE;

Example 2: (explicitly specify the primary key, if the information is not found, no lock)

SELECT * FROM Products WHERE id= '-1 ' for UPDATE;

Example 3: (No primary key, table lock)

SELECT * FROM Products WHERE name= ' Mouse ' for UPDATE;

Example 4: (primary key ambiguous, table lock)

SELECT * FROM Products WHERE id<> ' 3 ' for UPDATE;

Example 5: (primary key ambiguous, table lock)

SELECT * from the products WHERE ID like ' 3 ' for UPDATE;

Note 1:for Update applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect.
NOTE 2: To test the condition of the lock, you can use the command Mode of MySQL and open two windows to do the test.

Testing in MySQL 5.0 is exactly like this.

In addition: Myasim only supports table-level locks, INNERDB supports row-level locks
Data that has been added (row-level lock/table-level Lock) locks cannot be locked by other transactions or modified (modified, deleted) by other transactions
is a table-level lock, the table is locked regardless of whether the record is queried
In addition, if both A and B query the table ID but not the records, A and b do not have a row lock on the query, but a and B will get an exclusive lock, when a and then insert a record will be waiting because B already has a lock, then B and then insert the same data will throw deadlock Found when trying to get lock; Try restarting transaction then releases the lock, at which point a lock is obtained and the insert succeeds

MySQL row-level lock, table-level lock, page-level lock Detailed introduction