如何理解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表锁，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。