MySQL lock mechanism

Classification of locks

• From the type of data operation:

• Read lock (shared lock): For the same piece of data, multiple read operations can be performed at the same time without affecting each other.

• Write lock (exclusive lock): Before the current write operation is completed, it will block other write locks and read locks.

• From the granularity of data operations: row locks and table locks

Three locks

• Overhead, locking speed, deadlock, granularity, and concurrency performance can only be based on the characteristics of specific applications which lock is more suitable

• Three locks: table lock (partial read), row lock (partial write), page lock.

Table lock

• Features: Lean toward the MyISAM storage engine, low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.

• The following demonstrates adding a read lock

mysql> create table mylock(
    -> id int not null primary key auto_increment,
    -> name varchar(20)
    -> )engine myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mylock(name) values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mylock(name) values('b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mylock(name) values('c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mylock(name) values('d');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mylock(name) values('e');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)

#Manually increase table lock
# lock table table name read(write), table name 2 read(write),...;
mysql> lock table mylock read,book write;
Query OK, 0 rows affected (0.00 sec)

#View the locks added on the table
#Some other tables are omitted here
mysql> show open tables;
+--------------------+---------------------------- --------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
| db26 | mylock | 1 | 0 |
| db26 | book | 1 | 0 |

#Unlock
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables;
+--------------------+---------------------------- --------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
| db26 | mylock | 0 | 0 |
| db26 | book | 0 | 0 |



#Demonstrate below,
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)

#The following is using another session2 for operation
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
/*Read lock is a shared lock*/

#session1Operation
mysql> update mylock set name='a2' where id=1;
ERROR 1099 (HY000): Table'mylock' was locked with a READ lock and can't be updated

mysql> select * from book;
ERROR 1100 (HY000): Table'book' was not locked with LOCK TABLES

• It can be found that after the read lock is added to session1, the table can only be read, and any other operations on the table are illegal, and at the same time, no operations can be performed on other tables. Session2's reading of the table and any operations on other tables are not affected.However, if the table is modified, blocking will occur (that is, the cursor will keep flashing), when the table is unlocked, and when the blocking can pass.

• The following demonstrates adding write lock

mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)

mysql> update mylock set name='a4' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from book;
ERROR 1100 (HY000): Table'book' was not locked with LOCK TABLES
/*It is found that other tables cannot be viewed*/

#Use session2 operation
mysql> select * from mylock;
/*Blocking found*/

• It is found that after the write lock is added, only this session can perform read and write operations, but cannot operate other tables, and other sessions cannot perform any operations on this table.

• MyISAM will automatically add read locks to all tables involved before executing the query statement (select), and will automatically add write locks to the tables involved before performing addition, deletion, and modification operations. MySQL's table-level lock has two modes: table shared read lock (Table Read Lock) table exclusive write lock (Table Write Lock)

• Combining the above table to operate on MyISAM table, there will be the following situations:

• Reading a MyISAM table (adding a read lock) will not block other processes' read requests to the same table, but will block write requests to the same table. Only when the read lock is released, the write operations of other processes will be executed.

• Write operations to MyISAM tables (write locks) will block other processes from reading and writing to the same table.Only when the write lock is released, the read and write operations of other processes will be performed.

• In short: a read lock will block writes, but not reads. The write lock will block both reading and writing.

Table lock analysis

• Check which tables are locked:

show open tables;

• How to analyze table locks: You can analyze table locks on the system by checking the table_locks_immediate and table_locks_waited state variables:

mysql> show status like'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 113 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 3 |
| Table_open_cache_misses | 10 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)

• The first variable represents the number of table-level locks, the number of queries that can immediately acquire the lock, and the value of the lock is increased by 1 every time the lock is acquired immediately;

• The second variable represents the number of waits for mark lock contention (the number of times that the table cannot be obtained immediately, and the lock value is increased by 1 for each wait).A high value indicates that there is a serious table-level lock contention.

• In addition, MyISAM's read-write lock scheduling is write first, which is why MyISAM is not suitable for writing to the main table engine. Because after the lock is written, other threads cannot do any operations, and a large number of updates will make it difficult for queries to get the lock, resulting in permanent blockage.

Row lock

• Features: bias towards the InnoDB storage engine, high overhead, slow locking; deadlocks will occur; the smallest locking granularity, the lowest probability of lock conflicts, and the highest concurrency. There are two biggest differences between InnoDB and MyISAM: one is to support transactions; the other is to use row-level locks.

• A transaction is a logical processing unit composed of a set of SQL statements.The transaction has the following four attributes, usually referred to as the ACID attribute of the transaction:

• Atomicity (Atomicity): A transaction is an atomic operation unit.The modification of the data is either all executed or not executed at all;

• Consistency (Consistent): At the beginning and completion of the transaction, the data must be in a consistent state. This means that all relevant data rules must be used to modify the transaction to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or doubly linked lists) must also be correct.

• Isolation: The database system provides a certain isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate state in the transaction process is indistinguishable from the outside, and vice versa.

• Durable: After the transaction is completed, its modification to the data is permanent and can be maintained even if a system failure occurs.

• Problems caused by concurrent transaction processing:

• Lost Update

• Dirty Reads

• Non-Repeatable Reads

• Phantom Reads

• Transaction isolation level: dirty reads, non-repeatable reads and phantom reads are actually database read consistency problems, which must be solved by the database to provide a certain transaction isolation mechanism.

• The stricter the transaction isolation of the database, the smaller the side effects of concurrency, but the greater the price to pay, because transaction isolation essentially makes transactions "serialized" to a certain extent, which is obviously in contradiction with "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation.For example, many applications are not sensitive to "non-repeatable reads" and "phantom reads", and may be more concerned about concurrent access to data.

• View the current database transaction isolation level:

show variables like 'tx_isolation';

mysql> create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_innodb_lock values(1,'b2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb_lock values(3,'3');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb_lock values(4,'4000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb_lock values(5,'5000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb_lock values(6,'6000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb_lock values(7,'7000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_innodb_lock values(8,'8000');
Query OK, 1 row affected (0.11 sec)

mysql> insert into test_innodb_lock values(9,'9000');
Query OK, 1 row affected (0.27 sec)

mysql> insert into test_innodb_lock values(1,'b1');
Query OK, 1 row affected (0.00 sec)

mysql> create index test_innodb_a_ind on test_innodb_lock(a);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index test_innodb_lock_b_ind on test_innodb_lock(b);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

• Perform two sessions first: set autocommit=0;This is equivalent to blocking and will not be automatically submitted.

• Then operate on session1:

mysql> update test_innodb_lock set b='4001' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4001 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)

• However, if you operate session2 again at this time, you will find that the data in the table has not changed.

mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)

• At this time, use session1 for commit operation, session2 still cannot query the correct result. The correct approach is that session2 also performs a commit, so that after that, you can get the correct result by querying again.

• If you do the following:

#session1
mysql> update test_innodb_lock set b='4002' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#session2
mysql> update test_innodb_lock set b='4003' where a=4;

/*Blocked, indicating that the row lock has an effect*/


#session1
mysql> update test_innodb_lock set b='4005' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#session2
mysql> update test_innodb_lock set b='9001' where a=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
/*In this case, it is found that both sides will execute normally, and the row lock does not affect other rows*/

• It should be noted that when there is no index or index failure, row locks will become table locks, which greatly reduces performance.

• If session1 is executed update test_innodb_lock set b ='0629' where a>1 and a<6;, session2 is executed. insert into test_innodb_lock values(2,'2000');Note that the line a=2 does not exist, and there is no commit in session1 at this time, and session2 is found to be blocked. Then session1 commits, and the line a=2 is added to the table normally.

• When using range conditions instead of equal conditions to retrieve data and request a shared or exclusive lock, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the condition range but do not exist, It is called gap GAP. InnoDB will also lock this gap.This locking mechanism is called a gap lock.

• The hazard of gap lock: Because the search through the range during the Query execution process will lock all the index key values in the entire range, even if the key value does not exist.

• 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, resulting in the inability to insert any data within the locked key value range when locking.

• How to lock a row:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

#Proceed to lock
mysql> select * from test_innodb_lock where a=8 for update;
+------+------+
| a | b |
+------+------+
| 8 | 8000 |
+------+------+
1 row in set (0.00 sec)

#Unlock, other processes will be blocked during the period
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

• Because the InnoDB storage engine implements row-level locking, although the performance loss caused by the implementation of the locking mechanism may be higher than that of table-level locking, the overall concurrent processing power is far due to MyISAM's table-level locking. When the system concurrency is high, the overall performance of InnoDB will have obvious advantages compared with MyISAM.

• However, InnoDB's row-level locking also has its fragile side.When we use it improperly, the overall performance of InnoDB may even be worse than that of MyISAM.

• How to analyze row locking:show status like 'innodb_row_lock%';

• ...current_waits: The number currently waiting to be locked.

• ...time: The total length of time since the system is started to the current lock.

• ...time_avg: Average time spent in each waiting

• ...time_max: The longest time

• ...waits: The total number of waiting times from the start of the system to the present

• For these five state variables, the more important ones are the average waiting time, the total number of waiting, and the total waiting time.

Optimization suggestion

• As far as possible, data retrieval is done through the index, to avoid the upgrade of non-indexed row locks to table locks

• Design the index reasonably to minimize the scope of the lock

• As few search conditions as possible, avoid gap lock

• Try to control the size of the transaction, reduce the amount of locked resources and the length of time.

• As low-level transaction isolation as possible.

Page lock

• The overhead and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average. Just need to understand.