纸上得来终觉浅,绝知此事要躬行。 InnoDB行锁 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。 InnoDB 实现了以下两种类型的行锁。 共享锁(S):又称为读锁,简称S锁,共享锁就是多个
纸上得来终觉浅,绝知此事要躬行。
开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
InnoDB 实现了以下两种类型的行锁。
可以通过以下语句显示给记录集加共享锁或排他锁 。
共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(X) :SELECT * FROM table_name WHERE ... FOR UPDATE
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁;
在测试前仍然准备相关测试表和数据,注意表的存储引擎为InnoDB:
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(11,"100","1");
insert into test_innodb_lock values(3,"3","1");
insert into test_innodb_lock values(4,"400","0");
insert into test_innodb_lock values(5,"500","1");
insert into test_innodb_lock values(6,"600","0");
insert into test_innodb_lock values(7,"700","0");
insert into test_innodb_lock values(8,"800","1");
insert into test_innodb_lock values(9,"900","1");
insert into test_innodb_lock values(1,"200","0");
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
创建完成,我们看一下表结构和表数据,方便后面操作查看:
mysql> desc test_innodb_lock;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(16) | YES | MUL | NULL | |
| sex | varchar(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from test_innodb_lock;
+------+------+------+
| id | name | sex |
+------+------+------+
| 11 | 100 | 1 |
| 3 | 3 | 1 |
| 4 | 400 | 0 |
| 5 | 500 | 1 |
| 6 | 600 | 0 |
| 7 | 700 | 0 |
| 8 | 800 | 1 |
| 9 | 900 | 1 |
| 1 | 200 | 0 |
+------+------+------+
9 rows in set (0.00 sec)
Session-1 | Session-2 |
---|---|
关闭自动提交功能 | 关闭自动提交功能 |
可以正常的查询出全部的数据 | 可以正常的查询出全部的数据 |
查询 id 为 3 的数据 | 查询 id 为 3 的数据 |
更新 id 为 3 的数据,但是不提交 | 更新id为3 的数据, 处于等待状态 |
通过 commit, 提交事务 | 解除阻塞,更新正常进行 |
以上, 操作的都是同一行的数据,接下来,演示不同行的数据 : | |
更新 id 为 3 数据,正常的获取到行锁 , 执行更新 ; | 由于与Session-1 操作不是同一行,获取当前行锁,执行更新; |
如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。
查看当前表的索引 :
show index from test_innodb_lock ;
接下来我们看一个例子,当索引失效的时候,行锁升级为表锁的过程:
Session-1 | Session-2 |
---|---|
关闭事务的自动提交 | 关闭事务的自动提交 |
执行更新语句 | 执行更新语句, 但处于阻塞状态 |
提交事务 | 解除阻塞,执行更新成功 |
执行提交操作 |
以上两个客户端对一行数据进行修改时,由于执行更新时,name
字段本来为varchar
类型, 我们是作为int
类型使用,存在类型转换,索引失效,最终行锁变为表锁,导致Session-2更新数据处于等待状态。
当我们用范围条件,而不是使用相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据进行加锁; 对于键值在条件范围内但并不存在的记录,叫做 "间隙(GAP)" , InnoDB也会对这个 "间隙" 加锁,这种锁机制就是所谓的间隙锁(Next-Key锁) 。
下面是一个间隙锁的案例:
Session-1 | Session-2 |
---|---|
关闭事务自动提交 | 关闭事务自动提交 |
根据 id 范围更新数据 | |
插入id为2的记录, 出于阻塞状态 | |
提交事务 | |
解除阻塞 , 执行插入操作 | |
提交事务 |
由于Session-1的插入操作是一个范围,我们表中存在id
不连续的情况,导致在更新时不仅会加排它锁,还会在当前访问增加间隙锁,导致Session-2在插入元素时处于等待状态。
查看行锁竞争情况:
show status like "innodb_row_lock%";
InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力较高。但是,InnoDB的行级锁也是一把双刃剑,当我们使用不当的时候,可能会让InnoDB的整体性能下降。
优化建议:
--结束END--
本文标题: 【MySQL】锁之InnoDB
本文链接: https://www.lsjlt.com/news/6578.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
2024-05-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0