MySQL row-level lock, table-level lock, page-level lock detailed

page level: Engine BDB. Table level: Engine MyISAM, understood to lock the entire table, you can read at the same time, write No Line level: Engine INNODB, a separate row of record lock table level, directly lock the entire table, during your lock, other processes cannot write to the table. If you are a write lock, other processes read and do not allow row level, only lock the specified record 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. MySQL5. 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 the Write,mysql principle 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 the Read,mysql principle 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 single row of keywords that can be read with a simple reading: • UPDATETbl_nameSET column=ValueWHEREUnique_key_col=key_value; DELETE  fromTbl_nameWHEREUnique_key_col=key_value; SELECTA Parallel INSERT statement is combined with only a 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 lot of things on a table,INSERTAndSELECToperations, but parallel insertions are not possible, you can insert records into a staging table, and then periodically update the data in the staging table to the actual tables. Can be implemented with the following command: MySQL>LOCK TABLES real_table WRITE, insert_table write;mysql> INSERT  intoReal_tableSELECT *  fromInsert_table;mysql> TRUNCATE TABLEInsert_table;mysql>UNLOCK TABLES; The advantage of row-level locks is that they 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 disadvantage of row-level locks is that they consume more memory than page-level locks 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); The BDB storage engine is a page lock- Level-locking), also supports table-level locks; The InnoDB storage engine supports row-level locks, table-level locks, and row-level locks by default. The characteristics of the above three types of locks can be broadly summarized as follows:1table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock collision is the highest probability, concurrency is the lowest. 2) Row-level locks: high overhead, locking slow, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency. 3page 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 of MySQL table-level Lock: Table shared read lock (Table ReadLock) and table exclusive write locks (TableWrite 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. We can specify the parameter low by-Priority-Updates, which enables the MyISAM default engine to give the read request priority rights, setting its value to 1 (SetLow_priority_updates=1), which lowers the priority level. 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 with four properties (called the acid Attribute): atomicity (atomicity): A transaction is an atomic manipulation unit whose modifications to the data are either all executed or not executed Consistency (consistent): data must be in a consistent state at the beginning and completion of a transaction;Isolation): The database system provides a certain isolation mechanism to ensure that transactions are performed in a "stand-alone" environment that is not affected by external concurrency operations; persistence (durable): After the transaction completes, it changes the data to be permanent, even if a system failure occurs. InnoDB has two modes of row lock:1shared Lock: Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set. ( Select *  fromtable_namewhere... lockinchshare mode)2Exclusive locks: 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 *  fromtable_namewhere..... for Updatein order to allow row and table locks to coexist, implement a multi-granularity lock mechanism, there are two internal use of intent locks (all table locks), respectively, the intent to share the lock and intent exclusive lock. 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: INSERT, update performance optimization of several important parameters bulk_insert_buffer_size BULK INSERT cache size, this parameter is for the MyISAM storage engine. Suitable for inserting the- ++to improve efficiency. The default value is 8M. It can be doubled for the size of the data volume. Concurrent_insert concurrent insertions, when the table has no holes (Deleted records), in case a process acquires a read lock, Other processes can be inserted 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. The default is 1 for the table deletion frequency to set. Delay_key_write for MyISAM storage engines, Defer updating the index. 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. By default, the. Delayed_insert_limit, Delayed_insert_timeout, delayed_queue_size are deferred, the data is first handed to the memory queue, and then slowly inserted. But these configurations, not all storage engines support , at present, the 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 ========================*/since the InnoDB preset is row- LevelLock, so only the "explicit" specified primary key, MySQL will execute row lock (only to lock the selected data example), otherwise MySQL will execute table lock (the entire data form to lock). 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 *  fromProductsWHEREId='3'  for UPDATE;SELECT *  fromProductsWHEREId='3'  andType=1  for UPDATE; Example 2: (explicitly specify the primary key, if the information is not found, no lock)SELECT *  fromProductsWHEREId='-1'  for UPDATE; Example 3: (no primary key,TableLock)SELECT *  fromProductsWHEREName='Mouse'  for UPDATE; Example 4: (The primary key is ambiguous,TableLock)SELECT *  fromProductsWHEREId<>'3'  for UPDATE; Example 5: (the primary key is ambiguous,TableLock)SELECT *  fromProductsWHEREId like '3'  for UPDATE; Note 1: forUpdate is only available for InnoDB and must be in the trading block (BEGIN/COMMIT) in order 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. In MySQL5. 0 The test is exactly the same. Additionally: Myasim only supports table-level locks, INNERDB supports row-level locks (row- level locks/table-level lock) The data of the lock cannot be locked by another transaction, nor is it modified (modified, deleted) by another transaction to be a table-level lock, regardless of whether the record is queried or not, the table is locked in addition, if both A and B query the table ID but the query is not logged, A and b do not have row locks on the But A and B will get an exclusive lock, at which point A and then insert a record will be waiting because B already has a lock, at this time B and then insert the same data will throw deadlock found whenTrying toGet 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