Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the locking mechanism in the mysql database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Mysql locks are divided into three main categories:

Table-level lock: the storage engine is Myisam. Locking the whole table is characterized by low overhead, fast locking, strong locking, the highest probability of lock conflict and the lowest degree of concurrency.

Page-level lock: the storage engine is BDB. Lock a page of data (16kb or so), characteristics: overhead and lock time between the table level and row level; there will be a deadlock, locking strength between table lock and row lock, the degree of concurrency is general.

Row-level lock: the storage engine is innodb. Lock the data of a row, the characteristics: the implementation of the lock is more complex, expensive, slow locking speed.

According to the above characteristics, only from the point of view of locks: table-level locks are more suitable for applications that mainly rely on queries, with only a small amount of data updated according to index conditions, such as Web applications, while row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

Then the row-level lock is explained in detail, and the row-level lock is mainly divided into the following seven types: shared / exclusive lock, intention lock, record lock, gap lock, temporary lock, insert intention lock and self-increasing lock.

Shared / exclusive locks:

Shared lock: also known as read lock, can be read, but not written. Shared locks can be used with shared locks. Statement:

Select... Lock in share mode

Exclusive lock: also known as write lock, read is not allowed, write is not allowed, exclusive lock cannot be used with other places. Statement:

Select... For update

In mysql, exclusive locks are added to all update,delete,insert,alter writes by default. Select does not add any lock types by default. Once the task of writing data is not completed, the data can not be read by other tasks, which has a great impact on concurrent operations.

Intentional locks: innoDB introduces intentional locks in order to support multi-granularity locks, that is, allowing row-level locks and table-level locks to coexist. Intention lock means that at some point in the future, a transaction may have to add a shared / exclusive lock and declare an intention in advance. In this way, if someone tries to modify the whole table, there is no need to determine whether the data in the table is locked or not, just wait for the intended mutex to be released.

Intention shared lock (IS): if a transaction wants to acquire a shared lock for some records in a table, it needs to add an intention shared lock to the table first.

Intention mutex (IX): if a transaction wants to obtain a mutex for some records in a table, it needs to add an intentional mutex to the table.

Intention locks do not actually block any requests other than full table scans, their main purpose is to indicate whether someone is requesting to lock a row of data in the table.

RS: a lock on a single row record. Record locks always lock index records if innoDB stores engine tables

If no index is set at the time of creation, the innoDB storage engine will use an implicit primary key for locking.

Gap lock (GR): the gap lock locks the interval in the record, that is, the record of the range query.

Select * From user where id between 1 and 10 for update

This script locks data from 1 to 10 to prevent other transactions from modifying records in that interval.

The main purpose of the gap lock is to prevent other transactions from inserting data into the interval, resulting in "unrepeatable readings". If the isolation level of a transaction is downgraded to read commit (Read Committed, RC), the gap lock will automatically fail.

Temporary build lock (next-key Locks): a temporary build lock is a combination of a record lock and a gap lock, and the range of the lock contains both records and index intervals. By default, innoDB uses temporary locks to lock records. However, when the index of the query contains unique attributes, the temporary lock will be optimized and downgraded to a record lock, that is, only the index itself, not the range.

The main purpose of the key lock is also to avoid Phantom Read. If the isolation level of the transaction is downgraded to RC, the critical lock will also fail.

Insert intention lock (insert intention locks): for the modification and deletion of existing data rows, mutex must be added, and for data insertion, insert intention lock must be added. Is specific to insert operations.

Auto-inc locks: a special table-level lock that inserts auto-increment-type columns specifically for transactions. In the simplest case, if one transaction is inserting records into the table, all other transactions must wait so that the row inserted by the first transaction is a consecutive primary key value.

Let's take a look at the other locks:

Deadlocks: they are generated by alternating waiting between thread locks. A phenomenon of mutual waiting caused by competition for resources during the execution of two or more transactions.

The way Mysql handles deadlocks: roll back and forth small transactions according to the amount of data written.

Optimistic / pessimistic lock:

Optimistic lock: optimistic assumption that there will be no concurrent update conflicts, access, data processing process without locking, only update the data according to the version number or timestamp to determine whether there is a conflict, there is no responsibility to commit transactions.

If the system concurrency is very large, pessimistic locks will bring great performance problems, so choose to use optimistic locks. Now most applications belong to optimistic locks.

Pessimistic lock: it is pessimistic to assume that there is a high probability that concurrent update conflicts will occur, that exclusive locks will be added before accessing and processing data, that data will be locked during the entire data processing, and that locks will not be released until the transaction is committed or rolled back.

Advantages:

Pessimistic concurrency control is actually a conservative strategy of "lock first and then access", which ensures the security of data processing.

Disadvantages:

(a) in terms of efficiency, the mechanism for handling locking adds additional overhead to the database and increases the chances of deadlocks.

(B) in read-only transactions, since there is no conflict and there is no need to use locks, this will only increase the load on the system; and it will reduce parallelism. If a transaction locks a row of data, other transactions must wait for the transaction to finish processing that number of rows.

Recommendations:

Control the size of the transaction (the amount of data written by the operation) when using locks, try to cooperate with fields carrying indexes, avoid upgrading to table lock range queries, and minimize the size of transactions based on range queries. If the business must use locks, lock conflicts are particularly high. Change to table lock can adjust the transaction according to the situation of the project innodb_flush_log_at_trx_commit about the lock mechanism in the mysql database is shared here, I hope the above content can be of some help to everyone, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.