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

This article mainly introduces "what locks are there in the MySQL database". In the daily operation, I believe that many people have doubts about what locks there are in the MySQL database. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "what locks are there in the MySQL database?" Next, please follow the editor to study!

There are many types of locks in MySQL databases, but they can be roughly divided into three categories: global locks, table-level locks, and row-level locks. In this article, let's briefly talk about these three locks.

Global lock

Global lock is a relatively large granularity lock, basically can not be used, just like the door of our home, controls the entire database instance. A global lock is to lock the entire database instance so that the entire database is read-only.

MySQL provides a method to add a global read lock, and the command is Flush tables with read lock (FTWRL). After locking, the entire database instance is read-only, and commands related to data operations are suspended and blocked, such as data update statements, data definition statements, update transaction statements, and so on.

Therefore, global locks are generally only used for full database backups, and are generally only used when storage engines that do not support consistent reading do full database backups. For example, MyISAM, a storage engine that does not support consistent reading, needs to use global locks for full database backups, while InnoDB engines do not need to use global locks for full database backups.

Table level lock

Table-level locking is a very basic locking strategy for MySQL, and it is the least expensive strategy. It does not lock the entire database instance, but a table.

Table-level locks are the same as global locks, and the MySQL database provides locking commands: lock tables... Read/write . For example, lock tables T1 read, T2 write; commands, other threads write T1, read and write T2 statements will be blocked. At the same time, thread A can only read T1 and write T2 before executing unlock tables. You are not even allowed to write T1, and naturally you cannot access other tables.

We can use unlock tables to actively release the lock, if not used, automatically when the client is disconnected.

There is a problem with table-level locks. If a query is traversing the data in a table, and another thread changes the table structure and deletes a column during execution, then the result obtained by the query thread does not match the table structure. Definitely not.

In order to solve this problem, metadata lock (meta data lock,MDL) is introduced after MySQL version 5.5. MDL is automatically locked in the database, adding MDL read lock when adding, deleting, changing and searching a table, and MDL write lock when the table structure change operation is to be done.

MDL locks have the following two characteristics:

Read locks are not mutually exclusive, so you can have multiple threads to add, delete, change and query a table at the same time.

Read-write locks and write locks are mutually exclusive, which is used to ensure the security of the operation of the change table structure. Therefore, if two threads want to add fields to a table at the same time, one of them will have to wait for the other to finish execution before starting execution.

Row level lock

As the name implies, row-level lock is to lock the row records in the database table. Row-level lock can support concurrent processing to a great extent, but it also brings a lot of lock overhead.

Row-level locks are easier to understand, such as transaction A updates a row, and transaction B updates the same row at this time, it must wait for transaction A to complete the operation before updating.

Row-level locking is implemented separately by storage engines, and not all storage engines support row-level locking. For example, the MyISAM engine does not support row-level locking, which means that the MyISAM storage engine can only use table-level locks to control concurrency.

The InnoDB engine implements row-level locks, and two standard row-level locks are implemented in the InnoDB storage engine:

Shared lock (S Lock): allows a transaction to read a row

Exclusive lock (X Lock): allows transactions to delete and update a row

A shared lock is a compatible lock, that is, when a transaction has acquired a shared lock for row r, other transactions can immediately acquire a shared lock for row r, because reading does not change the data of row r.

An exclusive lock is an incompatible lock. If a transaction wants to acquire an exclusive lock on row r, if there is a shared lock or exclusive lock on row r, it must wait for other transactions to release the lock on row r.

In the InnoDB storage engine, consistent unlocked row reads are used by default, that is, row data is read through a row multi-version controller. We can display rows plus shared locks and exclusive locks, as shown below:

SELECT. FOR UPDATE: add an exclusive lock to read row records, and any locks that other transactions want to put on these rows will be blocked

SELECT. LOCK IN SHARE MODE: add a shared lock to the read row record. Other transactions can add a shared lock to the locked record, but want to add an exclusive lock. Will be blocked.

At this point, the study of "what locks are there in the MySQL database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.