MySQL row-level locks, table-level locks, page-level locks, mysql details

MySQL row-level locks, table-level locks, page-level locks, mysql details

Page Level: Engine BDB. Table-level: Engine MyISAM, which is interpreted as locking the entire table. It can be read at the same time but cannot be written at the row level. Engine INNODB locks a single row of records and directly locks the entire table, when you lock the table, other processes cannot write the table. If you are writing a lock, other processes are not allowed to read rows, and only lock the specified records. In this way, 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 principle of the table locking method used 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 no write lock is performed on the table, put 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: · only a few lock conflicts exist 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. Row-level locking disadvantages: · 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 using 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, however, when parallel insertion is not possible, you can insert records into the temporary table and update the data in the temporary table to the actual table on a regular basis. You can run 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; row-level locks have the advantages of reducing conflict locks 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 consume 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, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, the concurrency is the lowest. 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, its modifications to the data are either performed in full or not all; Consistency: data must be Consistent at the beginning and end of the transaction; Isolation ): the database system provides a certain isolation mechanism to ensure that the transaction is executed in an "independent" environment not affected by external concurrent operations; Durable: after the transaction is completed, the modification of data is permanent and can be maintained 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: allows transactions that obtain exclusive locks to update data, and prevents other transactions from obtaining the shared read lock and exclusive write lock for the same dataset. (Select * from table_name where ..... for update) to allow row locks and table locks to coexist and implement multi-granularity locks, there are also two internal intention locks (both are table locks ), the intention share lock and intention exclusive lock 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 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 lock at the end of the table when a process obtains the read lock. 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 delays updating indexes for the MyISAM storage engine. 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_in Sert_timeout, delayed_queue_size delayed insertion. Data is first transferred 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 based on actual conditions, generally,/* = ======================= */Since InnoDB defaults to Row-Level Lock, therefore, MySQL executes Row lock (only lock the selected data example) only when the specified Primary Key is "Explicit ), 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: (the primary key is specified, and row lock is available) SELECT * FROM products WHERE id = '3' for update; SELECT * FROM products WHERE id = '3' and type = 1 for update; Example 2: (specify the primary key explicitly. If this document is not found, no lock is required) 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: (table lock) SELECT * FROM products WHERE id <> '3' for update; example 5: (table lock) 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 (Row-level Locks/table-level locks) the locked data cannot be locked by other transactions or modified (modified or deleted) by other transactions. It is a table-Level Lock, and the table will be locked no matter whether or not the records are queried, if both A and B query the table id but no records are found, A and B do not perform row lock on the query, but A and B Both 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.

 

The difference between mysql row-level locks and table-level locks

You do not need to apply a lock. The lock already exists by default.
No lock required during Query

During addition, deletion, and modification operations, the system locks the table.

Mysql does not understand the lock table mechanism.

Table-level locks, page-level locks, and row-level locks are locks of different granularities. The difference is the lock range. As the name suggests, table-level locks request a lock throughout the table, A page lock requests a lock on a data page, and a row lock requests a lock on a row.
Generally, the update operation has a higher priority than the read operation, provided that the read operation has not been executed in the queue. If the read operation has started to  from the data, at this time, the lock resource of the update request has been locked by the read operation, so it can only be executed after the read operation is complete.