Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the underlying principle of the lock mechanism in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Why should Mysql be locked

The locking mechanism is used to manage the concurrent access to shared resources. it is not only a mechanism to protect the database, but also a mechanism to ensure the consistency and integrity of transaction data in the transaction operation of the database. When multiple users access data concurrently, there may be multiple transactions in the database to operate a row of data at the same time. If we do not control such concurrent operations, incorrect data may be read and stored, resulting in a breach of data consistency. Let's take a look at a typical data loss and update problem caused by concurrent updates:

| | transaction A | transaction B | | begin A | begin B | | select salary form tb where id=1 (query result is 1000) | select salary form tb where id=1 (query result is 1000) | | update tb set salary=1100 where id=1 | update tb set salary=1200 where id=1 | | commit A | commit B | |

Abnormal result: the salary of the employee whose salary field id is 1 in the table is updated to 1200, but the salary of the employee is actually modified twice. Because transaction B is committed after transaction A, the update operation of transaction An is lost, so we need a locking mechanism to ensure that this will not happen and ensure the consistency of the data in the transaction.

Second, lock type

Table lock: low overhead, fast locking; no deadlock; large lock granularity, high probability of lock conflict and lowest concurrency

Row locks: high overhead and slow locking; deadlocks occur; small locking granularity; low probability of lock conflicts and high concurrency

3. MyISAM storage engine:

Lock mode of table-level lock: there are two modes of table-level lock: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write Lock); read operations on MyISAM tables do not block other users' read requests for the same table, but block write requests to the same table; write operations on MyISAM tables block other users' read and write operations on the same table The MyISAM table is serial between read and write operations, and between write and write operations.

Concurrent insertion (Concurrent Inserts): the read and write of MyISAM table is serial, but on the whole, under certain conditions, MyISAM table can also support the concurrent operation of query and insert; MyISAM storage engine has a system variable concurrent_insert, which is specially used to control its concurrent insertion behavior. When concurrent_insert is set to 0, then concurrent insertion is not allowed; when concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows one process to insert records from the footer while another process reads the table, which is also the default setting for MySQL; when concurrent_insert is set to 2, records are allowed to be inserted concurrently in the footer regardless of whether there are holes in the MyISAM table. This concurrent insert feature of the MyISAM storage engine can be used to resolve lock contention for queries and inserts of the same table in the application. For example, setting the value of the concurrent_insert variable to 2 always allows concurrent insertions, while defragmenting the space by executing OPTIMIZE TABLE statements periodically during the idle period of the system to recover the middle hole caused by deleting the record.

Lock scheduling of MyISAM engine: the read and write locks of MyISAM storage engine are mutually exclusive and serial during read and write operations. One process requests a read lock for a MyISAM table, while another process also requests a write lock for the same table. The write priority is higher than the read priority, so the write process acquires the lock first, even if the read request goes to the lock wait queue first, the write request goes to the lock wait queue, and the write lock page is inserted before the read lock request. We can adjust the scheduling behavior of MyISAM through some settings, make the MyISAM engine give priority to read requests by default by specifying the startup parameter low-priority-updates, reduce the priority of update requests issued by the connection by executing the command SET LOW_PRIORITY_UPDATE=1, and lower the priority of INSERT, UPDATE, and DELETE statements by specifying the LOW_PRIORITY attribute of the statement. In addition, MySQL also provides a compromise method to adjust the read-write conflict, that is, set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request, giving the read process a chance to acquire the lock.

4. InnoDB storage engine:

Optimistic lock and pessimistic lock are two kinds of concurrency control ideas, which can be used to solve the problem of lost updates: optimistic lock will assume that high probability of concurrent update conflict will not occur, and no lock will be added in the process of accessing and processing data. only when updating the data, we can judge whether there is a conflict according to the version number or timestamp. Pessimistic locking will "pessimistically" assume that there is a high probability that concurrent update conflicts will occur, adding exclusive locks before accessing and processing data, locking data throughout data processing, and releasing locks after transaction commits or rollback.

InnoDB storage engine standard row-level locks: shared locks (S Lock): read locks that allow transactions to read a row of data; exclusive locks (X Lock): write locks that allow transactions to delete or update a row of data.

# # transaction 1 MariaDB [test] > show variables like "autocommit"; +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.00 sec) MariaDB [test] > begin Query OK, 0 rows affected (0.00 sec) MariaDB [test] > update tb1 set name= "aaa" where id=1; Query OK, 1 row affected (0 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test] > commit; Query OK, 0 rows affected (0.00 sec) # # transaction 2: MariaDB [test] > show variables like "autocommit" +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.00 sec) MariaDB [test] > begin Query OK, 0 rows affected (0.00 sec) MariaDB [test] > update tb1 set name= "" where id=1; Query OK, 1 row affected (12.89 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test] > rollback; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > select * from tb1 where id=1 +-+-+ | id | name | +-+-+ | 1 | aaa | +-+-+ 1 row in set (0.00 sec)

How to implement InnoDB row locks:

InnoDB row locks are implemented by locking the index items on the index. The implementation of row locks such as InnoDB means that InnoDB uses row-level locks only when data is retrieved through index conditions, otherwise InnoDB will use table locks. When querying without index conditions, InnoDB does use table locks instead of row locks; because MySQL row locks are locks for indexes, not records, although they access records of different rows, lock conflicts will occur if the same index key is used When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB uses row locks to lock data, whether using primary key indexes, unique indexes, or normal indexes. Even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by MySQL by determining the cost of different execution plans. If MySQL thinks that a full table scan is more efficient, such as for small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks.

Intention lock: intention sharing lock (IS Lock): a transaction wants to acquire a shared lock for certain rows in a table; intention exclusive lock (IX Lock): a transaction wants to acquire an exclusive lock for certain rows in a table; view the lock information of the InnoDB storage engine:

MariaDB [test] > show engine innodb status\ Gbot MariaDB [test] > show engine innodb status\ Gpolitic MariaDB [test] > select * from information_schema.innodb_trx\ G * * 1. Row * trx_id: 1266629 trx_state: RUNNING trx_started: 2020-01-08 16:24:50 trx_requested_lock_id : NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 36696 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory _ bytes: 376trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 1 row in set (0.00 sec)

MariaDB [test] > select * from information_schema.innodb_locks\ G; Empty set (0.00 sec)

MariaDB [test] > select * from information_schema.innodb_lock_waits\ G; Empty set (0.00 sec)

Consistent unlocked read (Consistent Nonlocking Read): the InnoDB storage engine reads data from rows in the database at the current execution time through a number of version control (multi versioning) methods. If the read row is performing a DELETE or UPDATE operation, the read operation does not wait for the lock on the row to be released; instead, the InnoDB storage engine reads a snapshot of the row, which refers to the previous version of the row, and the implementation is done through the undo segment. Undo is used for transaction China rollback data, so there is no additional overhead for snapshot data itself. In addition, reading snapshot data does not need to be locked because there are no transactions that need to modify the historical data. However, for snapshot data at different transaction isolation levels, inconsistent reads always read the latest snapshot data of locked rows, while for snapshot data at the REPEATABLE READ transaction isolation level, inconsistent reads always read the row data version at the beginning of the transaction.

Consistent lock reads: lock database reads explicitly to ensure the consistency of data logic; SELECT. FOR UPDATE: add an X lock to the read row record, and no other transaction can add any lock to the locked row; SELECT. LOCK IN SHARE MODE: add an S lock to the read row record, and other transactions can add an S lock to the locked row, but if you add an X lock, it will be blocked

# # transaction 1 MariaDB [test] > begin; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > select * from tb1 where id=1 for update; +-+-+ | id | name | +-+-+ | 1 | aaa | +-+-+ 1 row in set (0.00 sec) MariaDB [test] > rollback Query OK, 0 rows affected (0.00 sec) # # transaction 2 MariaDB [test] > begin; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > select * from tb1 where id=1 lock in share mode; +-+-+ | id | name | +-- +-+ | 1 | aaa | +-+-+ 1 row in set (11.55 sec) MariaDB [test] > rollback Query OK, 0 rows affected (0.00 sec)

Locking algorithm:

5. Deadlock

Deadlock refers to the phenomenon that two or more transactions wait for each other because of competing for resources during execution. MyISAM table lock is Deadlock Free, because MyISAM always gets all the locks needed at once, either satisfy them or wait, so there will be no deadlock. However, in InnoDB, locks are acquired step by step except for transactions composed of a single SQL, which determines that it is possible to have a deadlock in InnoDB. After a deadlock occurs, InnoDB usually automatically detects it and causes one transaction to release the lock and roll back, while the other transaction acquires the lock and continues to complete the transaction. However, when external locks or table locks are involved, InnoDB can not automatically detect deadlocks, which needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not used to solve the deadlock problem. In the case of high concurrent access, if a large number of transactions are suspended because they cannot immediately obtain the required locks, it will take up a lot of computer resources. Cause serious performance problems and even drag down the database. We can avoid this by setting the appropriate lock wait timeout threshold.

# # transaction 1 MariaDB [test] > begin; Query OK, 0 rows affected (0 sec) MariaDB [test] > update tb1 set name= "jyy" where id=1; Query OK, 1 row affected (0 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test] > update tb1 set name= "xixi" where id=2; Query OK, 1 row affected (8.25 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test] > commit Query OK, 0 rows affected (0.00 sec) MariaDB [test] > select * from tb1 where id in (1Power2); +-+-- + | id | name | +-+-+ | 1 | jyy | | 2 | xixi | +-- +-+ 2 rows in set (0.00 sec) # # transaction 2 MariaDB [test] > begin Query OK, 0 rows affected (0.00 sec) MariaDB [test] > update tb1 set name= "" where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test] > update tb1 set name= "heihei" where id=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Common ways to avoid deadlocks:

1) in the application, if different programs access multiple tables concurrently, it should be agreed to access the tables in the same order as far as possible, which can greatly reduce the chance of deadlock. In the above example, due to the different order of the two session access tables, the chance of deadlock is very high, but if accessed in the same order, deadlock can be avoided.

2) when the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can be greatly reduced.

3) in a transaction, if you want to update the record, you should directly apply for a lock of sufficient level, that is, an exclusive lock, instead of applying for a shared lock first, resulting in lock conflicts or even deadlocks

4) under the REPEATABLE-READ isolation level, if two threads add an exclusive lock to the same condition record at the same time, both threads will lock successfully if the condition record is not met. The program finds that the record does not exist and tries to insert a record. If both threads do so, there will be a deadlock. In this case, the isolation level READ COMMITTED can avoid the problem.

5) when the isolation level is READ COMMITTED, if both threads execute SELECT...FOR UPDATE first, determine whether there is a qualified record, and if not, insert the record. At this point, only one thread can insert successfully, and the other thread will have a lock wait. when the first thread submits, the second thread will make an error because of the primary key conflict, but although this thread makes an error, it will obtain an exclusive lock. at this time, if a third thread applies for an exclusive lock, there will also be a deadlock.

6) if a deadlock occurs, you can use the above SQL command to check the lock information to determine the cause of the last deadlock. The returned result China includes details of the transactions related to the deadlock, such as the SQL statement that caused the deadlock, the locks that the transaction has acquired, what locks it is waiting for, and the transactions that have been rolled back. Based on this, the causes of deadlock and improvement measures can be analyzed.

These are the underlying principles of the locking mechanism in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.