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

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.

Disadvantage of row-level locking:
         consumes more memory than page-level or table-level locking.
         is slower than page-level or table-level locking when used in most of the table, because you have to get more locks.
         is significantly slower than other locks if you frequently perform group by operations on most data, or you must scan the entire table frequently.
         with high-level locking, you can easily adjust your application by supporting different type 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:

The code is as follows:
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 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.

Nnodb 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 row-level lock, table-level lock, page-level lock Detailed introduction