The lock on the MySQL

There are many locks in MySQL, such as optimistic locks, pessimistic locks, row locks, table locks, Gap locks, MDL locks, intentional locks, read locks, write locks, shared locks, and exclusive locks. So lock a listen to let my head big, so go to see some blogs, some about optimistic lock, pessimistic lock, some about read lock, write lock, so optimistic lock and pessimistic lock seems to understand, read lock write lock seems to understand, but I still don’t know how to use, also don’t know optimistic lock and read lock write lock have what relationship? After reading a lot of articles, I gradually understand the relationship between them, so I wrote this article to sort out my thoughts. Capacity is limited, unavoidable error, please refer to.

Although there are many lock nouns listed above, these locks are not in the same dimension, which is why I am vague. Next, MySQL locks are analyzed from different dimensions.

Read locks and write locks

First of all, a read lock is also called a shared lock, and a write lock is also called an exclusive lock, which means that a shared lock and a read lock are the same thing, and an exclusive lock and a write lock are the same thing. Read lock, write lock is the system implementation level of the lock, but also the most basic lock. Read lock and write lock or a property of lock, such as row lock, there are row write lock and row read lock. MDL lock also has MDL write lock and MDL read lock. The lock relationship between read locks and write locks is as follows: Y indicates that they can coexist, and X indicates that they are mutually exclusive.

As can be seen from this table, read locks and write locks cannot coexist. Consider a scenario where a read lock is occupied by a request and a write lock is blocked by another request, but the resource is occupied by the read lock. However, if the read lock is occupied by subsequent requests, the read lock is not released, causing the write lock to wait. To prevent this from happening, the database is optimized so that when a write lock blocks, subsequent read locks block as well, thus avoiding starvation. We’ll see this phenomenon again later.

The storage model of MySQL has been introduced in previous articles. For InnoDB engine, B+ tree index is adopted. Assuming that the whole table needs to be locked, each node in the whole B+ tree needs to be locked, which is obviously a very inefficient practice. Therefore, MySQL proposed the concept of intent lock. Intent lock means that if you want to lock a node, you must add intent locks on all its ancestor nodes. For more complex designs on intent locks, check out the book Probability of Database Systems.

Table and row locks

Table locks and row locks are two different types of locking granularity. In addition to table and row locks, there are more granular locks — global locks.

Global lock: A global lock locks the entire database. MySQL uses flush tables with read lock to add a global lock and unlock tables. The lock is automatically released when the thread exits. When a global lock is added to a table, any update operations performed by any thread other than the current thread are blocked, including adding or deleting data from the table, creating a table, or modifying the table structure. A typical use scenario for global locking is a logical backup of a full library.

Table lock: a table lock locks a table. MySQL uses Lock tables

MySQL uses the lock in share mode command to lock a row or rows in a table. MySQL uses the lock in share mode command to lock a row or rows in a table. MySQL uses the lock in share mode command to lock a row or rows in a table. Select k from t where k = 1 for update lock all rows where k = 1. In addition, when the update command is used to update the table data, a row lock is automatically placed on the hit rows. MySQL does not lock all rows at once. After an update command is executed, server layer sends the command to InnoDB engine. InnoDB engine finds the first data that meets the condition and locks it back to Server layer. The server layer updates this data and passes it to the InnoDB engine. After this data is updated, the Server layer retrieves the next data.

To verify this process, start by creating a table and inserting a few rows with the following command

mysql-> create table t(id int not null auto_increment, c int not null, primary key(id))ENGINE=InnoDB;
mysql-> insert into t(id, c) values (1, 1), (2, 2), (3, 3);
Copy the code

Select * from t where id = 3 for update (select * from t where id = 3 for update); The isolation level of transaction C is changed to uncommitted read, as shown in the table below. The first two rows have been updated, and the last row with id 3 is not updated, indicating that transaction B is blocked here.

mysql> select *  from t;
+----+---+
| id | c |
+----+---+
|  1 | 0 |
|  2 | 0 |
|  3 | 3 |
+----+---+
Copy the code

Optimistic locks and pessimistic locks

Optimistic locking

Optimistic locking always assumes that no conflicts will occur, so resources are read without locking, only to determine at update time whether other transactions have updated the data during the entire transaction. If no other transaction updates this data, the update succeeds. If no other transaction updates this data, the update fails.

Pessimistic locking

Pessimistic locking always assumes that a conflict will occur, so the data is locked at the time it is read so that only one thread can change the data at the same time. Table locks and row locks introduced earlier in this article are pessimistic locks.

Optimistic locking and pessimistic locking are two different locking strategies. Optimistic locking assumes scenarios with fewer collisions, and therefore is suitable for scenarios with more read and less write. Pessimistic locks, on the other hand, are suitable for scenarios where you write more than you read. Optimistic locks are lighter because they do not need to maintain lock resources or block locks like pessimistic locks.

Implementation of optimistic locking

Optimistic locking can be implemented in two ways: version number and CAS algorithm

The version number

There are several steps to implement optimistic locking by version number:

1 Add a version field to each data to indicate the version number

2 After starting the transaction, read the data, save the version number in the data, and then perform other processing

Compare version1 with the current version of the database for the last update. Update t set version = version + 1 where version = version1 As we know from the previous transaction article, the update operation does the current read, so the latest version number is fetched even at the repeatable read isolation level. If no other transaction has updated this data, version equals version1, and the update succeeds. If the data has been updated by other transactions, the value of the version field is increased, so version is not version1, and the update is not effective.

CAS algorithm

CAS is the abbreviation of compare and swap, which translates into Chinese: compare first and then swap. CAS implementation pseudocode:

<< atomic >>
bool cas(int* p, int old, int new)  
{
    if(*p ! = old) {return false
    }
    *p = new
    return true
}
Copy the code

Where p is the variable pointer to be modified, old is the old value before modification, and new is the new value to be written. This pseudo-code means that the value pointed to by p is the same as the old value, if not, the data has been modified by another thread, return false. If they are the same, the new value is assigned to the object pointed to by p, returning true. This whole process is implemented through hardware synchronization primitives, ensuring that the whole process is atomic.

CAS functions are implemented in most languages, such as C in GCC:

bool__sync_bool_compare_and_swap (type *ptr, type oldval type newval, ...)
type __sync_val_compare_and_swap (type *ptr, type oldval type newval, ...)
Copy the code

Lock-free programming is actually implemented through CAS, such as the implementation of lock-free queues. The introduction of CAS also brought ABA problems. There will be a special article on CAS that summarizes lockless programming.

MDL lock and Gap lock

MDL lock

MDL locks are also table-level locks, and MDL locks do not require display use. MDL locks are used to avoid conflicts between data operations and table structure changes. If you are executing a query and another thread is deleting a table field, the two will collide, so MySQL added MDL locks after 5.5. An MDL read lock is added when adding, deleting, or modifying a table, and an MDL write lock is added when structural changes are made to a table. Read locks are compatible with each other, but read locks and write locks are incompatible.

The MDL needs to be careful to avoid MDL write locks blocking MDL read locks.

Transaction A assigns A MDL read lock to table T after performing select. After transaction B performs select, the MDL read lock is added to the table again. Read lock and read lock are compatible. Transaction C will block when executing alter and MDL write lock is required on table T. Transaction C blocking is not a big problem, but will cause all subsequent transactions to block, such as transaction D. This is to prevent write locks from starving to death. MySQL optimizes locks to wait while write locks are waiting. If transaction C does not hold the lock for a long time, or if transaction C executes for a long time, the database operation will be blocked.

To avoid this, there are several optimization ideas:

Avoid long transactions. Transactions A and B that are long may cause transaction C to block for A longer time in the MDL write lock.

2 For large tables, the table structure modification statement can be split into multiple small transactions, so that the MDL write lock is occupied for a shorter time each time the table structure is changed.

3 Add wait timeout to alter command

Gap lock

Gap locks are introduced by the InnoDB engine to avoid phantom reads. As discussed in MySQL transactions, the InnoDB engine can avoid phantom reads at repeatable read isolation levels. A gap lock locks the gaps between data rows to prevent new data from being inserted. The gap lock is only applied when the current read is in progress. For information on what is currently read, see my previous article “Transactions for MySQL”.

Lock the practice

It is impossible to analyze how a statement will be locked. Analysis of lock, must be combined with transaction isolation level and index, Ali database experts have written a very detailed analysis of the article, directly posted to learn together, MySQL lock processing analysis

Mind mapping

At the end of this article, I put up a mind map of MySQL, which is a summary of the MySQL series of articles.

reference

[1] Database System Concepts (6th edition)

[2] Lin Xiaobin, Lin Xiaobin

[3] High Performance MySQL (Version 3)

[4] Transaction isolation level and mysql transaction isolation level changed

[5] Li, Y. *, Li, Y. *, Et al

[6] Optimistic lock, pessimistic lock, this one is enough!