系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

【MySQL】锁之InnoDB

时间:2020-06-11来源:www.pcxitongcheng.com作者:电脑系统城

纸上得来终觉浅,绝知此事要躬行。

InnoDB行锁#

开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

InnoDB 实现了以下两种类型的行锁。

  • 共享锁(S):又称为读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
  • 排他锁(X):又称为写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁(包括共享锁和排他锁),但是获取排他锁的事务是可以对数据就行读取和修改。

可以通过以下语句显示给记录集加共享锁或排他锁 。


 
Copy
共享锁(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:


 
Copy
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);

创建完成,我们看一下表结构和表数据,方便后面操作查看:


 
Copy
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将对表中的所有记录加锁,实际效果跟表锁一样。

查看当前表的索引 :


 
Copy
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在插入元素时处于等待状态。

InnoDB 行锁争用情况#

查看行锁竞争情况:


 
Copy
show status like 'innodb_row_lock%';

  • Innodb_row_lock_current_waits: 当前正在等待锁定的数量
  • Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
  • Innodb_row_lock_time_avg:每次等待所花平均时长
  • Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
  • Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

总结#

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力较高。但是,InnoDB的行级锁也是一把双刃剑,当我们使用不当的时候,可能会让InnoDB的整体性能下降。

优化建议:

  • 尽可能通过索引来完成数据操作,避免无索引行锁升级为表锁。
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能减少索引条件,及索引范围,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离(但是需要业务层面满足需求)
分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载