What is the difference between table locks and row locks in mysql

The difference between table locks and row locks in mysql is: 1. Table locks favor the myisam storage engine, and row locks favor the innodb storage engine; 2. Table locks have a small overhead, while row locks have a large overhead; 3. Table locks The lock granularity is large, and the lock granularity of row lock is small.

This article will provide a detailed introduction to MySQL table locks and row locks, and analyze and compare the differences. I hope it will be a reference for everyone.

(Video tutorial recommendation: mysql video tutorial)

1. Table lock

Features: Favor MyISAM storage engine, The overhead is small, locking is fast, and there is no deadlock. The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.

When we edit a table or execute a statement to modify a table, we usually add a table lock to the table to avoid some asynchronous things. There are two types of table locks: One is a read lock and the other is a write lock.

We can manually add these two locks to the table. The statement is:

lock table 表名 read(write);

Release the locks of all tables:

unlock tables;

View locks Table:

show open tables;

Add read lock (shared lock):

What will be the effect if we add read lock to the table?

1. The process we added the read lock to can read the table with the read lock, but cannot read other tables.

2. The process with read lock cannot update the table with read lock.

3. Other processes can read the read-locked table (because it is a shared lock), and can also read other tables

4. Other processes update the read-locked table The table will always be in a state of waiting for the lock, and the update will not be successful until the lock is released.

Write lock (exclusive lock):

1. The locking process can perform any operation (CURD) on the locked table.

2. Other processes cannot query the locked table and need to wait for the lock to be released

Summary:

Read lock will block writing , but will not block reading. The write lock will block both reading and writing. (Pay special attention to the process)

Analysis:

show status like 'table%';

Enter the above command to get:

+----------------------------+----------+
| Variable_name        | Value |
+----------------------------+----------+
| Table_locks_immediate | 105         |
| Table_locks_waited   | 3           |
+----------------------------+----------+

Table_locks_immediate: The number of table-level locks generated, indicating The number of queries that can immediately acquire the lock, and the value of the lock is increased by 1 for each immediate acquisition.

Table_locks_waited: The number of times table-level lock contention occurs and waits occur (the number of times the lock cannot be acquired immediately, the lock value increases by 1 for each wait). A high value indicates the existence of a more serious table. Level lock contention conditions.

2. Row lock

Features: Favors InnoDB storage engine, high overhead, slow locking; deadlocks may occur; locking granularity is minimal, and the probability of lock conflicts occurs The lowest and the highest concurrency.

Row locks support transactions, so the knowledge about transactions will be summarized in the next blog.

Behavior:

1. When we update a row but do not submit it, other processes also update the row and need to wait. This is the row Lock.

2. If we update a row, other processes updating other rows will not be affected.

Row locks are upgraded to table locks:

When our row locks involve index failure, the table lock behavior will be triggered.

Normally, each locks its own row and does not affect each other. One is 2000 and the other is 3000.

Since an index is built on column field b, if it is not used normally, it will cause Row lock changes to table lock

For example, if single quotes are not added, the index becomes invalid, row lock changes to table lock

is blocked, and waits. Only after Session_1 is submitted, the blocking is released and the update is completed

So, from this, we still have to make good use of index queries.

Gap lock:

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will give existing data that meets the conditions The index entry of the record is locked; for a record whose key value is within the condition range but does not exist, it is called a "gap". InnoDB will also lock this "gap". This locking mechanism is the so-called gap lock ( Next-Key lock).

Because if Query passes a range search during execution, it will lock all index key values ​​in the entire range, even if the key value does not exist.

The gap lock has a fatal weakness, that is, after locking a range of key values, even some non-existent key values ​​will be innocently locked, making it impossible to insert when locked. Lock any data within the key value range. In some scenarios, this may cause great harm to performance

Optimization suggestions:

Try to make all data retrieval complete through indexes to avoid unnecessary Index row locks are upgraded to table locks.

Design the index reasonably and reduce the scope of locks as much as possible

Fewer search conditions as much as possible to avoid gap locks

Try to control the transaction size and reduce the amount of locked resources and Length of time

Lowest level transaction isolation as possible

The above is the detailed content of What is the difference between table locks and row locks in mysql. For more information, please follow other related articles on the PHP Chinese website!