mysql 没有 innodb_trx_Mysql Innodb 中的锁_于晒瑞的博客-程序员宅基地

技术标签: mysql 没有 innodb_trx  

本文主要涉及以下三个部分:

1. 为什么要加锁

2. 锁的分类

3. 常见语句的加锁分析

4. 如何分析死锁

5. 如何预防死锁

先列出我本地的运行环境

数据库版本是5.7,隔离级别是Repeatable-Read(可重复读),不同的数据库版本和隔离级别对语句的执行结果影响很大。讨论锁的时候不指明版本和隔离级别,都是耍流氓。

一、为什么要加锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

锁是用于管理对公共资源的并发控制。也就是说并发的情况下,会出现资源竞争,所以需要加锁。

举个例子,转账操作。简单来说,张三给李四转账x元,可以分为三步:

1,先查询张三的账户余额y是否大于x

2,张三的余额 y = y - x元

3,李四的余额 x = z + x元

假设张三账户余额有1000元,李四余额也有1000元,如果不加锁的话,同时有两个请求,A要求转500元,B要求转600元,第一步查询余额都是足够的,第2步和第3步也能执行成功,但是最终结果却是错误,第二个请求可能会覆盖掉第一个请求。

这种问题叫做 丢失更新: 多个事务操作同一行,后面的更新覆盖了前面的更新值。需要在应用级别加锁来避免。

数据库有ACID原则,其中I是隔离性,标准SQL规范中定义了四种隔离级别:

越往下,隔离级别越高,问题越少,同时并发度也越低。隔离级别和并发度成反比的。脏读:事务A读取了事务B未提交的数据

不可重复读:对于一条记录,事务A两次读取的数据变了

幻读:事务A按照相同的查询条件,读取到了新增的数据

MySQL中的隔离级别如下:

和标准SQL规范相比,MySQL中可重复读解决了幻读,实现了串行化隔离级别的功能,同时没有严重影响并发。是通过加锁、阻止插入新数据,来解决幻读的。

二、锁的分类

我们听说过读锁、写锁、共享锁、互斥锁、行锁等等各种名词,根据自己的理解,简单对这些锁进行了分类。

加锁机制:

1、乐观锁:先修改,保存时判断是够被更新过,应用级别

2、悲观锁:先获取锁,再操作修改,数据库级别

锁粒度:

表级锁:开销小,加锁快,粒度大,锁冲突概率大,并发度低,适用于读多写少的情况。

页级锁:BDB存储引擎

行级锁:Innodb存储引擎,默认选项

兼容性:

S锁,也叫做读锁、共享锁,对应于我们常用的 select * from users where id =1 lock in share mode

X锁,也叫做写锁、排它锁、独占锁、互斥锁,对应对于select * from users where id =1 for update

下面这个表格是锁冲突矩阵,可以看到只有读锁和读锁之间兼容的,写锁和读锁、写锁都是冲突的。

冲突的时候会阻塞当前会话,直到拿到锁或者超时

这里要提到的一点是,S锁 和 X锁是可以是表锁,也可以是行锁

索引组织表

先理解下索引组织表。

辅助索引

聚集索引

Innodb中的索引数据结构是 B+ 树,数据是有序排列的,从根节点到叶子节点一层层找到对应的数据。普通索引,也叫做辅助索引,叶子节点存放的是主键值。主键上的索引叫做聚集索引,表里的每一条记录都存放在主键的叶子节点上。当通过辅助索引select 查询数据的时候,会先在辅助索引中找到对应的主键值,然后用主键值在聚集索引中找到该条记录。举个例子,用name=Alice来查询的时候,会先找到对应的主键值是18 ,然后用18在下面的聚集索引中找到name=Alice的记录内容是 77 和 Alice。

表中每一行的数据,是组织存放在聚集索引中的,所以叫做索引组织表。

了解索引数据结构的目的是为了说明,行锁是加在索引上的。

1.select * from user where id=10 for update

一条简单的SQL。在user表中查找id为10的记录,并用for update加X锁。

这里User表中,有3个字段, 主键id 和 另外一个字段name。下面的表格是B+树索引的简化表达。第一行id是索引的节点,第二行和第三行是这行记录,包含了姓名和性别。

如图所示,通过锁住聚集索引中的节点来锁住这条记录。

聚集索引上的锁,比较好理解,锁住id=10的索引,即锁住了这条记录。

2. select * from user where name=‘b’ for update

查询user表中name为d的记录,并用for update加X锁

这里的name上加了唯一索引,唯一索引本质上是辅助索引,加了唯一约束。所以会先在辅助索引上找到name为d的索引记录,在辅助索引中加锁,然后查找聚集索引,锁住对应索引记录。

为什么聚簇索引上的记录也要加锁?试想一下,如果有并发的另外一个SQL,是直接通过主键索引id=30来更新,会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话,两个并发SQL之间是互相感知不到的。

3. select * from user where name=‘b’ for update

查询user表中name为b的记录,并用for update加X锁。这里name上加了普通的索引,不是唯一索引。普通索引的值是可以重复的。会先在辅助索引中找到name为b的两条记录,加X锁,然后得到主键值7和30,到聚集索引中加X锁。

事情并没有那么简单,如果这时有另一个事务,插入了name=b,id=40的记录,却发现是可以插入的。

位置在途中红色线条标注的间隙内,这样就会出现幻读,两次查询得到的结果是不一致的,第一次查到两条数据,插入之后得到三条数据。

为了防止这种情况,出现了另一种锁,gap lcok 间隙锁。锁住的是索引的间隙。

即图中,红色线条标识的空隙。因为新插入name=b的记录,可能出现在这三个间隙内。

这张图里出现了三种锁

记录锁:单行记录上的锁

间隙锁:锁定记录之间的范围,但不包含记录本身。

Next Key Lock: 记录锁+ 间隙锁,锁定一个范围,包含记录本身。

4. 意向锁( Intention Locks )

InnoDB为了支持多粒度(表锁与行锁)的锁并存,引入意向锁。意向锁是表级锁,

IS: 意向共享锁

IX: 意向排他锁

事务在请求某一行的S锁和X锁前,需要先获得对应表的IS、IX锁。

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突,用于表明“某个事务正在某一行上持有了锁,或者准备去持有锁”。比如,表中的某一行上加了X锁,就不能对这张表加X锁。

如果不在表上加意向锁,对表加锁的时候,都要去检查表中的某一行上是否加有行锁,多麻烦。意向锁的兼容性矩阵

5. 插入意向锁(Insert Intention Lock)

Gap Lock中存在一种插入意向锁,在insert操作时产生。

有两个作用:和next-key互斥,阻塞next-key 锁,防止插入数据,这样就不会幻读。

插入意向锁互相是兼容的,允许相同间隙、不同数据的并发插入

三、常见语句的加锁分析

后面会有多个SQL语句,先说明一下表结构

CREATE TABLE `user` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`id_no` varchar(255) DEFAULT NULL COMMENT '身份证号',

`name` varchar(255) DEFAULT NULL COMMENT '姓名',

`mobile` varchar(255) DEFAULT NULL COMMENT '手机号',

`age` int(11) DEFAULT NULL COMMENT '年龄',

`address` varchar(255) DEFAULT NULL COMMENT '地址',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_id_no` (`id_no`),

KEY `idx_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 COMMENT='用户表';

这里有一个user表,5个字段,其中id是主键,id_no是身份证号,加了唯一索引,name是用户姓名,可以重复的,加了普通索引,手机号、年龄、地址都没有索引。

1. 普通select

select * from user where id =1;

begin;

select * from user where id =1;

commit:

普通的select 语句是不加锁的。select包裹在事务中,同样也是不加锁的。where后面的条件不管多少,普通的select是不加锁的。

2. 显式加锁

select * from user where id =1 lock in share mode;

select * from user where id =1 for update;

显式指出要加什么样的锁。上面一个加的是共享锁,下面的是互斥锁。

这里需要强调的一点,需要明确在事务中是用这些锁,不在事务中是没有意义的。

3. 隐式加锁

update user set address '北京' where id=1;

delete from user where id=1;

update和delete也会对查询出的记录加X锁,隐式加互斥锁。加锁类型和for update 类似

后面只按照显式加锁的select for update 举例子,更新和删除的加锁方式是一样的。

4. 按索引类型

elect * from user where id =1 for update;

select * from user where id_no ='a22' for update;

select * from user where name ='王二' for update;

select * from user where address ='杭州' for update;

四条SQL,区别在于where条件的过滤列,分别是主键、唯一索引、普通索引、无索引。

主键:之前提到过索引组织表,这里会在聚集索引上对查询出的记录,加X锁

唯一索引:会在辅助索引上,把在对应的id_no=a22的索引加X锁,因为是唯一的,所以不是next-key锁。然后在主键上,也会在这条记录上加X锁。

普通索引:因为不是唯一的,会在辅助索引上,把对应的id_no=a22的索引加next-key锁。然后在主键加X锁。

无索引:首先,是不推荐这种写法,没有索引的话,因为会全表扫描,数据量大的话查询会很慢。这里讨论的是,这种情况下,会加什么锁? 答案: 首先,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。这是一个很恐怖的事情,请注意。

5. 记录不存在的情况

前面几个例子中,都是可以查到结果的。如果对应记录不存在会怎样?答案是锁住间隙,不允许插入。mysql要保证没有其他人可以插入,所以锁住间隙。

6. 普通 insert 语句

在插入之前,会先在插入记录所在的间隙加上一个插入意向锁。

insert会对插入成功的行加上排它锁,这个排它锁是个记录锁,而非next-key锁(当然更不是gap锁了),不会阻止其他并发的事务往这条记录之前插入 。

7. 先查询后插入

类似于这样的insert

insert into target_table select * from source_table ...

create target_table select * from source_table ...

将select查询的结果集,插入到另一张表中,或者使用结果集,创建一个新表。

和之前简单插入的情况类似,已插入成功的数据加X锁,间隙加上一个插入意向锁。

对于select的源表中的记录,会加共享的 next-key 锁。这是为了防止主从同步出问题。

举个例子:

session1 先开启事务,然后查询user2表的结果集,插入到user表中,session2开启事务,在插入user2中插入数据,所插入的数据刚好是session1能查询到的数据,如果不加锁的话,session2可以插入成功,然后session2提交事务,接着session1提交数据。这样看起来是没问题的,但是session2先提交的,所以bin log中会这样记录,先在user2表中插入数据,然后在user中插入数据,这样的bin log在从库执行的时候,就会出问题。

主库: user2插入一条数据,user 插入一条数据

从库: user2插入一条数据,user 插入两条数据

user表会比主库多一条数据。所以需要锁住select查询表中加next-key锁,不允许user2表中新增数据。

四、分析当前锁的情况

先说一下死锁的定义,死锁是指两个或两个以上的事务在执行过程中,因争夺资源而造成的一种互相等待的现象。这个定义适用于数据库,有几个重点,两个或两个以上的事务,一个事务是不会出现死锁的。争夺的资源一般都是表或者记录。

出现死锁了会怎样,正常情况下,mysql会检查出死锁,并回滚某一个事务,让另一个事务正常运行。

Mysql 会回滚副作用小的事务,判定的标准是执行的时间以及影响的范围。

1.如何知道系统有没有发生过死锁,如何去查看发生过的锁

show status like ‘innodb_row_lock%'; 从系统启动到现在的数据

Innodb_row_lock_current_waits:当前正在等待锁的数量;

Innodb_row_lock_time :锁定的总时间长度,单位ms;

Innodb_row_lock_time_avg :每次等待所花平均时间;

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;

Innodb_row_lock_waits :从系统启动到现在总共等待的次数。

平均时间和锁等待次数比较大的话,说明可能会存在锁争用情况

2. show engine innodb status

展示innodb存储引擎的运行状态

通过这个命令显示的内容比较多,其中有一项lasted detected deadlock 显示最近发生的死锁。

图中红色线条标注的是执行的SQL,以及加了什么锁,可以看出是在这行记录上加了X锁,没有gap锁。

3. 错误日志中查看历史发生过的死锁

set global innodb_print_all_deadlocks=1;

上一个命令,只能看到最近发生的锁,如果我想看历史发生的锁怎么办? 执行这一句,更改innodb 的一个配置,innodb_print_all_deadlocks,打印所有的死锁。会将死锁的信息输出到mysql的错误日志中,默认是不输出,格式和show engine innodb status 是差不多的。

4. information_schema.innodb_locks

information_schema 数据库是mysql自带的,保存着关于MySQL服务器所维护的所有其他数据库的信息。其中innodb_locks表,记录了事务请求但是还没获得的锁,即等待获得的锁。

lock_id:锁的id,由锁住的空间id编号、页编号、行编号组成

lock_trx_id:锁的事务id。

lock_mode:锁的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP]

lock_type:锁的类型,表锁还是行锁

lock_table:要加锁的表。

lock_index:锁住的索引。

lock_space:innodb存储引擎表空间的id号码

lock_page:被锁住的页的数量,如果是表锁,则为null值。

lock_rec:被锁住的行的数量,如果表锁,则为null值。

lock_data:被锁住的行的主键值,如果表锁,则为null值。

5. information_schema.innodb_lock_waits

查看等待中的锁

requesting_trx_id:申请锁资源的事务id。

requested_lock_id:申请的锁的id。

blocking_trx_id:阻塞的事务id,当前拥有锁的事务ID。

blocking_lock_id:阻塞的锁的id,当前拥有锁的锁ID

6. information_schema.innodb_trx

查看已开启的事务

trx_id:innodb存储引擎内部事务唯一的事务id。

trx_state:当前事务的状态。

trx_started:事务开始的时间。

trx_requested_lock_id:等待事务的锁id,如trx_state的状态为LOCK WAIT,那么该值代表当前事务之前占用锁资源的id,如果trx_state不是LOCK WAIT的话,这个值为null。

trx_wait_started:事务等待开始的时间。

trx_weight:事务的权重,反映了一个事务修改和锁住的行数。在innodb的存储引擎中,当发生死锁需要回滚时,innodb存储引擎会选择该值最小的事务进行回滚。

trx_mysql_thread_id:正在运行的mysql中的线程id,show full processlist显示的记录中的thread_id。

trx_query:事务运行的sql语句

五、预防死锁

1. 以相同的顺序更新不同的表

这样执行的话,会出现锁等待,但不容易出现死锁。

假设有这么两个接口,增加老师和学生的幸运值、减少老师和学生的幸运值,这个需求是我造出来的,先别管需求是不是合理。

现在有两个请求,一个增加幸运值,一个降低幸运值,如果更新顺序不同的话,就是这样,第一个事务先给老师加幸运值,第二个接口给学生减幸运值,然后第一个事务给学生加幸运值,因为锁已经被第二个事务持有了,所以第一个事务等待。然后第二个事务给老师幸运值,这时就互相等待锁,出现了死锁。

2. 预先对数据进行排序

比如一个接口批量操作数据,如果乱序的话,并发的情况下,也是有可能出现死锁的。给学生批量加分的接口,按照表格中的执行顺序的话,第一个事务,持有A的锁,请求B的锁,第二个事务持有B的锁,请求A的锁,出现死锁。

3. 直接申请足够级别的锁,而非先共享锁,再申请排他锁。

比如这种情况,两个事务,先申请共享锁,共享锁是兼容的,然后申请互斥锁的时候,需要互相等待,就出现了死锁。

4. 事务的粒度及时间尽量保持小,这样锁冲突的概率就小了,也就不容易出现死锁。不建议在数据库的事务中执行API调用。

5. 正确加索引。没有索引会引起全表扫描,类似于锁表。

六:总结:

1,正确的加索引,尽量先查询,然后使用主键去加锁,等于操作来加锁,而尽量避免辅助索引,或者不是范围比较来加锁。

2,出现了锁的问题,根据数据库已有的信息,分析死锁。

3,举了几个例子,可能很多都是上线之后才发现的,最好能在开发阶段就避免死锁。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_35976993/article/details/113270037

智能推荐

07-Elasticsearch集成_先锋成员5A的博客-程序员宅基地

一、Spring Data框架集成1.1 Spring Data框架介绍Spring Data是一个用于简化数据库、非关系型数据库、索引库访问,并支持云服务的开源框架。其主要目标是使得对数据的访问变得方便快捷,并支持map-reduce框架和云计算数据服务。 Spring Data可以极大的简化JPA(Elasticsearch…)的写法,可以在几乎不用写实现的情况下,实现对数据的访问和操作。除了CRUD外,还包括如分页、排序等一些常用的功能。Spring Data的官网:Spring Data

通用计算机系统和嵌入式计算机系统相同点,嵌入式计算机系统同通用型计算机系统相比有什么特点?..._波澜不惊的蓝果冻的博客-程序员宅基地

原标题:嵌入式计算机系统同通用型计算机系统相比有什么特点?嵌入式系统是计算机技术、通信技术、半导体技术、微电子技术、语音图像数据传输技术等先进技术和具体应用对象相结合后的更新换代产品。系统无多余软件,硬件亦无多余存储器,可靠性高,成本低,体积小,功耗少;可应用于家电、工业、通信、国防等领域。其产品形态丰富多样,市场潜力巨大。那么下面尚观教育小编跟同学们简单聊一聊嵌入式计算机系统同通用型计算机系统相...

acm-(离散化、单调栈)CF1454F. Array Partition_&*^*&的博客-程序员宅基地

传送门首先离散化,然后枚举值xxx作为区间的最大最小值是否能够找到对应的ansansans。首先xxx最左边和最右边的两个位置tl,trtl,trtl,tr是必须被左区间和右区间所取的,设mxl[i]mxl[i]mxl[i]表示max(a1,a2,...,ai)max(a_1,a_2,...,a_i)max(a1​,a2​,...,ai​),mxr[i]mxr[i]mxr[i]表示max(ai,ai+1,...,an)max(a_i,a_{i+1},...,a_n)max(ai​,ai+1​,...,.

判断43是不是质数用c语言,1是素数吗(c语言判断一个数为素数)_思爱空间的博客-程序员宅基地

1不是素数,最小的质数是2。原因如下:素数又称质数,有无限个。一个大于1的自然数,除了1和它本身外,不能被其他自然数整除,换句话说就是该数除了1和它本身以.质数又称素数。指在一个大于1的自然数中,除了1和此整数自身外,没法被其他自然数整除的数。换句话说,只有两个正因数(1和自己)的自然数即为素数。比1大但不是.质数是一个大于1的自然数,除了1和它本身外,不能整除以其他自然数,最小的质数是2,1不是...

vue项目-从0开始 vue+element ui_前端菜鸡日常丶的博客-程序员宅基地

手把手带你撸一套后台管理系统1.配置环境使用 npm 创建 vue 项目1.配置环境node.js https://nodejs.org/zh-cn/双击安装,在安装界面一直Next,直到Finish完成安装。打开控制命令行程序(CMD),检查是否正常我有一个右击桌面就可以在快捷菜单上有管理者身份的 cmd。链接:https://pan.baidu.com/s/1TqMeyY77dZYuIsT-9bmiVQ提取码:1klz虽然 node 自带 npm ,但是不是最新版本的 np

随便推点

Microsoft Bot Framework 上手_weixin_30535913的博客-程序员宅基地

因为这前使用过MS Bot Frameowrk 做过开发, 最近心血来潮想做(挖坑)一个小的bot。今天带领大家使用MS Bot Framework创建Hello World。首先,我们要创建项目, 因为我已经更新了extensions & update, 所以我的visual c#里面会有bot framework的模板当我们创建好项目之后, 我们会发...

android activity 动态设置 dpi,如何在Android中将位图保存到位图时设置dpi图像?_PS自学部落的博客-程序员宅基地

以下是我用于管理截图和调整图像大小的一些辅助方法。public static Bitmap getResizedBitmap(Bitmap bm, int newWidth, int newHeight, boolean recycleOriginal) {int width = bm.getWidth();int height = bm.getHeight();// Determine scal...

用户输入的是q么python_Python 第1周 - Python基础-python入门\变量\用户输入\循环语句的使用..._weixin_39926040的博客-程序员宅基地

内容Python介绍安装Hello World程序变量用户输入if..else流程判断while循环for循环作业需求3、Python入门在后面的操作中,主要以python 3.4 版本为主:Python脚本执行1在linux 下创建一个文件叫hello.py,并输入:2print("hello world ")3然后执行命令:python hello.py ,输出:4$ python hello...

String 有多长?_公众号:Java后端的博客-程序员宅基地

1String的形式public final class StringString类是由final修饰的,所以是不能被继承的①,我们在对字符串进行比较时,一般是期望对比其中的字符串是否...

idea的安装与破解_kissdead0xzy的博客-程序员宅基地

Windows7下安装与破解IntelliJ IDEA2017IntelliJ IDEA,是java语言开发的集成环境,IntelliJ在业界被公认为最好的java开发工具之一,尤其在智能代码助手、代码自动提示、重构、J2EE支持、各类版本工具(git、svn、github等)、JUnit、CVS整合、代码分析、 创新的GUI设计等方面的功能可以说是超常的。IDEA是JetBrains公司的产品,...

python制作图片墙_Python 打造专属照片墙_MM Wang的博客-程序员宅基地

1、先直接上代码import randomfrom PIL import Imageimport os# 照片墙 背景 照片素材 图形#定义图形fingure = [[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0],[0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 1,...

推荐文章

热门文章

相关标签