1, Introduction to mysql lock
Lock source
When there are concurrent operations, a mechanism is necessary to ensure the integrity and consistency of data.
Lock is the realization of this technology.
Lock type
According to the concept, it is divided into pessimistic lock and optimistic lock
According to particle size:
Table lock, page lock and row lock are the most common.
Among them, MyISAM engine only has table locks, while InooDB has both table locks and row locks.
According to function:
Shared lock, exclusive lock (exclusive lock), intent lock, etc.
Among them, the shared lock is called S lock. Exclusive locks are called X locks.
| Lock name | characteristic |
|---|---|
| Watch lock | Fast locking, no deadlock, large locking granularity, the highest probability of lock conflict and the lowest concurrency. |
| Row lock | High overhead and low probability of lock conflict. High concurrency will cause deadlock. |
| Page lock | The overhead, locking time and locking granularity are bounded between table locks and row locks, resulting in deadlock and general concurrency. |
Mind map list
Various locks of MySQL may be difficult to understand. Before understanding, you must have a mind map. Which lock belongs to which engine and which lock belongs to which lock. You must distinguish between large and small classes, so that it will not be too difficult to learn.
2, MyISAM table lock
Let's first look at the compatibility of read lock and write lock:
| Current lock mode / compatible / request lock mode | Read lock | Write lock |
|---|---|---|
| Read lock | yes | no |
| Write lock | yes | no |
MySQL test version | 5.7.26
Table engine: sys_ admin_ The log table is MyISAM, test_ The users table is InnoDB.
sys_admin_log operation log MyISAM table
CREATE TABLE `sys_admin_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`admin_name` varchar(255) DEFAULT NULL COMMENT 'account number',
`created_at` int(12) DEFAULT NULL COMMENT 'Operation time',
`ip` varchar(200) CHARACTER SET utf8 DEFAULT NULL COMMENT 'ip',
`content` text COMMENT 'journal',
`admin_id` int(11) DEFAULT NULL COMMENT 'account number id',
`path` varchar(255) DEFAULT NULL COMMENT 'Operational routing',
`method` varchar(255) DEFAULT NULL COMMENT 'Operation method',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='Operation log table';
INSERT INTO `sys_admin_log` (`id`, `admin_name`, `created_at`, `ip`, `content`, `admin_id`, `path`, `method`) VALUES ('1', 'admin_name', '1632898006', '127.0.0.1', '{\"s\":\"\\/api\\/index\"}', '1', 'api/index', 'GET');
INSERT INTO `sys_admin_log` (`id`, `admin_name`, `created_at`, `ip`, `content`, `admin_id`, `path`, `method`) VALUES ('2', 'admin_name', '1632898498', '127.0.0.1', '{\"s\":\"\\/api\\/index\"}', '1', 'api/index', 'GET');
test_users InnoDB table
CREATE TABLE `test_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT '',
`email` varchar(255) DEFAULT '',
`password` varchar(255) DEFAULT '',
`pid` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1800010 DEFAULT CHARSET=utf8;
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800001', 'kana.sasaki', '1936902877maaya70@kato.net', '\"\\BvW[h]\'b~zV`nl', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800002', 'fyamaguchi', '608386635sasada.satomi@matsumoto.com', '.Wh4_}', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800003', 'nanami.sakamoto', '1302522125kyosuke37@hamada.jp', '3A)J&SqtrbxDiMU7S', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800004', 'kanou.nanami', '317175880yuta34@uno.com', 'xKB1nz~+/J#FserC', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800005', 'chiyo93', '35942393zkanou@murayama.jp', 'M&5M]B~hg!p', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800006', 'uyamaguchi', '1852957470kondo.kaori@gmail.com', 'n*P*H`0[', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800007', 'akira.sato', '1568165671hiroshi23@sato.jp', 'wQ_vE4[t\"v', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800008', 'atsushi06', '75486597zishida@kudo.net', 'FYw\")QWG*', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800009', 'shota40', '399696901kimura.tsubasa@yahoo.co.jp', '3uJ.u.7.<i?', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800010', 'ogaki.yuta', '96819331asuka82@kudo.jp', '.=&~}7_]bI<', '0');
INSERT INTO `test_users` (`id`, `name`, `email`, `password`, `pid`) VALUES ('800011', 'koizumi.sayuri', '1457611112akira.aota@aoyama.jp', '+_2g|fCy}DuR~lU0en', '0');
1. Read lock
Syntax: lock table tablename read
Session 1: to sys_ admin_ If you lock the log table, you cannot test_users and other tables can only read the locked table.
mysql> lock table sys_admin_log read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_admin_log;
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
| id | admin_name | created_at | ip | content | admin_id | path | method |
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
| 1 | admin_name | 1632898006 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
| 2 | admin_name | 1632898498 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
2 rows in set (0.00 sec)
mysql> select * from test_users limit 0,2;
ERROR 1100 (HY000): Table 'test_users' was not locked with LOCK TABLES
mysql>
mysql> delete from test_users where id = 2;
ERROR 1100 (HY000): Table 'test_users' was not locked with LOCK TABLES
mysql>
Session 2: sys in session 1_ admin_ Log table locking does not affect session 2's query of locked tables and other tables.
mysql> select * from sys_admin_log;
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
| id | admin_name | created_at | ip | content | admin_id | path | method |
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
| 1 | admin_name | 1632898006 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
| 2 | admin_name | 1632898498 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
2 rows in set (0.00 sec)
Session 2:
At this time, although session 1 is for sys_admin_log is locked,
However, session 2 can still access sys_admin_log table is locked.
However, with the table lock, session 2, like session 1, can only query.
mysql> lock table sys_admin_log read; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users; ERROR 1100 (HY000): Table 'test_users' was not locked with LOCK TABLES mysql> mysql> UPDATE `sys_admin_log` SET `admin_name`='admin_name5' WHERE (`id`='1'); ERROR 1099 (HY000): Table 'sys_admin_log' was locked with a READ lock and can't be updated mysql>
Conclusion 1: after the table read lock is executed, the current session can only access the locked table, not the unlocked table, but the non current session is not affected.
Session 1: to sys_admin_log table is locked and the data with id 1 is deleted.
mysql> lock table sys_admin_log read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_admin_log;
+----+-------------+------------+-----------+----------------------+----------+-----------+--------+
| id | admin_name | created_at | ip | content | admin_id | path | method |
+----+-------------+------------+-----------+----------------------+----------+-----------+--------+
| 1 | admin_name5 | 1632898006 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
| 2 | admin_name | 1632898498 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
+----+-------------+------------+-----------+----------------------+----------+-----------+--------+
2 rows in set (0.00 sec)
mysql> delete from sys_admin_log where id = 1;
ERROR 1099 (HY000): Table 'sys_admin_log' was locked with a READ lock and can't be updated
mysql>
Session 2: to sys_ admin_ Delete a row of data in the log table, but it will cause blocking.
mysql> delete from classroom where id = 3; ......wait for
Conclusion 2:
After the table read lock is executed, the current session can only perform query operations and cannot perform other operations (update, delete, etc.). Non current session can perform other operations, but it will cause blocking.
2. Write lock
Syntax: lock table tablename write
Session 1: Session 1 holds sys_admin_log table read lock. Other sessions can hold the read lock of the table, but cannot hold the write lock of the table.
mysql> lock table sys_admin_log read; Query OK, 0 rows affected (0.00 sec)
Session 2: Session 2 can hold sys_admin_log, but cannot hold the table write lock, which will cause blocking.
mysql> lock table classroom read; Query OK, 0 rows affected (0.00 sec) mysql> lock table classroom write; ......wait for
Conclusion 1: when one session holds a table read lock, other sessions can hold a table read lock, but cannot hold a table write lock.
Session 1: transaction 1 to sys_admin_log with table lock, only transaction 1 can add, delete, modify and query.
mysql> lock table sys_admin_log write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sys_admin_log;
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
| id | admin_name | created_at | ip | content | admin_id | path | method |
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
| 3 | admin_name | 1632898498 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | NULL | NULL |
| 2 | admin_name | 1632898498 | 127.0.0.1 | {"s":"\/api\/index"} | 1 | api/index | GET |
+----+------------+------------+-----------+----------------------+----------+-----------+--------+
2 rows in set (0.00 sec)
mysql>
Session 2: after session 1 adds a write lock, session 2 cannot operate on this table, but can operate on other tables.
mysql> select * from sys_admin_log; Ctrl-C -- sending "KILL QUERY 214" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted mysql> select * from test_users limit 0,2; +--------+-------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+-------------+--------------------------------------+------------------+------+ | 800001 | kana.sasaki | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+-------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql> update test_users set name = "willem" where id = 800001; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | willem | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec)
Conclusion 2: when a session holds a table write lock, the session can only add, delete, modify and query the table. Other sessions cannot perform all operations on the table. However, it does not affect other sessions to operate on other tables.
3. Summary
| \ | Table read lock | Table write lock |
|---|---|---|
| When a transaction has a table read / write lock, can other transactions curd the table | It can be checked but not added, deleted or modified | This query can be added or deleted |
| When a transaction already holds a table read lock, can other transactions continue to hold a table read / write lock | Can read the lock in the holding table | Cannot hold table write lock |
| When a transaction already holds a table write lock, can other transactions continue to hold a table read / write lock | Cannot hold table read lock | Cannot hold table write lock |
| When a transaction has a table read / write lock, can the transaction operate on other tables | No | No |
| #2, InnoDB table lock (intent lock): |
Meaning of intention lock (Baidu Encyclopedia)
Intention lock means that if an intention lock is added to a node, it means that the lower node of the node is being locked; When locking any node, you must first add an intention lock to its upper node.
The intent lock is maintained by the data engine itself, and the user cannot intervene manually. Before adding row level exclusive lock or shared lock, InooDB will judge whether there is a corresponding intent lock in the data table of the data row.
InooDB holds row locks. MyISAM does not have row locks. Since there are row locks, it is necessary to understand the compatibility between InooDB downlink locks and table locks.
Let's do an experiment:
Session 1: to test_ A row of data in the users table was locked and not submitted
mysql> select * from test_users limit 0,3; +--------+-----------------+--------------------------------------+-------------------+------+ | id | name | email | password | pid | +--------+-----------------+--------------------------------------+-------------------+------+ | 800001 | willem | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | | 800003 | nanami.sakamoto | 1302522125kyosuke37@hamada.jp | 3A)J&SqtrbxDiMU7S | 0 | +--------+-----------------+--------------------------------------+-------------------+------+ 3 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 800001 lock in share mode; +--------+--------+----------------------------+------------------+------+ | id | name | email | password | pid | +--------+--------+----------------------------+------------------+------+ | 800001 | willem | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | +--------+--------+----------------------------+------------------+------+ 1 row in set (0.00 sec) mysql>
Session 2: to test_ Add a table level shared lock to the users table to see if it will block? Discovery can be added.
mysql> lock table test_users read; Query OK, 0 rows affected (0.00 sec)
Conversation 2: since the table level shared lock cannot be added, can the table level exclusive lock be added? Blocked
mysql> lock table student write; ......
Conclusion 1: when a session holds a row level shared lock of a table, other sessions can obtain the table level shared lock of the table, but cannot obtain the table level exclusive lock of the table.
Continue with the second experiment:
Session 1: to test_ A row of data in the users table has an exclusive lock and is not submitted
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 800005 for update; +--------+---------+----------------------------+-------------+------+ | id | name | email | password | pid | +--------+---------+----------------------------+-------------+------+ | 800005 | chiyo93 | 35942393zkanou@murayama.jp | M&5M]B~hg!p | 0 | +--------+---------+----------------------------+-------------+------+ 1 row in set (0.00 sec) mysql>
Session 2: window 2 applies for test_ Table level shared lock for the users table. There was a blockage.
mysql> lock table test_users read; ......
Session 2: will it be blocked to apply for an exclusive lock? I found it blocked, too
mysql> lock table test_users write; ......
Conclusion 2: when a session holds row level exclusive locks of a table, other sessions cannot obtain table level exclusive locks and shared locks.
The above examples are all about adding row locks first and then table locks. Let's turn it over and have a try. Let's see how to add table lock first and row lock first.
Look at the third experiment:
Session 1: to test_users table with table read lock.
mysql> select * from test_users; +----+--------------+-----+ | id | name | age | +----+--------------+-----+ | 1 | Bald head strength | 24 | | 5 | King Gigi | 40 | | 10 | Snow White | 26 | +----+--------------+-----+ 3 rows in set (0.00 sec) mysql> lock table test_users read; Query OK, 0 rows affected (0.00 sec)
Conversation 2: I went to see if I could write a lock on the application line. I found that it was blocked.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 1 for update; ......
Conversation 2: let's see if we can apply for row read lock? It is found that you can apply
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 1 lock in share mode; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 1 | Bald head strength | 24 | +----+-----------+-----+ 1 row in set (0.00 sec)
Conclusion 3: when a session holds a table read lock of a table, other session transactions can obtain row level read locks, but obtaining row level write locks will be blocked.
Let's look at the fourth experiment:
Session 1: to test_users table with table read lock
mysql> select * from test_users; +----+--------------+-----+ | id | name | age | +----+--------------+-----+ | 1 | Bald head strength | 24 | | 5 | King Gigi | 40 | | 10 | Snow White | 26 | +----+--------------+-----+ 3 rows in set (0.00 sec) mysql> lock table test_users write; Query OK, 0 rows affected (0.00 sec)
Session 2: to test_ The users table adds a row of data and applies for a row write lock. It is found that it will be blocked
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 1 for update;
Session 2: to test_ A row of data in the users table applies for a row read lock, which is still blocked.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 1 lock in share mode; ......
Conclusion 4: when a session holds the table write lock of a table, other transactions cannot obtain the row level read-write lock of the table.
summary
Then the above chestnuts can explain:
| \ | Intent shared lock (IS) | Intent exclusive lock (IX) |
|---|---|---|
| Table level shared lock | compatible | mutex |
| Table level exclusive lock (X) | mutex | mutex |
After understanding the compatibility of table level and row level, it will be better to understand the intention lock.
Intent lock is a table lock!
When we need to add a table lock to a table, we need to judge whether any data rows in the table are locked according to the intention lock to determine whether it can be added successfully. If the intent lock is a row lock, we have to traverse all the data rows in the table to judge. If the intent lock is a table lock, we can judge directly once to know whether any data rows in the table are locked.
Attention! Attention!!!
There is a pit here. My tests above are MySQL 5.7, but under MySQL 5.7, after adding row write lock, I can add table read lock. Different versions lead to different results. I haven't found the answer from the Internet for several days, which once made me doubt my life. Remember this
3, InnoDB row lock
Note that row locks in InnoDB need to be run in transactions to take effect.
1. S hared lock
Syntax: lock in share mode
Concept:
Also known as read lock, it adds a shared lock to a resource. It can modify or read the resource itself. Others can continue to hold the shared lock of the resource and cannot hold the exclusive lock of the resource.
It can only be read, and no other operations can be performed.
Session 1: to test_ Add a shared lock to the data with the users table id of 6.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | willem | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386da.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql> select * from test_users where id = 800001 lock in share mode; +--------+--------+----------------------------+------------------+------+ | id | name | email | password | pid | +--------+--------+----------------------------+------------------+------+ | 800001 | willem | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | +--------+--------+----------------------------+------------------+------+ 1 row in set (0.00 sec) mysql>
Session 2: modifying this data will always cause blocking until timeout or lock release.
mysql> update test_users set name = "tian" where id = 800001; ...wait for
Conclusion 1: a session adds a shared lock to a row in a table. Other sessions can't do other operations until the lock is released.
Session 1: to test_ Add a shared lock to the data with user table id 6 to see if the current session can operate on the data in other tables?
mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users where id = 800001 lock in share mode; +--------+------+----------------------------+------------------+------+ | id | name | email | password | pid | +--------+------+----------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | +--------+------+----------------------------+------------------+------+ 1 row in set (0.00 sec) mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql> update test_users set name = "share" where id = 800001; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | share | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql>
Conclusion 2:
A session adds a shared lock to a row in a table, which does not affect the session's operation of other tables and its own tables, which is different from table lock (table lock is that after the current session adds a table lock to the table, the current session can only operate the data in the table, and cannot operate the data in other tables)
Session 1: start the transaction and give it to test_ The with id 6 in the users table is locked.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql> select * from test_users where id = 800001 lock in share mode; +--------+------+----------------------------+------------------+------+ | id | name | email | password | pid | +--------+------+----------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | +--------+------+----------------------------+------------------+------+ 1 row in set (0.00 sec) mysql>
Session 2: also give test_ The with id 6 in the users table is locked.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_users limit 0,2; +--------+------------+--------------------------------------+------------------+------+ | id | name | email | password | pid | +--------+------------+--------------------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | | 800002 | fyamaguchi | 608386635sasada.satomi@matsumoto.com | .Wh4_} | 0 | +--------+------------+--------------------------------------+------------------+------+ 2 rows in set (0.00 sec) mysql> select * from test_users where id = 800001 lock in share mode; +--------+------+----------------------------+------------------+------+ | id | name | email | password | pid | +--------+------+----------------------------+------------------+------+ | 800001 | tian | 1936902877maaya70@kato.net | "\BvW[h]'b~zV`nl | 0 | +--------+------+----------------------------+------------------+------+ 1 row in set (0.00 sec) mysql>
Session 1: modifying this data will cause blocking.
mysql> update test_users set name = 'Li Beijing' where id = 6; ......wait for
Session 2: a deadlock occurs when the data is modified.
mysql> update test_users set name = 'Li Beijing' where id = 6; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Conclusion 3: when one session holds the shared lock of a row, other sessions can also hold the shared lock of a row. However, if both sessions modify this data at the same time, it will cause deadlock.
2. Exclusive lock (X lock)
Syntax: for update
Concept: also known as write lock, it adds an exclusive lock to a resource. It can modify or read the resource itself. Other sessions cannot continue to hold the shared lock and exclusive lock of the resource. It can only read the locked data and cannot perform other operations.
Prerequisite for application of exclusive lock:
No thread uses an exclusive lock or shared lock on any row data in the result set, otherwise the application will be blocked.
for update and lock in share mode are only applicable to InnoDB and can only take effect in the transaction block (BEGIN/COMMIT). During transaction operation, MySQL will add an exclusive lock to each row of data in the query result set through the for update statement, and other threads will block the update and deletion of the record. The exclusive lock includes row lock and table lock
Row exclusive locks do not mean that other transactions cannot query the row data after adding them, but other transactions cannot add other locks to the row. mysql InnoDB engine will automatically add exclusive locks to the data involved by modifying data statements, update, delete and insert by default. select statements will not add any lock types by default, whether row shared locks or row exclusive locks Enough for query, because ordinary query does not have any locking mechanism.
Session 1: apply an exclusive lock to the data with student table id 6.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from student; +----+-----------+-------+------+ | id | name | price | cid | +----+-----------+-------+------+ | 6 | Li Beijing | 50 | 6,2 | | 7 | Shanghai Wang | 200 | 2 | +----+-----------+-------+------+ 2 rows in set (0.00 sec) mysql> select * from student where id = 6 for update; +----+-----------+-------+------+ | id | name | price | cid | +----+-----------+-------+------+ | 6 | Li Beijing | 50 | 6,2 | +----+-----------+-------+------+ 1 row in set (0.00 sec)
Session 2: can session 2 read the locked data after session 1 is locked exclusively?
The answer is yes!
mysql> select * from student where id = 6; +----+-----------+-------+------+ | id | name | price | cid | +----+-----------+-------+------+ | 6 | Li Beijing | 50 | 6,2 | +----+-----------+-------+------+ 1 row in set (0.00 sec)
Session 2: after session 1 adds an exclusive lock, can session 2 continue to hold the exclusive lock of the row?
The answer is blocked.
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 6 for update; ......wait for
Session 2: after session 1 adds an exclusive lock, can session 2 still hold the shared lock of the row? It will also be blocked.
mysql> select * from student where id = 6 lock in share mode; ......wait for
Session 2: can session 2 still modify this data after session 1 adds an exclusive lock? The answer is no! It will cause the lock to wait until the resource is released.
mysql> update student set name = 'Wu Beijing' where id = 6; ......wait for
Conclusion 1: when one session holds the exclusive lock of a row, other sessions cannot modify data and hold the shared lock and exclusive lock of line change, which will cause blocking.
3. Summary
| \ | Row read lock | Row write lock |
|---|---|---|
| After the uplink read / write lock is added, can other transactions delete this data | Will block | Will block |
| After the uplink read / write lock is added, can other transactions read this data | can | can |
| After the uplink read lock is added, can other transactions hold the row / read lock | can | No |
| After the uplink write lock is added, can other transactions hold the row / read lock | No | No |
4, Lock algorithm (row lock)
1. Record lock
Window 1: directly lock the record with id 1
mysql> select * from student; +----+--------+ | id | name | +----+--------+ | 1 | Xiao Wang | | 2 | Lee | | 3 | Zhang | +----+--------+ 3 rows in set (0.00 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1 for update; +----+--------+ | id | name | +----+--------+ | 1 | Xiao Wang | +----+--------+ 1 row in set (0.00 sec)
Window 2:
The record with id 1 cannot be repaired, locked, added, deleted or modified, but it can be found
mysql> select * from student where id = 1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2. Clearance lock
Gap Lock is a locking mechanism introduced by Innodb to solve the unreal reading problem under repeatable read submission.
3. Key lock
Critical key lock is a combination of record lock and gap lock. Its blocking range includes both index records and index intervals to solve the problem of phantom reading.