How to use MySQL table locks, row locks, exclusive locks and shared locks

1. Selection of transaction isolation mechanism

• If we don’t care at all, use uncommitted read Transaction isolation mechanism, if these threads are allowed to operate the database concurrently, dirty reads (uncommitted data is read), non-repeatable reads (two query values ​​are different), phantom reads (two query data volumes are different), etc. Problem, data has the lowest security, the advantage is that the concurrency efficiency is very high, generally it will not be used

• If we serialize (rely on Lock implementation), all transactions are sorted through locks. Although data security is improved, the concurrency efficiency is too low, and it is generally not used

• So we generally use Committed Read and Repeatable Read These two isolation levels balance the security, consistency and concurrency efficiency of data, and are controlled by MVCC multi-version concurrency Implemented (MVCC is the principle of committed read and repeatable read, and lock is the principle of serialization)

2. Table-level lock & row-level lock

Table-level lock: lock the entire table. The overhead is small (because you don’t need to find the record of a certain row in the table to lock it. If you want to modify this table, you directly apply for the lock of this table), the lock is fast, and there will be no deadlock; the lock granularity is large, and lock conflicts will occur. The probability is high and the concurrency is low

Row-level lock: Lock a row record. It is expensive (you need to find the corresponding record in the table, and there is a process of searching the table and index), locking is slow, and deadlock will occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is high

InnoDB storage engine supports transaction processing, the table supports row-level locking, and the concurrency capability is better

1. InnoDB row lock is through the index on the index It is implemented by locking items instead of locking row records in the table. This means that InnoDB will only use row-level locks to retrieve data through index conditions. Otherwise InnoDB will use table locks

2. Since InnoDB's row lock implementation is a lock added for index fields, not for row records, although different rows of the table under the InnoDB engine are accessed, if the same index field is used As a filter condition, lock conflicts will still occur, and can only be performed serially, not concurrently

3. Even if indexes are used in SQL, after passing through the MySQL optimizer,if It is considered that full table scanning is more efficient than using indexes. At this time, the use of indexes will be abandoned. Therefore, row locks will not be used
   , but table locks will be used. For example, for some small tables , MySQL will not use the index

3. Exclusive lock (Exclusive) and shared lock (Shared)

• Exclusive lock, Also known as X lock, write lock

• Shared lock, also known as S lock, read lock

Read (SS) It is compatible, but reading and writing (SX, SX) and writing (XX) are mutually exclusive.

There is the following relationship between adding X and S locks to transactions:

• A transaction adds S lock to data object A. It can perform read operations on A but cannot perform update operations. During the locking period, other transactions can add S locks to A but cannot add X locks

• If a transaction adds an X lock to data object A, it can read and update A. During the locking period, other transactions cannot add any locks to A

Display locking: select … lock in share mode to force shared lock acquisition, select … for update to acquire exclusive lock

1. Test exclusive locks and shared locks between different transactions Compatibility

Let’s first check the SQL and content of the table

Check the isolation level:

## First open a transaction and add an exclusive lock to the data with id=7

Then use another client to open the transaction

We use the service thread of another transaction to add an exclusive lock to the data with id=7, which is blocked.

We try to add an exclusive lock to the data with id=7. The shared lock is still blocked

Summary: For data locks between different transactions, only SS locks can coexist, and XX, SX, and XS cannot coexist

2. Test row lock addition On the index item

the execution lock is added to the index tree

Use the non-indexed fields of the table as filter conditions

Transaction 2 now also wants to get the ranking of this record It locks, and it predictably fails; now transaction 2 obtains the exclusive lock of chenwei's record, try to see if it can succeed

InnoDB supports row locks , when using the primary key id as the filtering condition, transaction 1 and transaction 2 can successfully acquire locks for different rows. However, now we find that we cannot obtain the exclusive lock named chenwei. Why is this? Let’s explain:

InnoDB’s row lock is implemented by locking index entries, rather than locking table row records

And we Using name as a filter condition does not use an index, so naturally row locks will not be used, but table locks will be used. This means that InnoDB uses row-level locks only when data is retrieved through the index, otherwise InnoDB will use table locks!!!

We add an index to the name field

We found that after adding an index to name, two transactions can obtain exclusive locks (for update) on different rows, once again proving that InnoDB's row locks are The

added to the index item is because the name now goes through the index. Through zhangsan, we find the id of the row record where it is located on the auxiliary index tree, which is 7, and then Go to the primary key index tree and obtain the exclusive lock of the corresponding row record (My personal guess is that the corresponding records in the auxiliary index tree and primary key index tree are locked)

4. Serialization isolation level test

(All transactions use exclusive locks or shared locks, no user is required to manually lock)

Set the serialization isolation level

Two transactions can acquire shared locks at the same time (SS coexistence)

Now let transaction 2 insert data

At this time, insert needs to add an exclusive lock, but since transaction 1 has added a shared lock to the entire table, transaction 2 can no longer successfully lock the table (SX does not coexist)

rollback

Because we added an index to name, the above select is equivalent to adding a row shared lock to the data with name zhangsan

Transaction 2 update

Transaction 2 cannot update because the entire table has been locked by the shared lock of transaction 1 at this time

Transaction 2 looks for zhangsan on the auxiliary index tree , find the corresponding primary key value, and then go to the primary key index tree to find the corresponding record, but find that this row of records has been locked by a shared lock. Transaction 2 can obtain the shared lock, but cannot obtain the exclusive lock

Let’s try using the primary key index id to see if we can update

It’s still blocked, although the field behind where we now use id instead of name, but name also finds the corresponding primary key through the auxiliary index tree, and then finds the corresponding record on the primary key index tree, and the records on the primary key index tree are locked (My personal guess is that the auxiliary index tree and The data corresponding to the primary key index tree is locked)

We updated the data with id=8 successfully. Only row locks are added to the row data with id 7, so we can successfully operate the data with id 8

If there is an index, row locks are used; if there is no index, Then use table lock.

Table-level locks or row-level locks refer to the granularity of the lock. Shared locks and exclusive locks refer to the nature of the lock. Whether it is a table lock or a row lock, there is a distinction between shared locks and exclusive locks

The above is the detailed content of How to use MySQL table locks, row locks, exclusive locks and shared locks. For more information, please follow other related articles on the PHP Chinese website!