The original intention of database lock design is to handle concurrency issues. As a resource shared by multiple users, when concurrent access occurs, the database needs to properly control the rules for accessing resources.
Based on the scope of locking, the locks in MySQL can be roughly divided into three categories: global locks, table-level locks, and row-level locks.
Global Lock
A global lock is a lock on the entire database instance. MySQL provides a method to add a global read lock, the command is Flush tables with read lock (FTWRL).
When you need to put the entire database in read-only status, you can use this command, after which the following statements from other threads will be blocked: data update statements (data insert, delete, update), data definition statements (including creating tables, modifying table structure, etc.) and committing statements of update-type transactions.
A typical use case for global locks is to make a logical backup of the entire database. That is, select every table in the database and save it as text.
The danger of making the entire database read-only:
- If the backup is on the master database, then updates cannot be executed during the backup period, and business will basically have to stop;
- If the backup is on the slave database, then during the backup, the slave database cannot execute the binlog synchronized from the master database, which will cause master-slave delay.
The official logical backup tool is mysqldump. When mysqldump uses the –single-transaction parameter, it will start a transaction before exporting data to ensure a consistent view. The single-transaction method is only applicable to databases where all tables use the transaction engine.
Why not use the method of set global readonly=true?
- First, in some systems, the value of readonly is used for other logic, such as judging whether a database is a master database or a backup database. Therefore, the method of modifying global variables has a larger impact, and I do not recommend you to use it.
- Secondly, there is a difference in the exception handling mechanism. If the FTWRL command is executed and the client is disconnected due to an exception, MySQL will automatically release this global lock, and the entire database will return to a state where it can be updated normally. However, after setting the entire database to readonly, if the client has an exception, the database will keep the readonly state, which may cause the entire database to be unwritable for a long time, which is high risk.
Table-Level Lock
In MySQL, there are two types of table-level locks: one is a table lock, and the other is a metadata lock (MDL).
The syntax of table lock is lock tables … read/write, similar to FTWRL. You can actively release the lock using unlock tables, or it will be automatically released when the client is disconnected. Please note that the lock tables syntax not only limits the read and write of other threads but also specifies the operation object of this thread.
Another type of table-level lock is MDL (metadata lock). MDL does not need to be used explicitly, and it will be automatically added when accessing a table. The role of MDL is to ensure the correctness of read and write.
MDL was introduced in MySQL 5.5. When performing insert, delete, update, and select operations on a table, add an MDL read lock; when performing structural changes to the table, add an MDL write lock.
Features:
- Read locks do not exclude each other, so you can have multiple threads doing insert, delete, update, and select on a table at the same time.
- Read and write locks and write locks are mutually exclusive to ensure the safety of table structure change operations. Therefore, if two threads want to add fields to a table at the same time, one must wait for the other to finish execution before it can start execution.
How to safely add fields to small tables?
First of all, you need to solve the problem of long transactions. Transactions that are not committed will always hold the MDL lock. In the innodb_trx table in MySQL's information_schema library, you can check the transactions that are currently executing. If the table that needs to be changed with DDL happens to have a long transaction executing, consider pausing DDL or killing this long transaction.
Row Lock
MySQL's row locks are implemented at the engine layer by each engine itself. However, not all engines support row locks. For example, the MyISAM engine does not support row locks. Lack of support for row locks means that concurrency control can only use table locks. Row locks are locks on row records in data tables. In InnoDB transactions, row locks are added when needed, but they are not released immediately when they are no longer needed. Instead, they are released when the transaction ends. This is the two-phase locking protocol.
Deadlock and Deadlock Detection
When different threads in a concurrent system have circular resource dependencies and all involved threads are waiting for other threads to release resources, this can lead to these threads entering a state of endless waiting, known as deadlock.
When a deadlock occurs, there are two strategies:
- One strategy is to go straight into waiting until it times out. This timeout can be set by the innodb_lock_wait_timeout parameter.
- The other strategy is to initiate deadlock detection. After a deadlock is detected, it actively rolls back a transaction in the deadlock chain, allowing other transactions to continue. Setting the innodb_deadlock_detect parameter to on signifies the start of this logic.
In InnoDB, the default value of innodb_lock_wait_timeout is 50s.
Suppose there are 1000 concurrent threads that need to update the same line simultaneously, then the operation of deadlock detection is at the order of 1 million. Although the final detection result is that there is no deadlock, a large amount of CPU resources are consumed during this period. Therefore, you will see a high CPU utilization rate, but only a few transactions can be executed per second.
Update Process
When you need to update a data page, if the data page is in memory, it is directly updated. If the data page is not in memory, InnoDB buffers these update operations in the change buffer, so you don't need to read this data page from the disk. The next time a query needs to access this data page, it reads the data page into memory and then executes the operations related to this page in the change buffer. This ensures the correctness of this data logic. Although it's called change buffer, it's actually persistent data. That is, the change buffer has a copy in memory and will also be written to disk.
All update operations need to first judge whether this operation violates the uniqueness constraint. This must read the data page into memory to judge. If it is already read into memory, then directly updating memory will be faster, so there is no need to use the change buffer.
Therefore, unique index updates cannot use change buffer. In fact, only ordinary indexes can be used. The size of the change buffer can be dynamically set by the innodb_change_buffer_max_size parameter.
Reading data from disk into memory involves random IO access, which is one of the most expensive operations in the database. Change buffer reduces random disk access, so it will significantly improve the update performance.
Change Buffer Usage Scenarios
The main purpose of the change buffer is to cache the changes of records. So before merging a data page, the more changes the change buffer records (that is, the more times this page needs to be updated), the greater the benefit.
For write-heavy and read-light businesses, the chance of a page being accessed immediately after writing is relatively small, so the effect of using the change buffer is the best.
Suppose a business's update mode is to query immediately after writing. Even if the conditions are met and the update is recorded in the change buffer first, the subsequent immediate access to this data page will trigger the merge process. This does not reduce the number of random access IOs, but increases the maintenance cost of the change buffer.
How should we choose between ordinary indexes and unique indexes? In fact, there is no difference in query capabilities between these two types of indexes. The main consideration is the impact on update performance.
