MySQL row-level locks, table-level locks, and page-level locks

Page Level: Engine BDB.
Table-level: Engine MyISAM, which is interpreted as locking the entire table and reading at the same time.
Row-level: Engine INNODB, locking a single row of records

At the table level, the entire table is locked directly. During the lock period, other processes cannot perform write operations on the table. If you are writing a lock, other processes are not allowed to read it.
Row-level, which only locks specified records, so that other processes can still operate on other records in the same table.
Page-level, table-level locks are fast, but there are many conflicts, less row-level conflicts, but the speed is slow. Therefore, the compromised page level is used to lock a group of adjacent records at a time.

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

The locking method for READ and MySQL is as follows:
If there is no write lock on the table, place a read lock on it.
Otherwise, put the lock request in the read lock queue.

InnoDB uses row locking and BDB uses page locking. Both storage engines may have deadlocks. This is because, during SQL statement processing, InnoDB automatically acquires row locks and BDB obtains page locks, rather than at transaction startup.

Advantages of Row-level locking:
· There are only a few lock conflicts when different rows are accessed in many threads.
· Only a small number of changes are allowed during rollback.
· A single row can be locked for a long time.

Disadvantages of Row-level locking:
· More memory is occupied than page-level or table-level locking.
· When used in most tables, it is slower than page-level or table-level locking because you must obtain more locks.
· If you often perform group by operations on most data or scan the entire table frequently, it is much slower than other locks.
· With high-level locking, you can easily adjust applications by supporting different types of locking because the lock cost is lower than Row-level locking.

In the following cases, table locking takes precedence over page-level or row-level locking:
· Most of the table statements are used for reading.
· Read and update strict keywords. 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 without any write operations.

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

If you want to perform a large number of INSERT and SELECT operations on a table, but parallel INSERT is not possible, you can INSERT records into the temporary table, then, the data in the temporary table is updated to the actual table on a regular basis. You can use the following command:
Copy codeThe 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;

Row-level locks have the following advantages:
Reduce the conflict lock when multiple threads request different records.
Reduce data changes during transaction rollback.
It is possible to lock a single row of records for a long time.

Row-level locks have the following Disadvantages:
It consumes more memory than page-level locks and table-level locks.
A lock is a mechanism by a computer to coordinate multiple processes or threads to concurrently access a certain resource. The lock mechanisms of different databases are similar. Because database resources are resources shared by many users, how to ensure consistency and effectiveness of Concurrent Data Access is a problem that all databases must solve, lock conflicts are also an important factor affecting the concurrent database access performance. The unlocking mechanism not only enables us to develop and utilize database resources more effectively, but also enables us to better maintain the database and thus improve the database performance.

The locking mechanism of MySQL is relatively simple. Its most notable feature is that different storage engines support different locking mechanisms.

For example, the MyISAM and MEMORY storage engines use table-level-locking; The BDB storage engine uses page-level-locking ), table-level locks are also supported. the InnoDB Storage engine supports both row-level locks and table-level locks. Row-level locks are used by default.

The features of the above three locks can be roughly summarized as follows:
1) Table-level locks: low overhead and fast locking; no deadlocks; large lock granularity, the highest probability of lock conflicts and the lowest concurrency.
2) Row-level locks: high overhead and slow locking; deadlocks may occur; minimum lock granularity, minimum probability of lock conflicts, and highest concurrency.
3) page lock: the overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, and the concurrency is average.

Each of the three locks has its own characteristics. From the perspective of the lock, table-level locks are more suitable for queries-oriented applications with only a small amount of data updated based on index conditions, such as WEB applications; row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

MySQL Table-level locks can be used in two modes: Table Read Lock and Table Write Lock ). What does this mean? When reading a MyISAM table, it does not block read requests from other users to the same table, but blocks write operations on the same table; write operations on the MyISAM table will block read and write operations on the same table by other users.

The read and write operations of MyISAM tables are sequential, that is, they cannot be performed during read operations, and the opposite is true. However, under certain conditions, MyISAM tables also support concurrent query and insertion operations. The Mechanism is to control a system variable (concurrent_insert, when the value is set to 0, concurrent insertion is not allowed. When the value is set to 1, if the MyISAM table has no holes (that is, the table has no rows deleted ), myISAM allows another process to insert records from the end of the table while reading the table; when the value is set to 2, no matter whether the MyISAM table has holes, records can be inserted concurrently at the end of the table.

How is MyISAM lock scheduling implemented? This is also a key issue. For example, if a process requests the read lock of a MyISAM table and another process also requests the write lock of the same table, what will MySQL process preferentially? Studies show that the write process will first obtain the lock (even if the Read Request first goes to the lock wait Queue ). However, this also causes a major defect, that is, a large number of write operations will make query operations difficult to obtain read locks, which may cause permanent blocking. Fortunately, we can adjust the scheduling behavior of MyISAM through some settings. You can specify the low-priority-updates parameter so that the MyISAM default engine gives the Read Request priority and sets its value to 1 (set low_priority_updates = 1) to lower the priority.

The biggest difference between InnoDB locks and MyISAM locks is that they support transactions and use row-level locks. We know that a transaction is a logical processing unit composed of a group of SQL statements. It has four attributes (ACID attributes:

Atomicity: a transaction is an atomic operation unit. Modifications to the data are either performed in full or not all;
Consistent: data must be Consistent at the beginning and end of the transaction;
Isolation: the database system provides a certain Isolation mechanism to ensure that transactions are executed in an "independent" environment not affected by external concurrent operations;
Durable: after a transaction is completed, it modifies the data permanently, even if a system failure occurs.

InnoDB has two modes of row lock:

1) shared lock: allows a transaction to read a row to prevent other transactions from obtaining exclusive locks for the same dataset.
(Select * from table_name where... lock in share mode)

2) exclusive lock: allow the transaction that obtains the exclusive lock to update data and prevent other transactions from obtaining the shared read lock and exclusive write lock for the same dataset. (Select * from table_name where... for update)
In order to allow row locks and table locks to coexist and implement multi-granularity locks, there are also two types of internal intention locks (both table locks), which are intention shared locks and intention exclusive locks respectively.
InnoDB row locks are implemented by locking the index items. That is, InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, table locks are used!

In addition, insert and update several important parameters for performance optimization.
Copy codeThe Code is as follows:
Bulk_insert_buffer_size
Batch insert cache size. This parameter is for the MyISAM storage engine. this method improves the efficiency when more than 100-1000 records are inserted at a time. the default value is 8 Mb. double the data size.

Concurrent_insert
Concurrent insert. When the table has no holes (records deleted), other processes can insert the read locks at the end of the table when a process obtains the read locks.

The value can be set to 0 and cannot be inserted concurrently. 1. When the table has no holes, concurrent insertion is performed. 2. Concurrent Insertion is performed regardless of whether there are holes.
The default value is 1. The deletion frequency is set for the table.

Delay_key_write
For the MyISAM storage engine, Index Update is delayed. this means that when the update record is updated, the data is first up to the disk, but the index is not up, and the index is stored in the memory. When the table is closed, the memory index is written to the disk. if the value is 0, it is disabled. If the value is 1, it is enabled. enabled by default.

Delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
Delayed insertion: The data is first handed over to the memory queue and then inserted slowly. however, not all storage engines support these configurations. Currently, common InnoDB does not support these configurations, and MyISAM does. increase the value according to the actual situation. Generally, it is enough by default.

/* ================================ MySQL InnoDB Lock table and lock row ================ ================= */

Because InnoDB defaults to Row-Level Lock, MySQL will only execute Row lock (only Lock the selected data example) If a specified Primary Key is specified explicitly ), otherwise, MySQL will execute Table Lock (Lock the entire data form ).

For example, assume that there is a form products, which contains two columns: id and name. id is the primary key.

Example 1: (specify the primary key and use this document, row lock)
Copy codeThe Code is as follows: SELECT * FROM products WHERE id = '3' for update;
SELECT * FROM products WHERE id = '3' and type = 1 for update;

Example 2: (specify a primary key. If no data is found, no lock is required)
Copy codeThe Code is as follows: SELECT * FROM products WHERE id = '-1' for update;

Example 3: (no primary key, table lock)
Copy codeThe Code is as follows: SELECT * FROM products WHERE name = 'mouse 'for update;

Example 4: (the primary key is not clear, table lock)
Copy codeThe Code is as follows: SELECT * FROM products WHERE id <> '3' for update;

Example 5: (the primary key is not clear, table lock)
Copy codeThe Code is as follows: SELECT * FROM products WHERE id LIKE '3' for update;

NOTE 1: for update only applies to InnoDB and must be in the transaction block (BEGIN/COMMIT.
NOTE 2: To test the locking status, you can use the Command Mode of MySQL to open two windows for testing.

In MySql 5.0, the test is true.

In addition, MyAsim only supports table-level locks and InnerDB supports row-level locks.
Data that has been added (Row-Level Lock/table-Level Lock) locks cannot be locked by other transactions or be modified (modified or deleted) by other transactions)
The table is locked no matter whether or not records are queried.
In addition, if both A and B query the table id but cannot query the records, A and B do not apply the row lock in the query, but both A and B obtain the exclusive lock, at this time, if A inserts another record, it will be in the waiting state because B has A lock. If B inserts another record with the same data, it will throw the Deadlock found when trying to get lock; try restarting transaction and release the lock. At this time, A gets the lock and inserts it successfully.