MySQL lock mechanism
definition
Definition: a lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently
Classification of locks
Classification of locks:
1. From the type of data operation (read / write)
- Read lock (shared lock): for the same data, multiple read operations can be performed simultaneously without affecting each other.
- Write lock (exclusive lock): it will block other write locks and read locks before the current write operation is completed.
2. From the granularity of data operation
- Watch lock
- Row lock
Three locks
Watch lock
characteristic
Features: biased towards MyISAM storage engine, low overhead and fast locking; No deadlock; The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.
Case study (MyISAM engine)
Create table SQL
create table mylock (
id int not null primary key auto_increment,
name varchar(20)
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
Lock unlock command
mysql> show open tables; +--------------------+----------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+----------------------------------------------+--------+-------------+ | mysql | time_zone_transition_type | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | setup_timers | 0 | 0 | | performance_schema | events_waits_history_long | 0 | 0 | | mysql | time_zone_transition | 0 | 0 | | performance_schema | mutex_instances | 0 | 0 | | performance_schema | events_waits_summary_by_instance | 0 | 0 | | mysql | tables_priv | 0 | 0 | | mysql | procs_priv | 0 | 0 | | mysql | func | 0 | 0 | | performance_schema | events_waits_history | 0 | 0 | | mysql | time_zone_name | 0 | 0 | | mysql | user | 0 | 0 | | deptemp | mylock | 0 | 0 | | performance_schema | file_instances | 0 | 0 | | performance_schema | cond_instances | 0 | 0 | | mysql | plugin | 0 | 0 | | mysql | db | 0 | 0 | | mysql | proxies_priv | 0 | 0 | | mysql | time_zone | 0 | 0 | | performance_schema | rwlock_instances | 0 | 0 | | performance_schema | events_waits_current | 0 | 0 | | mysql | event | 0 | 0 | | mysql | columns_priv | 0 | 0 | | performance_schema | performance_timers | 0 | 0 | | performance_schema | threads | 0 | 0 | | mysql | host | 0 | 0 | | performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 | | performance_schema | file_summary_by_event_name | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | | performance_schema | file_summary_by_instance | 0 | 0 | | performance_schema | setup_instruments | 0 | 0 | | mysql | servers | 0 | 0 | | performance_schema | setup_consumers | 0 | 0 | +--------------------+----------------------------------------------+--------+-------------+ 34 rows in set (0.00 sec) -- to mylock The meter is locked for reading, book Table write lock mysql> lock table mylock read,book write; Query OK, 0 rows affected (0.02 sec) -- In_use A field of 1 indicates that it is locked mysql> show open tables; +--------------------+----------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+----------------------------------------------+--------+-------------+ | mysql | time_zone_transition_type | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | setup_timers | 0 | 0 | | performance_schema | events_waits_history_long | 0 | 0 | | mysql | time_zone_transition | 0 | 0 | | performance_schema | mutex_instances | 0 | 0 | | performance_schema | events_waits_summary_by_instance | 0 | 0 | | mysql | tables_priv | 0 | 0 | | mysql | procs_priv | 0 | 0 | | mysql | func | 0 | 0 | | performance_schema | events_waits_history | 0 | 0 | | mysql | time_zone_name | 0 | 0 | | mysql | user | 0 | 0 | | deptemp | mylock | 1 | 0 | | performance_schema | file_instances | 0 | 0 | | performance_schema | cond_instances | 0 | 0 | | mysql | plugin | 0 | 0 | | mysql | db | 0 | 0 | | mysql | proxies_priv | 0 | 0 | | mysql | time_zone | 0 | 0 | | performance_schema | rwlock_instances | 0 | 0 | | performance_schema | events_waits_current | 0 | 0 | | mysql | event | 0 | 0 | | deptemp | book | 1 | 0 | | performance_schema | performance_timers | 0 | 0 | | performance_schema | threads | 0 | 0 | | mysql | host | 0 | 0 | | performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 | | performance_schema | file_summary_by_event_name | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | | performance_schema | file_summary_by_instance | 0 | 0 | | performance_schema | setup_instruments | 0 | 0 | | mysql | servers | 0 | 0 | | performance_schema | setup_consumers | 0 | 0 | | mysql | columns_priv | 0 | 0 | +--------------------+----------------------------------------------+--------+-------------+ 35 rows in set (0.00 sec) -- Unlock mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> show open tables; +--------------------+----------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+----------------------------------------------+--------+-------------+ | mysql | time_zone_transition_type | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | setup_timers | 0 | 0 | | performance_schema | events_waits_history_long | 0 | 0 | | mysql | time_zone_transition | 0 | 0 | | performance_schema | mutex_instances | 0 | 0 | | performance_schema | events_waits_summary_by_instance | 0 | 0 | | mysql | tables_priv | 0 | 0 | | mysql | procs_priv | 0 | 0 | | mysql | func | 0 | 0 | | performance_schema | events_waits_history | 0 | 0 | | mysql | time_zone_name | 0 | 0 | | mysql | user | 0 | 0 | | deptemp | mylock | 0 | 0 | | performance_schema | file_instances | 0 | 0 | | performance_schema | cond_instances | 0 | 0 | | mysql | plugin | 0 | 0 | | mysql | db | 0 | 0 | | mysql | proxies_priv | 0 | 0 | | mysql | time_zone | 0 | 0 | | performance_schema | rwlock_instances | 0 | 0 | | performance_schema | events_waits_current | 0 | 0 | | mysql | event | 0 | 0 | | deptemp | book | 0 | 0 | | performance_schema | performance_timers | 0 | 0 | | performance_schema | threads | 0 | 0 | | mysql | host | 0 | 0 | | performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 | | performance_schema | file_summary_by_event_name | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | | performance_schema | file_summary_by_instance | 0 | 0 | | performance_schema | setup_instruments | 0 | 0 | | mysql | servers | 0 | 0 | | performance_schema | setup_consumers | 0 | 0 | | mysql | columns_priv | 0 | 0 | +--------------------+----------------------------------------------+--------+-------------+ 35 rows in set (0.00 sec)
Read lock
Read locks are shared locks, which can be viewed by other sessions (left session1, right session2)
-- session1 Locked tables can be read, not written, and other tables cannot be viewed 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
-- session2 Can read session1 Locked tables cannot be written (blocked. As long as the locked table is unlocked, the write operation can be performed immediately), and other tables can also be viewed mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 19 | 1 | | 5 | 2 | | 14 | 4 | | 20 | 4 | | 1 | 5 | | 6 | 5 | | 3 | 6 | | 16 | 7 | | 18 | 7 | | 4 | 8 | | 13 | 9 | | 10 | 10 | | 15 | 12 | | 9 | 13 | | 11 | 13 | | 12 | 14 | | 17 | 17 | | 8 | 18 | | 7 | 19 | | 2 | 20 | +--------+------+ 20 rows in set (0.00 sec)
The table has not been unlocked. session2: blocking
Table 1 session2 after unlocking: execution is completed immediately
Write lock
-- session1 You can read and write locked tables. You can't read other tables mysql> lock table mylock write; Query OK, 0 rows affected (0.00 sec) mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 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
session2:
You can read and write other tables. You can't read or write the table locked by session1. It is blocked and you need to wait for the lock to be released
Case conclusion
Summary: before executing the query statement (select), MyISAM will automatically add read locks to all the tables involved. Before performing the add, delete and modify operations, MyISAM will automatically add write locks to the tables involved.
There are two modes of MySQL table level locks:
- Table Read Lock
- Table Write Lock
| Lock type | Compatible | Read lock | Write lock |
|---|---|---|---|
| Read lock | yes | yes | no |
| Write lock | yes | no | no |
Conclusion:
Combined with the above table, the MyISAM table can be operated as follows:
1. The read operation (adding read lock) on the MyISAM table will not block the read requests of other processes to the same table, but will block the write requests to the same table. Only when the read lock is released will the write operation of other processes be performed.
2. Writing to the MyISAM table (adding a write lock) will block other processes from reading and writing to the same table. Only after the write lock is released will other processes perform the read and write operations.
In short, read locks block writes, but not reads. The write lock blocks both reading and writing.
Row lock
characteristic
Features: it is biased towards InnoDB storage engine, with high overhead and slow locking; Deadlock will occur; The locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest
There are two major differences between InnoDB and MyISAM:
- First, support services
- Second, row level lock is adopted
case analysis
You need to turn off mysql auto submit
SET autocommit=0;
Create table SQL
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB; INSERT INTO test_innodb_lock VALUES(1,'b2'); INSERT INTO test_innodb_lock VALUES(3,'3'); INSERT INTO test_innodb_lock VALUES(4, '4000'); INSERT INTO test_innodb_lock VALUES(5,'5000'); INSERT INTO test_innodb_lock VALUES(6, '6000'); INSERT INTO test_innodb_lock VALUES(7,'7000'); INSERT INTO test_innodb_lock VALUES(8, '8000'); INSERT INTO test_innodb_lock VALUES(9,'9000'); INSERT INTO test_innodb_lock VALUES(1,'b1'); CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a); CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b); set autocommit = 0;
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)
session2:
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)
session1 commit session2commit session2:
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)
Test row lock
1. Operate the same row of data
-- 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 mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4002 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
-- session2 block mysql> update test_innodb_lock set b = 4003 where a =4;
session2 after session1commit: complete the execution immediately
Query OK, 1 row affected (2.94 sec) Rows matched: 1 Changed: 1 Warnings: 0
At this time, the query result of session1 is 4002
mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4002 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
The query result of session2 is 4003
mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4003 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
After session2 commit s and session1 commits, session1 is updated to 4003
2. Operate different rows of data
-- session1 mysql> update test_innodb_lock set b = 4009 where a = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
-- session 2 mysql> update test_innodb_lock set b = 9009 where a =9; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
The modified value can be seen after both sides commit
mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4009 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9009 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
Upgrade table lock without index row lock
-- session1 mysql> update test_innodb_lock set a = 41 where b = 4009; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
-- session2 because b Field is varcahr Type, query statement b=4009 yes int Type, mysql The underlying layer converts it, and the type conversion is performed on the index column, resulting in index invalidation, resulting in row lock upgrading to table lock, so session2 The update statement for is blocked mysql> update test_innodb_lock set b = 9008 where a =9;
session2 after session1commit: complete the execution immediately
Query OK, 1 row affected (3.82 sec) Rows matched: 1 Changed: 1 Warnings: 0
Gap lock hazard
Definition: when we retrieve data with range conditions instead of equality conditions and request shared or exclusive locks, 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". InnoDB will also lock the "GAP". This locking mechanism is the so-called next key lock.
-- session1 Lock 2,3,4,5 Line, even if 2 does not exist mysql> update test_innodb_lock set b = 'test' where a > 1 and a < 6; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
-- session2 Blocked mysql> INSERT INTO test_innodb_lock VALUES(2, '2000');
session2 was successfully inserted after session1commit
Query OK, 1 row affected (6.68 sec)
Case conclusion
Because InnoDB storage engine implements row level locking, although the performance loss caused by the implementation of locking mechanism may be higher than table level locking, it is much better than table level locking of MyISAM in terms of overall concurrent processing capacity. When the system concurrency is high, the overall performance of InnoDB will have obvious advantages over MyISAM.
Secondly, the row level lock of InnoDB must be used properly, or it will backfire, which will make the overall performance of InnoDB worse than MyISAM.
Optimization suggestions
- As far as possible, all data retrieval should be completed through the index to avoid upgrading the non indexed row lock to a table lock
- Reasonably design the index to minimize the scope of the lock
- Search conditions shall be as few as possible to avoid gap lock
- Try to control the transaction size and reduce the amount of locking resources and time length
- Low level transaction isolation as possible
Page lock
The overhead and locking time are between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrency is general.
Changed: 3 Warnings: 0
```sql -- session2 Blocked mysql> INSERT INTO test_innodb_lock VALUES(2, '2000');
session2 was successfully inserted after session1commit
Query OK, 1 row affected (6.68 sec)
Case conclusion
Because InnoDB storage engine implements row level locking, although the performance loss caused by the implementation of locking mechanism may be higher than table level locking, it is much better than table level locking of MyISAM in terms of overall concurrent processing capacity. When the system concurrency is high, the overall performance of InnoDB will have obvious advantages over MyISAM.
Secondly, the row level lock of InnoDB must be used properly, or it will backfire, which will make the overall performance of InnoDB worse than MyISAM.
Optimization suggestions
- As far as possible, all data retrieval should be completed through the index to avoid upgrading the non indexed row lock to a table lock
- Reasonably design the index to minimize the scope of the lock
- Search conditions shall be as few as possible to avoid gap lock
- Try to control the transaction size and reduce the amount of locking resources and time length
- Low level transaction isolation as possible
Page lock
The overhead and locking time are between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrency is general.