本篇文章为大家展示了如何理解MySQL 5.5 InnoDB表锁，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
对于没有索引的表，MySQL会使用表级锁，写操作不会阻塞读操作，读操作不会阻塞写操作；一个会话的写操作会对整张表加锁，其他会话想修改表需要等到这个会话提交或回滚事务。
会话
关系型数据库创建表t12(id tinyint(3)无符号不为null，
-名称varchar(10)不为空)
-发动机=innodb auto_increment=8默认字符集=gbk
查询正常，0行受影响(0.12秒)
关系型数据库显示t12的密钥；
空集(0.00秒)
关系型数据库显示像%提交%这样的变量；
- -
|变量名|值|
- -
|自动提交|开|
| innodb_commit_concurrency | 0 |
| innodb _ flush _ log _ at _ Trx _ commit | 1 |
- -
3行一组(0.00秒)
关系型数据库设置自动提交=0;
查询正常，0行受影响(0.00秒)
关系型数据库显示像%提交%这样的变量；
- -
|变量名|值|
- -
|自动提交|关闭|
| innodb_commit_concurrency | 0 |
| innodb _ flush _ log _ at _ Trx _ commit | 1 |
- -
3行一组(0.00秒)
关系型数据库插入t12值(10，‘Neo’)；
查询正常，1行受影响(0.00秒)
关系型数据库提交；
查询正常，0行受影响(0.00秒)
关系型数据库从t12中选择*;
- -
| id |名称|
- -
| 10 | Neo |
- -
一行一组(0.00秒)
会话
关系型数据库显示像%提交%这样的变量；
- -
|变量名称
nbsp;| 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表锁，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/96674.html