加入收藏 | 设为首页 | 会员中心 | 我要投稿 银川站长网 (https://www.0951zz.com/)- 云通信、基础存储、云上网络、机器学习、视觉智能!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

如何了解MySQL 5.5 InnoDB表锁

发布时间:2023-07-28 13:17:33 所属栏目:MySql教程 来源:
导读:本篇文章为大家展示了如何理解MySQL 5.5 InnoDB表锁,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。对于没有索引的表,MySQL会使用表级锁,写操作不会阻塞读操作,读操作

本篇文章为大家展示了如何理解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表锁,你们学到知识或技能了吗?

(编辑:银川站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章