如何理解MySQL 5.5 InnoDB表鎖

167次閱讀

共計 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 行業資訊頻道。

正文完