如何理解MySQL 5.5 InnoDB表锁

130次阅读

共计 11302 个字符，预计需要花费 29 分钟才能阅读完成。

本篇文章为大家展示了如何理解 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 set autocommit=0;
Query OK, 0 rows affected (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 set autocommit=0;
Query OK, 0 rows affected (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 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 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)

会话①
使用 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 select * from tab_with_index where name= 4 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上述内容就是如何理解 MySQL 5.5 InnoDB 表锁，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。

正文完