如何理解MySQL 5.5 InnoDB表鎖

本篇文章為大家展示了如何理解MySQL 5.5 InnoDB表鎖，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

對于沒有索引的表，MySQL會使用表級鎖，寫操作不會阻塞讀操作，讀操作不會阻塞寫操作；一個會話的寫操作會對整張表加鎖，其他會話想修改表需要等到這個會話提交或回滾事務。

會話①
mysql> create table t12(id tinyint(3) unsigned not null,
    ->       name varchar(10) not null)
    ->       engine=innodb auto_increment=8 default charset=gbk;
Query OK, 0 rows affected (0.12 sec)

mysql> show keys from t12;
Empty set (0.00 sec)

mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql> insert into t12 values(10,'Neo');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t12;
+----+------+
| id | name |
+----+------+
| 10 | Neo  |
+----+------+
1 row in set (0.00 sec)

會話②
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

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

mysql> select * from t12;
+----+------+
| id | name |
+----+------+
| 10 | Neo  |
+----+------+
1 row in set (0.00 sec)

會話①
mysql> update t12 set name='trinity' where id=10;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

會話②
下面DML語句會一直阻塞
mysql> insert into t12 values(20,'Trinity');

過一段時間會出現超時提示
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

會話①
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

會話②
mysql> insert into t12 values(20,'Trinity');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

對于有索引的表，寫操作不會阻塞讀操作，讀操作不會阻塞寫操作；如果在MySQL在寫操作時使用索引掃描，則會使用行級鎖，一個會話的寫操作會對修改的行加鎖，其他會話想修改這些行需要等到這個會話提交或回滾事務，其他會話對其他行的寫操作不受影響，行鎖會阻塞表鎖；如果MySQL使用全表掃描，則會使用表級鎖，一個會話的寫操作會對整張表加鎖，其他會話想修改表需要等到這個會話提交或回滾事務，表鎖會阻塞行鎖。

會話①
mysql> create index idx_t12_id on t12(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引
mysql> show keys from t12;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t12   |          1 | idx_t12_id |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

查看執行計劃
mysql> explain select * from t12 where id=20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t12   | ALL  | idx_t12_id    | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

mysql> delete from t12 where id=20;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t12;
+----+------+
| id | name |
+----+------+
| 10 | Neo  |
+----+------+
1 row in set (0.00 sec)

會話②

查看執行計劃
mysql> explain select * from t12 where id=10;
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | t12   | ref  | idx_t12_id    | idx_t12_id | 1       | const |    1 |       |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
1 row in set (0.00 sec)

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

mysql> select * from t12;
+----+---------+
| id | name    |
+----+---------+
| 10 | Jack    |
| 20 | Trinity |
| 20 | Trinity |
+----+---------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| Id | User            | Host      | db   | Command | Time   | State                                                                       | Info             |
+----+-----------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
|  1 | system user     |           | NULL | Connect | 769140 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  2 | system user     |           | NULL | Connect | 769141 | Connecting to master                                                        | NULL             |
| 13 | event_scheduler | localhost | NULL | Daemon  | 621090 | Waiting on empty queue                                                      | NULL             |
| 76 | neo             | localhost | fire | Sleep   |    180 |                                                                             | NULL             |
| 78 | neo             | localhost | fire | Query   |      0 | NULL                                                                        | show processlist |
+----+-----------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
5 rows in set (0.00 sec)

mysql> explain select * from t12;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t12   | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

下面的更新沒有使用索引而使用全表掃描，這樣會加表級鎖，會處于阻塞狀態。
mysql> update t12 set name='Jack';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

會話①

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

會話②
隨著會話①的回滾操作，會話②執行成功
mysql> update t12 set name='Jack';
Query OK, 3 rows affected (12.41 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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

會話①
mysql> update t12 set name='Jack';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

會話②
插入操作會一直處于阻塞狀態
mysql> insert into t12 values(30,'Lily');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

會話①
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

會話②
mysql> insert into t12 values(30,'Lily');
Query OK, 1 row affected (0.09 sec)

兩行數據使用了同一個索引，對兩個不同的行加鎖，也會引起鎖等待
mysql> show create table tab_with_index\G
*************************** 1. row ***************************
       Table: tab_with_index
Create Table: CREATE TABLE `tab_with_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from tab_with_index where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
+------+------+
2 rows in set (0.00 sec)

mysql> show keys from tab_with_index;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tab_with_index |          1 | id       |            1 | id          | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

會話①
使用where id=1 and name='1'條件進行查詢
mysql> select * from tab_with_index where id=1 and name='1' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

會話②
查詢where id=1 and name='4'條件進行查詢，由于和會話①使用了相同的索引，即使查詢了不同的字段，也會引起鎖等待
mysql> select * from tab_with_index where id=1 and name='4' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在這種情況下，可以考慮創建聯合索引
會話①
mysql> create index idx_id_name on tab_with_index(id,name);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tab_with_index where id=1 and name='4' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 4    |
+------+------+
1 row in set (0.00 sec)

會話②
mysql> select * from tab_with_index where id=1 and name='1' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

InnoDB存儲引擎的表使用不同索引的阻塞例子

會話①
mysql> show create table tab_with_index\G
*************************** 1. row ***************************
       Table: tab_with_index
Create Table: CREATE TABLE `tab_with_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  KEY `id` (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> desc tab_with_index;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from tab_with_index;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
|    1 | 4    |
+------+------+
5 rows in set (0.00 sec)

mysql> explain select * from tab_with_index where id=1 for update;
+----+-------------+----------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | tab_with_index | ref  | id            | id   | 5       | const |    2 | NULL  |
+----+-------------+----------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> select * from tab_with_index where id=1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tab_with_index where id=1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
+------+------+
2 rows in set (0.01 sec)

會話②
mysql> explain select * from tab_with_index where name='4' for update;
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab_with_index | ref  | idx_name      | idx_name | 13      | const |    2 | Using index condition |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from tab_with_index where name='4' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上述內容就是如何理解MySQL 5.5 InnoDB表鎖，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。