您的位置:新葡亰496net > 网络数据库 > 高并发和大数目存储方案,为啥开采职员必供给

高并发和大数目存储方案,为啥开采职员必供给

发布时间:2019-09-22 11:05编辑:网络数据库浏览(120)

    Sqlserver 高并发和大数据存储方案 

    原文:

    1.锁?

    计算机程序锁

         随着用户的日益递增,日活和峰值的暴涨,数据库处理性能面临着巨大的挑战。下面分享下对实际10万 峰值的平台的数据库优化方案。与大家一起讨论,互相学习提高!

    4.1、innodb存储引擎表类型

    innodb表类似oracle的IOT表(索引聚集表-indexorganized table),在innodb表中每张表都会有一个主键,如果在创建表时没有显示的定义主键则innodb如按照如下方式选择或者创建主键。
    首先表中是否有唯一非空索引(unique not null),如果有则该列即为主键。
    不符合上述条件,innodb存储引擎会自动创建一个6字节大小的指针,rowid()。

    1.1何为锁


       案例:游戏平台.

    4.2、innodb逻辑存储结构

    innodb的逻辑存储单元由大到小分别是 tablespace,segment,extent,page(block)组成

    锁在现实中的意义为:封闭的器物,以钥匙或暗码开启。在计算机中的锁一般用来管理对共享资源的并发访问,比如我们java同学熟悉的Lock,synchronized等都是我们常见的锁。当然在我们的数据库中也有锁用来控制资源的并发访问,这也是数据库和文件系统的区别之一。

     

    1、解决高并发

    4.2.1、表空间(tablespace)

    所有数据都是存放在表空间中的,启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间中,每张表空间内存放的只是数据,索引和插入缓冲,其他类的数据,如undo信息,系统事务信息,二次写缓冲等还是存放在原来你的共享表空间。

    1.2为什么要懂数据库锁?

    • 控制对共享资源进行并发访问
    • 保护数据的完整性和一致性

          当客户端连接数达到峰值的时候,服务端对连接的维护与处理这里暂时不做讨论。当多个写请求到数据库的时候,这时候需要对多张表进行插入,尤其一些表 达到每天千万 的存储,随着时间的积累,传统的同步写入数据的方式显然不可取,经过试验,通过异步插入的方式改善了许多,但与此同时,对读取数据的实时性也需要做一定的牺牲。

    4.2.2、段(segment)

    常见的segment有数据段、索引段、回滚段。innodb是索引聚集表,所以数据就是索引,索引就是数据,那么数据段即是B 树的页节点(leaf node segment),索引段即为B 树的非索引节点(non-leaf node segment)。而且段的管理是由引擎本身完成的。

    通常来说对于一般的开发人员,在使用数据库的时候一般懂点DQL,DML(insert,update,delete)就够了。

     新葡亰496net 1

          异步的方式有很多,目前采取的方式是通过作业每隔一段时间(5min、10min..看需求设定)将临时表的数据转到真实表。

    4.2.3、区(extend)

    区是由64个连续的页主成,每个页大小为16K,即每个区的大小为(64*16K)=1MB,对于大的数据段,mysql每次最多可以申请4个区,以此保证数据的顺序性能。

    小明是一个刚刚毕业在互联网公司工作的Java开发工程师,平常的工作就是完成PM的需求,当然在完成需求的同时肯定逃脱不了spring,springmvc,mybatis的那一套框架,所以一般来说sql还是自己手写,遇到比较复杂的sql会从网上去百度一下。对于一些比较重要操作,比如交易啊这些,小明会用spring的事务来对数据库的事务进行管理,由于数据量比较小目前还涉及不了分布式事务。

     

       1.已有原始表A 也是在读取的时候真正用到的表。

    4.2.4、页(page)

    页是innodb磁盘管理最小的单位,innodb每个页的大小是16K,且不可更改。常见的类型有:数据页 B-tree Node;undo页 Undo Log Page;系统页 System Page;事务数据页 Transaction system Page;插入缓冲位图页 Insert Buffer Bitmap;插入缓冲空闲列表页 Insert Buffer freeBitmap;未压缩的二进制大对象页Uncompressed BLOB Page;压缩的二进制大对象页 Compressed BLOB Page。

    前几个月小明过得都还风调雨顺,知道有一天,小明接了一个需求,商家有个配置项,叫优惠配置项,可以配置买一送一,买一送二等等规则,当然这些配置是批量传输给后端的,这样就有个问题每个规则都得去匹配他到底是删除还是添加还是修改,这样后端逻辑就比较麻烦,聪明的小明想到了一个办法,直接删除这个商家的配置,然后全部添加进去。小明马上开发完毕,成功上线。

    新葡亰496net 2.png)

          2.建立与原始表A同结构的B和C,用来作数据的中转处理,同步流程是C->B->A。

    4.2.5、行

    innodb存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放。每个页最多可以存放16K/2~200行,也就是7992个行。

    新葡亰496net 3

    开始上线没什么毛病,但是日志经常会出现一些mysql-insert-deadlock异常。由于小明经验比较浅,对于这类型的问题第一次遇见,于是去问了他们组的老司机-大红,大红一看见这个问题,然后看了他的代码之后,输出了几个命令看了几个日志,马上定位了问题,告诉了小明:这是因为delete的时候会加间隙锁,但是间隙锁之间却可以兼容,但是插入新的数据的时候就会因为插入意向锁会被间隙锁阻塞,导致双方被资源被互占,导致死锁。小明听了之后似懂非懂,由于大红的事情比较多,不方便一直麻烦大红,所以决定自己下来自己想。下班过后,小明回想大红说的话,什么是间隙锁,什么是插入意向锁,看来作为开发者对数据库不应该只会写SQL啊,不然遇到一些疑难杂症完全没法解决啊。想完,于是小明就踏上了学习Mysql锁这条不归之路。

    lock  主要是事务,数据库逻辑内容,事务过程

          3.建立同步数据的作业Job1和记录Job1运行状态的表,在同步的时候比较关键的是需要检查Job1的当前状态,如果当前正在将B的数据同步到A,则把服务端过来的数据存到C,然后再把数据导入到B,等到下一次Job执行的时候再将这批数据转到A。如图1:

    4.3、innodb物理存储结构

    innodb引擎由共享表空间,日志文件(redo log),表结构定义文件组成。

    欢迎工作一到五年的Java工程师朋友们加入Java程序员开发: 854393687

    latch/mutex 内存底层锁;

     

    4.4、innodb行记录格式

    mysql从5.1开始,innodb提供了compact和redundant(为了兼容以前版本)两种格式来存放行记录数据。

    群内提供免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!

     

                    新葡亰496net 4                                                        图1

    4.4.1、compact行记录格式

    Compact行记录的设计目标是能高效存放数据。不管是char还是varchar类型,NULL指是不占用存储空间的。行记录中还包括两个隐藏列 事务ID列(6字节)和回滚指针列(7字节) 若没有定义的PrimaryKey 会增加一个6字节的RowID列。InnoDB在页内部是通过一种链表方式串联各个行记录的。

    2.InnoDB

    更新丢失

     

    4.4.2、redundant行记录格式

    Redundant行记录格式为了兼容以前版本。每个行最多有1023个列,因为列的数量占用了10位。对于varchar的NULL值,它不占用任何存储空间,而对于类型char的NULL值需要占用空间。

    2.1mysql体系架构


         同时,为保万无一失和便于排查问题,应该用一个记录整个数据库实例的存储过程,在较短的时间检查作业执行结果,如果遇到异常失败的,应该及时通过其他方式通知到相关人员。如写入到发邮件和短信表,让一个Tcp的通知程序定时读取发送等等。

    4.4.3、行溢出数据

    innoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外,作为行溢出数据。Varchar(N)中的N指的是字符的长度,官方手册中定义的65535长度是指所有VARCHAR列的长度总合。

    数据一般都是存放在B-tree Node的页类型中,但是发生行溢出的时,存放行溢出的页类型为Uncompress BLOB Page。如果一个页中至少放入两行的数据,那varchar就不会存放到BLOB页中,阀值长度为8098。对于TEXT或者BLOB的数据类型,我们总是以为它们是放在Uncompressed BLOB Page中的,其实这也是不准确的,放在数据页还是BLOB页同样和前面讨论的VARCHAR一样。

    小明没有着急去了解锁这方面的知识,他首先先了解了下Mysql体系架构:

    原因:

    注:如果一天的数据达到几十个G,如果又对这个表有查询要求(分区下面会提到),下策之一:

    4.4.4、compressed与dynamic记录格式

    InnoDB Plugin引入了新的文件格式成为Barracuda文件格式,它拥有两种新的行记录格式Compressed和Dynamic两种,它对于存放BLOB的数据采用了安全的行溢出方式。

    新葡亰496net 5

    B的更改还没有提交时,A已经再次修改了数据。

         可将B同时同步到多台服务器分担下查询压力,减少资源的竞争。因为整个数据库的资源是有限的,如插入操作,会先获得一个共享锁,然后通过聚集索引定位到某一行数据,再升级为意向锁,而sqlserver对锁的维护根据数据的大小需要申请不同的内存,造成了资源的竞争。所以应该尽可能的将读和写分开,可根据业务模型分,可根据设定的规则分;在平台性的项目中应该优先保证数据能有效的插入。

    4.4.5、char的行结构存储

    从mysql4.1开始CHR(n),中N指定的是字符的长度,而不是之前版本的字节长度。也就是说在不同字符集下,CHAR的内部存储不是定长的数据。可以通过select a,char_length(a),length(a) from t;查看字符和字节数。所以在多字符集下,char和varchar占用a空间是一样的。

    可以发现Mysql由连接池组件、管理服务和工具组件、sql接口组件、查询分析器组件、优化器组件、 缓冲组件、插件式存储引擎、物理文件组成。

    此时A使用原来的元数据作为基础更新后,B的更新便会丢失;

         在不可避免的查询大数据肯定会耗用大量的资源,如遇到批量删除的时候,可以换成以循环分批次(如一次2000条)的方式,这样不至于这个进程导致整个库挂掉,衍生出一些无法预计的bug。经实践,有效可行,只是牺牲了存储空间。也可根据查询需求将表里数据量大的字段拆分出来到新表,当然这些也要根据每个业务场景结合需求来设定,设计出适合而并不需要华丽的方案即可。

    4.5、innodb数据页结构

    InnoDB数据页由七部分组成:
    File Header:文件头( 38 bytes )
    Page Header:页头( 56 bytes )
    Infimum Supremum Records:页中上/下界记录
    Users Records:用户记录,即行记录
    Free Space:空闲空间
    Page Directory:叶目录
    File Trailer:文件结尾信息

    小明发现在mysql中存储引擎是以插件的方式提供的,在Mysql中有多种存储引擎,每个存储引擎都有自己的特点。随后小明在命令行中打出了:

    新葡亰496net 6.png)

     

    4.6、named file formats

    innodb存储引擎通过named file formats机制来解决不同版本下页结构兼容性问题。之前的版本定义为Antelope(包括Compact和Redudant文件格式),最新定义为Barracuda(包括Compressed和Dynamic文件格式)。使用参数innodb_file_format指定文件格式。

    showenginesG;

    新葡亰496net 7

     

    4.7、约束

    一看原来有这么多种引擎。

     

     2、解决存储问题

    4.7.1、数据完整性

    innodb提供了以下四种约束:Primary key,Unique Key,Foreign Key,Default,Not NULL。

    又打出了下面的命令,查看当前数据库默认的引擎:

    解决办法:

      如果每天单表的数据都达到了几十个G,改善存储方案自然迫不及待了。现分享下自有的方案,在暴涨的数据摧残之下,仍坚守在一线!现举例对自有环境分享拙见:

    4.7.2、约束的创建和查找

    创建时候定义,或者使用alter table定义。

    showvariableslike'%storage_engine%';

    在修改数据上加写锁,当有锁时,A会等B更新提交完,才可以继续在B的基础上继续更新;

      现有数据表A,单表每天新增数据30G,在存储的时候采用异步将数据同步的方式,有的不能清除数据的表,在分区后还可分文件组,将文件组分配到不同的磁盘中,减少IO资源的竞争,保障现有资源的正常运行。现结合需求保留历史数据5天:

    4.7.3、约束和索引的区别

    primary key和unique key既是约束也是主键。约束是一个逻辑的概念,用来保证数据完整性,而索引是一个数据结构,有逻辑上的概念,在数据库中更是一个物理存储的方式。

    新葡亰496net 8

    新葡亰496net 9.png)

      1这时需要通过作业job根据分区函数去生成分区方案,如根据userid或者时间字段来分区;

    4.7.4、对于错误数据的约束

    可以通过修改sql_mode来保证约束的强制性。

    小明恍然大悟:原来自己的数据库是使用的InnoDB,依稀记得自己在上学的时候好像听说过有个引擎叫MyIsAM,小明想这两个有啥不同呢?马上查找了一下资料:

     新葡亰496net 10

    ·    2.将表分区后,查询可以通过对应的索引,快速定位到某一段分区;

    4.7.5、ENUM和SET约束

    由于mysql不支持check约束,所以可以通过ENUM和SET来实现部分需求,还可以通过触发器来实现check约束,注意需要修改sql_mode=’strict_trans_tables’; 只能限于对离散数值的约束,对于ENUM 若插入非法值将插入空字符串作为特殊错误值。

    新葡亰496net 11

     

      3通过作业合并分区将不要的分区数据转移到相同结构和索引的表,然后清除这个表的数据。

    4.7.6、触发器与约束

    触发器的作用是在insert,delete和update命令之前或之后自动调用sql命令或者存储过程。所以一个表最多可以建立6个触发器。

    小明大概了解了一下InnoDB和MyIsAM的区别,由于使用的是InnoDB,小明就没有过多的纠结这一块。

     

      如图2:

    4.7.7、外键

    2.2事务的隔离性

    事务锁粒度

     新葡亰496net 12

    4.8、视图

    小明在研究锁之前,又回想到之前上学的时候教过的数据库事务隔离性,其实锁在数据库中其功能之一也是用来实现事务隔离性。而事务的隔离性其实是用来解决,脏读,不可重复读,幻读几类问题。


    图2

    4.8.1、视图的作用

    2.2.1 脏读

     

     

    4.8.2、物化视图

    Oracle数据库支持物化视图—该视图不是基于基表的虚表,而是根据基表实际存在的实表,物化视图可以用于预先计算并保存表链接或聚集等耗时较多的操作结果。在MS中,这种视图为索引视图。当基表发生了DML操作后,物化视图采用ON DEMAND和ON COMMIT方式刷新进行同步。Mysql的视图不支持物化视图,都是虚拟的。

    一个事务读取到另一个事务未提交的更新数据。 什么意思呢?

    行锁: innodb ,oracle

      通过sql查询跟踪捕捉到查询耗时长的,以及通过sql自带的存储过程sp_lock或视图dm_tran_locks、dblockinfo查看当前实例存在的锁的类型和粒度。

    4.9、分区表

    新葡亰496net 13

    页锁:sql server

      定位到具体的查询语句或者存储过程之后,对症下药!药到病除!

    4.9.1、分区表的概述

    分区表不是在存储引擎曾完成的,所以不止innodb支持分区表功能。myisma,ndb等都支持。mysql的分区表是水平分区,并不是垂直分区,mysql的分区表是局部分区索引,一个分区中既存储数据又存放索引。当前mysql数据库支持以下几种类型的分区:
    Range分区,行数据基于属于一个给定连续区间的列值放入分区,这个值只能是整数。VALUE LESS THAN需指定MAXVALUE值的分区,主要用于日期列的分区。对于RANGE分区的查询,优化器只能对YEAR() TO_DAYS() TO_SECONDS()和UNIX_TIMESTAMP()函数进行优化选择。
    LIST分区和range类似,只是list分区里面是离散的值,这个值只能是整数。(VALUE IN对于未定义的插入,MySQL会抛出异常。对于多条记录同时插入过程中存在未定义的值时,MyISAM分区会允许之前的行数据插入,而拒绝之后的行数据插入,但是InnoDB将其视为一个事务从而ROLLBACK整个插入。
    HASH分区,根据用户自定义的表达式的返回值 返回值不为负(PARTITION BY HASH (expr) 将数据均匀分布还可按LINEAR HASH分区区别在于算法不同)。hash分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致一致。
    KEY分区,根据mysql数据库提供的哈西函数进行分区。key分区和hash分区相似,不同在于hash分区是用户自定义函数进行分区,key分区使用mysql数据库提供的函数进行分区。
    columns分区,mysql-5.5开始支持COLUMNS分区,可视为RANGE和LIST分区的进化,COLUMNS分区可以直接使用非整形数据进行分区。RANGE COLUMNS分区可对多个列的值进行分区。
    不论什么类型的分区,如果表中存在主键和唯一索引,那么分区列必须是主键或者唯一索引的一个组成部分。否则回报错。

    在事务A,B中,事务A在时间点2,4分别对user表中id=1的数据进行了查询了,但是事务B在时间点3进行了修改,导致了事务A在4中的查询出的结果其实是事务B修改后的。破坏了数据库中的隔离性。

    表锁:Myisam ,memory

          当然,仁者见仁,智者见智-_-

    4.9.2、子分区

    mysql允许在RANGE和LIST分区上再进行HASH或者key的子分区。每个分区上的子分区数量必须相同。在每个分区内,子分区的名称是唯一的,分区可以放到不同磁盘上。

    2.2.2 不可重复读

     

     

    4.9.3、分区中的NULL值

    RANGE,HASH,KEY分区如果插入null值,mysql会把它放入最左边的分区,如果删除最左边的分区,null值不会被删除,他会记录到新的最左边的分区。LIST分区如果没有指定NULL值的存放位置,那么就会报错。

    在同一个事务中,多次读取同一数据返回的结果不同,和脏读不同的是这里读取的是已经提交过后的。

    获取innodb行锁争用情况

     

    4.9.4、分区的性能

    OLTP(在线事务处理,如博客,电子商务,网络游戏)系统不适合使用分区表,如果磁盘空间和磁盘IO没出现瓶颈,也不建议使用分区表。而OLAP(在线分析处理,如数据仓库,数据集市)比较适合分区操作。

    新葡亰496net 14

     

     

    索引和算法

    索引和开销是需要找一个平衡点,过多或者过少都会影响性能,从而导致负载过高,浪费硬件资源。而且索引应该一开始就需要添加上,事后添加的话需要DBA根据监控大量SQL语句,耗费大量时间。

    在事务B中提交的操作在事务A第二次查询之前,但是依然读到了事务B的更新结果,也破坏了事务的隔离性。

    mysql> show status like '%innodb_row_lock%';
     ------------------------------- ------- 
    | Variable_name                 | Value |
     ------------------------------- ------- 
    | Innodb_row_lock_current_waits | 0     |
    | Innodb_row_lock_time          | 0     |
    | Innodb_row_lock_time_avg      | 0     |
    | Innodb_row_lock_time_max      | 0     |
    | Innodb_row_lock_waits         | 0     |
     ------------------------------- ------- 
    5 rows in set (0.00 sec)
    

     

    5.1、innodb存储引擎概述

    innodb支持常见的两种索引,B 树索引和hash索引。hash索引是自适应的,不能认为干预。B 树是由平衡二叉树演化而来,但是B 树不是一个二叉树。
    B 树并不能直接找到具体的行,B 树索引只能找到数据行所在的页,然后数据库通过把页读入内存,再在内存中进行查找。

    2.2.3 幻读

    如果发现锁争用比较严重,如innodb_row_lock_waits 和 innodb_row_lock_time_avg的值比较高,

     

    5.2、二分查找法

    页中的具体行就是通过二分法查找的。1946年发明的二分查找法,直到1962年才出现完整正确的二分查找法。

    一个事务读到另一个事务已提交的insert数据。 时间点|事务A | 事务B ---|---|--- 1|begin; | 2|select * from user where id > 1; | begin; 3| | insert user select 2; 4| | commit; 5|select * from user where id > 1; | 6|commit; |

    还可以通过设置innodb monitor 来进一步观察发生锁冲突的表,数据行等,并分析锁争用的原因:

     

    5.3、平衡二叉树

    平衡二叉树(左节点键值<根节点键值 <右节点键值)首先的符合二叉树定义,其次必须满足任何节点的左右两个子树高度最大差1.平衡二叉树的效率较高,但是维护平衡二次树需要消耗比较多的资源。多用于内存结构对象中,维护开销相对比较小。

    在事务A中查询了两次id大于1的,在第一次id大于1查询结果中没有数据,但是由于事务B插入了一条Id=2的数据,导致事务A第二次查询时能查到事务B中插入的数据。

     

     

    5.4、B 树

    B 树是从B树和索引顺序访问方法演化而来。在B 树中,所有记录节点都是按键值的大小顺序存放在同一层的叶节点中,各页节点指针进行链接。同时它们的父节点只是作为索引节点使用。

    事务中的隔离性:

     

     

    5.4.1、B 树的插入操作

    B 树总会保持平衡,但是对于新插入的值可能需要大量拆分,这样会消耗大量磁盘资源,所以B 树有了旋转(rotation)功能,旋转发生在leat page已经满了,但是其左右节点没有满的情况下,这时B 树并不会着急去拆分页的操作,而且是将记录转移到所在页的兄弟节点上,通常左兄弟先被检查。具体操作看书。

    新葡亰496net 15

    innodb锁模式与粒度

     

    5.4.2、B 树的删除操作

    B 树使用填充因子(fill factor)来控制树的删除变化,50%是填充因子可设的最小值。B 树的删除操作同样必须保证删除后页节点中的记录依然排序。具体操作看书。

    小明注意到在收集资料的过程中,有资料写到InnoDB和其他数据库有点不同,InnoDB的可重复读其实就能解决幻读了,小明心想:这InnoDB还挺牛逼的,我得好好看看到底是怎么个原理。


     

    5.5、B 树索引

    B 树索引在数据库中有一个特点是高扇出性(fan out),B 树的高度一般是2-3层。B 树索引可以分为聚集索引(clustered index)和辅助聚集索引(secondary index),其内部都是B 树,叶节点存放着所有的数据。它们不同的是:叶节点存放的是否是一整行的信息。
    聚集索引:即表中数据按照主键顺序存放,而聚集索引就是按照每张表的主键构造一颗B 树,并且叶节点中存放着整张表的行记录数据。聚集索引的存储并不是物理上的连续,而是逻辑上的连续。它的另一个好处是:对于主键的排序查找和范围查找速度非常快。

    辅助索引:也称为非聚集索引,叶级别不包含行的全部数据,叶节点除了包行键值以外,每个叶级别中的索引行中还包含了一个书签,该书签就是对应行数据的聚集索引键。

    新葡亰496net 16

    2.3 InnoDB锁类型

     

     

    5.5.1、B 树索引的管理

    索引可以索引整个列的数据,也可以只索引一个列的开头部分数据。InnoDB Plugin支持一种称为快速索引创建方法,这种方法只限定于辅助索引,创建索引会对表加上一个S锁,删除时只需将辅助索引的空间标记为可用,并删除内部视图上的对该表的索引定义即可。

    小明首先了解一下Mysql中常见的锁类型有哪些:

    四种基本锁模式

     

    5.6、B 树索引的使用

    2.3.1 S or X

    • 共享锁(S)-读锁-行锁
    • 排他锁(X)-写锁-行锁
    • 意向共享锁(IS)-表级 :事务想要获得一张表中某几行的共享锁
    • 意向排他锁(IX)-表级:事务想要获得一张表中某几行的排他锁

     

    5.6.1、什么时候使用B 树索引

    当某个字段的取值范围很广,几乎没有重复,即高选择性,则使用B 树索引是最适合的。根据笔者经验,一般取出数据占整个的20%时,优化器就不会使用索引,而是全表扫描。

    在InnoDb中实现了两个标准的行级锁,可以简单的看为两个读写锁:

     

     

    5.6.2、顺序读,随机读与预读取

    顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据,只是逻辑地顺序读在物理磁盘上可能还是随机读取。
    随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据,而辅助索引和主键所在的数据段不同,因此访问方式是随机的。
    为提高读取性能,InnoDB采用预读取方式将所需数据读入内存,包括随机预读取 random read ahead 和线性预读取 linear read ahead。但是自InnoDB Plugin1.0.4起,随机访问的预读取被取消了,保留了线性预读取,并加入了innodb_read_ahead_threshold参数。它控制一个区中多少页被顺序访问时,InnoDB才启用预读取,预读取下一个页中所有的页。

    S-共享锁:又叫读锁,其他事务可以继续加共享锁,但是不能继续加排他锁。

    意向锁,简单来说就是:

     

    5.7、hash索引

    innodb存储引擎中自适应hash索引使用的是散列表(hash table)的数据结构。但是散列表不只存在于自适应hash中,每个数据库中都存在,用来加速内存中数据的查找。

    X-排他锁: 又叫写锁,一旦加了写锁之后,其他事务就不能加锁了。

    如需要对页上的记录R进行X锁,那么分别需要对该记录所在的数据库,表,页,上意向锁IX,最后对记录R上X锁。

     

    5.7.1哈西表(hash table)

    hash table又叫散列表,由直接寻址表改进而来。利用哈希函数解决了直接寻址遇到的问题,同时又使用链接发解决了碰撞问题。

    兼容性:是指事务A获得一个某行某种锁之后,事务B同样的在这个行上尝试获取某种锁,如果能立即获取,则称锁兼容,反之叫冲突。

    若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。

     

    5.7.2自适应哈西索引

    它是数据库系统自己创建并使用的,DBA本身并不能对其进行干预。需要注意的是,哈希索引只能用来搜素等值的查询,对于其它的查找是不能使用哈希索引的。我们只能通过参数innodb_adaptive_hash_index来禁用或启动此特性。

    纵轴是代表已有的锁,横轴是代表尝试获取的锁。

     

     

    锁是区别文件系统和数据库系统的一个关键特性。

    新葡亰496net 17

    innodb支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。

     

    6.1、什么是锁?

    锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储引擎会在数据库内部其他多个地方使用锁,从而允许对不同资源提供并发访问。例如操作缓冲池中的LRU列表,删除,添加,移动LRU列表中的元素,为了保证一致性,必须有锁的介入。

    2.3.2 意向锁

     

     

    6.2、innodb存储引擎中的锁

    意向锁在InnoDB中是表级锁,和他的名字一样他是用来表达一个事务想要获取什么。意向锁分为:

    意向锁:

     

    6.2.1、锁的类型

    S lock 共享锁允许事务读一行数据。
    X lock 排它锁允许事务删除或者更新一条数据。
    IS lock 意向共享锁事务想要获得一个表中某几行的共享锁。
    IX lock 意向拍他所事务想要获得一个表中某几行的排它锁。
    因为InnoDB存储引擎支持的是行级别的锁,所以意向锁其实不会阻塞除全表扫描以外的任何请求。

    意向共享锁:表达一个事务想要获取一张表中某几行的共享锁。

    • 意向锁总是自动先加,并且意向锁自动加自动释放
    • 意向锁提示数据库这个session将要在接下来将要施加何种锁
    • 意向锁和X/S 锁级别不同,除了阻塞全表级别的X/S锁外其他任何锁 

     

    6.2.2、一致性的非锁定读操作

    一致性非锁定读(consistent nonlocking read)是指innodb通过多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。非锁定读的机制大大提高了数据读取的并发性,在InnoDB引擎中为默认的读取方法,即读取不会占用和等代表上的锁。
    多版本控制是通过快照实现的,快照数据其实就是当前数据之前的历史版本,可能有多个版本。这种技术称为行多版本技术,由此带来的并发控制叫做多半本并发控制(multi version concurrency control,MVCC).
    在Read Committed和Repeatable Read(innodb默认的事务隔离级别)下,innodb存储引擎使用非锁定的一致性读。但是对于快照数据的定义却不同。在Read Commited级别,对于快照数据,非一致性读总是读取被锁定行的最新一份快照。在Repeatable级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。

    意向排他锁:表达一个事务想要获取一张表中某几行的排他锁。

    自动施加,自动释放,

     

    6.2.3、SELECT…FOR UPDATE &SELECT…LOCK IN SHARE MODE

    SELECT…FOR UPDATE 可以获得一个X锁。
    SELECT…LOCK IN SHARE MODE 可以获得一个S锁。
    注意上述操作时必须使用显示提交方式,即加上begin,start transaction或者set autocommit = 0。

    这个锁有什么用呢?为什么需要这个锁呢? 首先说一下如果没有这个锁,如果要给这个表加上表锁,一般的做法是去遍历每一行看看他是否有行锁,这样的话效率太低,而我们有意向锁,只需要判断是否有意向锁即可,不需要再去一行行的去扫描。

     

    6.2.4、自增长和锁

    对于含有子增长计数器的表进行插入时,会执行”SELECT MAX(auto_inc_col) FROM t FOR UPDATE;”插入操作会更具这个自增长的计数器值加1赋予自增长列。这个实现方式叫做AUTO-INC Locking。这是一种特殊的锁,为了提高并发,它不会在事务执行完才释放,只是在语句执行后立即释放。
    从mysql-5.1.22版本开始,innodb引擎提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了子增长值插入的性能。并且mysql-5.1.22开始,innodb引擎提供了一个参数innodb_autoinc_lock_mode,默认的值为1。在讨论新的增长方式之前我们需要对自增长实现方式分类:
    1.INSERT-LIKE:指所有的插入语句,比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。
    2.Simple insert:指在插入前就能确定插入行数的语句,包括INSERT、REPLACE,不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。
    3.Bulk inserts:指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
    4.Mixed-mode inserts:指其中一部分是子增长的,有一部分是确定的。
    现在有SIMPLE INSERT、BULK INSERTS、MIXED-MODE INSERTS三种类型的INSERT语句,有AUTO-inc locking(最早的)和轻量级互斥量的自增长两种auto—increment锁。
    1.innodb_高并发和大数目存储方案,为啥开采职员必供给打听数据库锁。autoinc_lock_mode=0 5.1.22之前的方式,也就是所有类型的insert都用AUTO-inc locking。
    2.innodb_autoinc_lock_mode=1 这个参数是5.1.22之后出现的也是之后的默认值,对于SIMPLE INSERT,使用轻量级互斥量的锁,对于BULK INSERT,使用AUTO-inc locking。
    3.innodb_autoinc_lock_mode=2 指不管什么情况都使用轻量级互斥的锁,效率最高,但是复制只能使用row-basereplication,因为statement-base replication会出现问题。
    另外就是innodb和myisam的一个区别,innodb下,自增长必须是索引,而且必须是索引的第一个列,不然会报错,myisam不会出现这个问题。

    在InnoDB中由于支持的是行级的锁,因此InnboDB锁的兼容性可以扩展如下: . | IX |IS|X|S ---|---|---|---|--- IX| 兼容 | 兼容|冲突|冲突 IS| 兼容 | 兼容|冲突|兼容 X| 冲突 | 冲突 | 冲突 | 冲突 S| 冲突 | 兼容|冲突|兼容

     

    6.2.5、外键和锁

    外键主要用于引用完整性的约束检查。innodb中,对于一个外键列,如果没有显示的对这个列加索引,innodb就自动的对其加一个索引。

    2.3.3 自增长锁

    innodb锁模式互斥

    6.3、锁的算法

    1.Record Lock,单行记录上的锁,锁住索引记录。
    2.GapLock,间隙锁能锁定一个范围,但不包括记录本身如 < 6 时,依然可以插入6。
    3.Next-KeyLock:Gap Lock Record Lock,锁定一个范围并且锁定记录本身,如 < 6,插入6时会被阻塞。
    在REPEATABLE READ模式下 Next-KeyLock算法是默认的行记录锁定算法。

    自增长锁是一种特殊的表锁机制,提升并发插入性能。对于这个锁有几个特点:

    新葡亰496net 18.png)

    6.4、锁问题

    新葡亰496net,本来锁问题会导致的是更新丢失、幻读、脏读、不可重复读,但是innodb作者却只写出了三种问题,可能是幻读通过innodb Next-key Lock解决了,作者就没有提及。这几个锁问题对应事务隔离的4个安全级别:
    READ UNCOMMITTED(事务隔离最低的级别,有事务隔离就能解决更新丢失,但是存在脏读的问题)。
    READ COMMITED(ORACLE和SQL SERVER默认的隔离级别,解决了脏读,但是一个事务多次读取的内容不同,出现了不可重复读的问题)。
    READ REPEATABLE(可重复读,innodb引擎的默认事务隔离级别,解决了不可重复读的问题,但是产生了幻读,innodb通过Next-key lock解决了幻读)。
    SERIALIZABLE(可串行话,通过强制事务排序解决幻读问题,会降低性能)总的看来innodb默认的 READ REPEATABLE是非常棒的。

    在sql执行完就释放锁,并不是事务执行完。

    新葡亰496net 19

    6.5、阻塞

    innodb中需要其他事务的锁释放它锁占用的资源,这个时候就会发生锁等待,这就是阻塞。innodb引擎有两个相关参数:innodb_lock_wait_timeout 用来设定等待的时间,默认是50秒,这是一个动态参数,可以随时调整;innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作,默认是OFF,代表不回滚,这是一个静态参数。

    对于Insert...select大数据量插入会影响插入性能,因为会阻塞另外一个事务执行。

     

    6.6、死锁

    死锁会产生阻塞,所以可以通过6.5的参数,让超时的阻塞回滚。还有就是开发的时候,每个事务对表,字段,行的操作,都是顺序的,这样可以很大程度上避免死锁。

    自增算法可以配置。

    数据库加锁操作

    在MySQL5.1.2版本之后,有了很多优化,可以根据不同的模式来进行调整自增加锁的方式。小明看到了这里打开了自己的MySQL发现是5.7之后,于是便输入了下面的语句,获取到当前锁的模式:

     

    mysql> show variables like 'innodb_autoinc_lock_mode';

    一般的select语句不加任何锁,也不会被任何事物锁阻塞

    -------------------------- ------- | Variable_name | Value |

    读的隔离性由MVCC确保

    -------------------------- ------- | innodb_autoinc_lock_mode | 2 |

     

    -------------------------- ------- 1 row in set

    undo log 用来帮助事务回滚及MVCC(多版本并发控制 ,即select时可以使用行数据的快照,而不用等待锁资源)

    在MySQL中innodb_autoinc_lock_mode有3种配置模式:0、1、2,分别对应”传统模式”, “连续模式”, “交错模式”。

     

    传统模式:也就是我们最上面的使用表锁。

    S锁

    连续模式:对于插入的时候可以确定行数的使用互斥量,对于不能确定行数的使用表锁的模式。

      手动:select * from tb_test lock in share mode;

    交错模式:所有的都使用互斥量,为什么叫交错模式呢,有可能在批量插入时自增值不是连续的,当然一般来说如果不看重自增值连续一般选择这个模式,性能是最好的。

      自动:insert前

    2.4InnoDB锁算法

     

    小明已经了解到了在InnoDB中有哪些锁类型,但是如何去使用这些锁,还是得靠锁算法。

    X锁

    2.4.1 记录锁(Record-Lock)

       手动:

    记录锁是锁住记录的,这里要说明的是这里锁住的是索引记录,而不是我们真正的数据记录。

    select *  from tb_test   for update;
    

    如果锁的是非主键索引,会在自己的索引上面加锁之后然后再去主键上面加锁锁住.

       自动:update,delete 前

    如果没有表上没有索引,则会使用隐藏的主键索引进行加锁。

     

    如果要锁的列没有索引,则会进行全表记录加锁。

    线上环境中:

    2.4.2 间隙锁

    新葡亰496net 20.png)

    间隙锁顾名思义锁间隙,不锁记录。锁间隙的意思就是锁定某一个范围,间隙锁又叫gap锁,其不会阻塞其他的gap锁,但是会阻塞插入间隙锁,这也是用来防止幻读的关键。

    新葡亰496net 21

    新葡亰496net 22

     

    2.4.3 next-key锁

    锁等待时间:innodb_lock_wait_timeout

    这个锁本质是记录锁加上gap锁。在RR隔离级别下,Innodb对于行的扫描锁定都是使用此算法,但是如果查询扫描中有唯一索引会退化成只使用记录锁。为什么呢? 因为唯一索引能确定行数,而其他索引不能确定行数,有可能在其他事务中会再次添加这个索引的数据会造成幻读。

     

    这里也说明了为什么Mysql可以在RR级别下解决幻读。

    mysql>show global variables like "%wait%"
    

    2.4.4 插入意向锁

     

    插入意向锁Mysql官方对其的解释:

    innodb 行锁

    An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.


    可以看出插入意向锁是在插入的时候产生的,在多个事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。

     

    这里要说明的是如果有间隙锁了,插入意向锁会被阻塞。

    通过索引项加锁实现

    2.5 MVCC

    • 只有条件走索引才能实现行级锁                    a)
    • 索引上有重复值,可能锁住多个记录              b)
    • 查询有多个索引可以走,可以对不同索引加锁   c)
    • 是否对索引加锁实际上取决于Mysql执行计划

    MVCC,多版本并发控制技术。在InnoDB中,在每一行记录的后面增加两个隐藏列,记录创建版本号和删除版本号。通过版本号和行锁,从而提高数据库系统并发性能。

     

    在MVCC中,对于读操作可以分为两种读:

    自增主键做条件更新,性能做好;

    快照读:读取的历史数据,简单的select语句,不加锁,MVCC实现可重复读,使用的是MVCC机制读取undo中的已经提交的数据。所以它的读取是非阻塞的。

     

    当前读:需要加锁的语句,update,insert,delete,select...for update等等都是当前读。

    通过索引项加锁实现的例子:

    在RR隔离级别下的快照读,不是以begin事务开始的时间点作为snapshot建立时间点,而是以第一条select语句的时间点作为snapshot建立的时间点。以后的select都会读取当前时间点的快照值。

    a) 只有,有条件走索引才能实现行级锁

    在RC隔离级别下每次快照读均会创建新的快照。

     

    具体的原理是通过每行会有两个隐藏的字段一个是用来记录当前事务,一个是用来记录回滚的指向Undolog。利用undolog就可以读取到之前的快照,不需要单独开辟空间记录。

    mysql> show create table t2G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    mysql> select * from t2;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    2 |
    |    1 |    3 |
     ------ ------ 
    
    此时A连接 在b =2 时加 写锁;
    mysql> select * from t2 where b =2 for update;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    2 |
     ------ ------ 
    而此时再B连接中再对b=3,加写锁时,失败;
    mysql> select * from t2 where b=3 for update;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    

    3.加锁分析

    说明,表中没有索引时,innodb将对整个表加锁,而不能体现行锁的特性;

    小明到这里,已经学习很多mysql锁有关的基础知识,所以决定自己创建一个表搞下实验。首先创建了一个简单的用户表:

     

    CREATE TABLE `user` ( `id` int unsignedNOTNULLAUTO_INCREMENT, `name` varchar CHARACTERSETutf8mb4 DEFAULT NULL, `comment` varchar CHARACTERSETutf8 DEFAULT NULL, PRIMARY KEY , KEY `index_name` ENGINE=InnoDBAUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4COLLATE=utf8mb4_bin;

     

    然后插入了几条实验数据:

     b)  索引上有重复值,可能锁住多个记录 

    insert user select 20,333,333;insert user select 25,555,555;insert user select 20,999,999;

     

    数据库事务隔离选择了RR

    mysql> show create table t2G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      KEY `a` (`a`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    mysql> select * from t2;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    2 |
    |    1 |    3 |
    |    2 |    9 |
     ------ ------ 
    
    在A连接中,在a=1,b=2处加一个写锁;实际上 是在a=1这个索引上加的锁
    mysql> select * from t2 where a=1 and b=2 for update;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    2 |
     ------ ------ 
    1 row in set (0.00 sec)
    
    在B连接中,在a=1 and b=3处加写锁失败,因都是a=1这个索引,而A中已经对a=1这个索引的行加过了锁;
    mysql> select * from t2 where a =1 and b=3 for update;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    此时B连接是可以对 a=2 and b =9 这一行中,在a=2 这个索引上加锁的;
    mysql> select * from t2 where a=2 and b =9 for update ;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    2 |    9 |
     ------ ------ 
    

    3.1 实验1

    注意

    小明开启了两个事务,进行实验1.

    行锁升级成表锁:

    新葡亰496net 23

    mysql> select * from t2 where  b =9 for update ;
    

    小明开启了两个事务并输入了上面的语句,发现事务B居然出现了超时,小明看了一下自己明明是对name

    555这一行进行的加锁,为什么我想插入name=556给我阻塞了。于是小明打开命令行输入:

    select*frominformation_schema.INNODB_LOCKS

    发现在事务A中给555加了Next-key锁,事务B插入的时候会首先进行插入意向锁的插入,于是得出下面结论:

    新葡亰496net 24

    可以看见事务B由于间隙锁和插入意向锁的冲突,导致了阻塞。

    3.2 实验2

    小明发现上面查询条件用的是普通的非唯一索引,于是小明就试了一下主键索引:

    新葡亰496net 25

    Records: 1 Duplicates: 0 Warnings: 0

    居然发现事务B并没有发生阻塞,哎这个是咋回事呢,小明有点疑惑,按照实验1的套路应该会被阻塞啊,因为25-30之间会有间隙锁。于是小明又祭出了命令行,发现只加了X记录锁。原来是因为唯一索引会降级记录锁,这么做的理由是:非唯一索引加next-key锁由于不能确定明确的行数有可能其他事务在你查询的过程中,再次添加这个索引的数据,导致隔离性遭到破坏,也就是幻读。唯一索引由于明确了唯一的数据行,所以不需要添加间隙锁解决幻读。

    新葡亰496net 26

    3.3 实验3

    上面测试了主键索引,非唯一索引,这里还有个字段是没有索引,如果对其加锁会出现什么呢? 时间点|事务A | 事务B ---|---|--- 1|begin; | 2|select * from user where comment = '555' for update; | begin; 3| | insert user select 26,'666','666'; 4| | ERROR 1205 : Lock wait timeout exceeded; try restarting transaction 5| | insert user select 31,'3131','3131'; 6| | ERROR 1205 : Lock wait timeout exceeded; try restarting transaction 7| | insert user select 10,'100','100'; 8| | ERROR 1205 : Lock wait timeout exceeded; try restarting transaction 小明一看哎哟我去,这个咋回事呢,咋不管是用实验1非间隙锁范围的数据,还是用间隙锁里面的数据都不行,难道是加了表锁吗?

    的确,如果用没有索引的数据,其会对所有聚簇索引上都加上next-key锁。

    新葡亰496net 27

    所以大家平常开发的时候如果对查询条件没有索引的,一定进行一致性读,也就是加锁读,会导致全表加上索引,会导致其他事务全部阻塞,数据库基本会处于不可用状态。

    4.回到事故

    4.1 死锁

    小明做完实验之后总算是了解清楚了加锁的一些基本套路,但是之前线上出现的死锁又是什么东西呢?

    死锁:是指两个或两个以上的事务在执行过程中,因争夺资源而造成的一种互相等待的现象。说明有等待才会有死锁,解决死锁可以通过去掉等待,比如回滚事务。

    解决死锁的两个办法:

    等待超时:当某一个事务等待超时之后回滚该事务,另外一个事务就可以执行了,但是这样做效率较低,会出现等待时间,还有个问题是如果这个事务所占的权重较大,已经更新了很多数据了,但是被回滚了,就会导致资源浪费。

    等待图(wait-for-graph): 等待图用来描述事务之间的等待关系,当这个图如果出现回路如下:

    新葡亰496net 28

    就出现回滚,通常来说InnoDB会选择回滚权重较小的事务,也就是undo较小的事务。

    4.2 线上问题

    小明到这里,基本需要的基本功都有了,于是在自己的本地表中开始复现这个问题: 时间点|事务A | 事务B ---|---|--- 1|begin; | begin; 2|delete from user where name = '777'; | delete from user where name = '666'; 3| insert user select 27,'777','777';| insert user select 26,'666','666'; 4| ERROR 1213 : Deadlock found when trying to get lock; try restarting transaction| Query OK, 1 row affected (14.32 sec) Records: 1 Duplicates: 0 Warnings: 0

    可以看见事务A出现被回滚了,而事务B成功执行。 具体每个时间点发生了什么呢?

    时间点2:事务A删除name = '777'的数据,需要对777这个索引加上next-Key锁,但是其不存在,所以只对555-999之间加间隙锁,同理事务B也对555-999之间加间隙锁。间隙锁之间是兼容的。

    时间点3:事务A,执行Insert操作,首先插入意向锁,但是555-999之间有间隙锁,由于插入意向锁和间隙锁冲突,事务A阻塞,等待事务B释放间隙锁。事务B同理,等待事务A释放间隙锁。于是出现了A->B,B->A回路等待。

    时间点4:事务管理器选择回滚事务A,事务B插入操作执行成功。

    新葡亰496net 29

    4.3 修复BUG

    这个问题总算是被小明找到了,就是因为间隙锁,现在需要解决这个问题,这个问题的原因是出现了间隙锁,那就来去掉他吧:

    方案一:隔离级别降级为RC,在RC级别下不会加入间隙锁,所以就不会出现毛病了,但是在RC级别下会出现幻读,可提交读都破坏隔离性的毛病,所以这个方案不行。

    方案二:隔离级别升级为可序列化,小明经过测试后发现不会出现这个问题,但是在可序列化级别下,性能会较低,会出现较多的锁等待,同样的也不考虑。

    方案三:修改代码逻辑,不要直接删,改成每个数据由业务逻辑去判断哪些是更新,哪些是删除,那些是添加,这个工作量稍大,小明写这个直接删除的逻辑就是为了不做这些复杂的事的,所以这个方案先不考虑。

    方案四:较少的修改代码逻辑,在删除之前,可以通过快照查询,如果查询没有结果,则直接插入,如果有通过主键进行删除,在之前第三节实验2中,通过唯一索引会降级为记录锁,所以不存在间隙锁。

    经过考虑小明选择了第四种,马上进行了修复,然后上线观察验证,发现现在已经不会出现这个Bug了,这下小明总算能睡个安稳觉了。

    4.4 如何防止死锁

    小明通过基础的学习和平常的经验总结了如下几点:

    以固定的顺序访问表和行。交叉访问更容易造成事务等待回路。

    尽量避免大事务,占有的资源锁越多,越容易出现死锁。建议拆成小事务。

    降低隔离级别。如果业务允许(上面4.3也分析了,某些业务并不能允许),将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。

    为表添加合理的索引。防止没有索引出现表锁,出现的死锁的概率会突增。

    欢迎工作一到五年的Java工程师朋友们加入Java程序员开发: 854393687

    群内提供免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!

    这句对本意在b=9这行加索引,b又没有加索引,所以这是对整个表加锁;因为没有指定a =2,所以mysql找不到a这个索引的;

     

    c)  查询有多个索引可以走,可以对不同索引加锁

     

    mysql> show create table t2G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      KEY `a` (`a`),
      KEY `b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    mysql> select * from t2;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    2 |
    |    1 |    3 |
    |    2 |    9 |
     ------ ------ 
    在A连接中对 a=1 and b=2 加锁;
    mysql> select * from t2 where a =1 and b =2  for update;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    2 |
     ------ ------ 
    
    此时B连接中对a =1 and b=3 ,也是可以加锁的;这是因为mysql 可以从a=1这个索引来加锁,也可以对b=3加锁;
    所以就与上面b)中只能对a=1索引来加锁 区别开来;
    
    mysql> select * from t2 where a =1 and b =3  for update;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |    1 |    3 |
     ------ ------ 
    

     

    innodb的gap lock 间隙锁

     

    gap lock消灭幻读

         innodb消灭幻读仅仅为了确保 statement模式replicate的主从一致性

     

    小心gap lock

     

    自增主键做条件更新,性能最好;

     

    gap lock 间隙锁 解释:

     

    mysql> select * from t2;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |   20 |    2 |
    |   24 |    4 |
    |   27 |    5 |
    |   27 |    6 |
    |   27 |    8 |
    |   30 |    6 |
    |   31 |    4 |
    |   32 |    9 |
     ------ ------ 
    8 rows in set (0.00 sec)
    
    在A连接中给a=27 加锁(a 是有索引的)
    mysql> select * from t2 where a=27 for update;
     ------ ------ 
    | a    | b    |
     ------ ------ 
    |   27 |    5 |
    |   27 |    6 |
    |   27 |    8 |
     ------ ------ 
    3 rows in set (0.00 sec)
    

     

    此时隔离等级是Repeatable  Read,标准的是可以出现幻读现象的,

    即在B连接中 insert into t2 values(27,3),是可以插入成功的,而且B连接提交后,A连接是可以查看到增加的,27,3这一行的。

     

    而innodb 通过间隙锁是的B连接中  insert into t2 values(27,3) 插入失败,来消灭幻读的出现。

    但是这种方法是有局限的,它会将a=24--29(30-1)中间的任何数都锁住,所以才叫间隙锁;

     

    B连接中则只能插入不在这个区间的数据;

     

    锁升级


     

    • 由一句单独的sql语句在一个对象上持有的锁的数量超过了阈值,默认这个阈值为5000.值得注意的是,如果是不同对象,则不会发生锁升级。
    • 锁资源占用的内存超过了激活内存的40%时就会发生锁升级

     

    innodb不存在锁升级的问题。因为其不是根据每个记录来产生行锁的,相反,其根据每个事务访问的每个页对锁进行管理的,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常都是一致的。

     

    简单说innodb根据页进行加锁,并采用位图方式,定位到行的,所需资源较小。

    例子:

     新葡亰496net 30

     

    新葡亰496net 31.png)

    死锁


     新葡亰496net 32

     

    新葡亰496net 33.png)

     

    死锁数据库自动解决

         数据库挑选冲突事务中回滚代价较小的事务回滚

     

    死锁预防

         单表死锁可以根据批量更新表的更新条件排序

         可能冲突的跨表事务尽量避免并发

         尽量缩短事务长度

     

    排查死锁:

    • 了解触发死锁的sql所在事务的上下文
    • 根据上下文语句加锁的范围来分析存在争用的记录
    • 通常改善死锁的主要方法:

            --对同一表的操作根据加锁条件进行排序

            --拆分长事务

     

    业务逻辑加锁


     

         业务流程中的悲观锁(开始的时候,在所有记录加锁,直到最后释放;而乐观锁开始不加锁,只是在最后提交中看提交有没有成功,没成功返回给应用程序)

     

         悲观锁开始就给所有记录加锁,一般等所有业务流程完成,才释放锁;因此会对并发性能有一定的影响;

     

    如何缩短锁的时间?

    1)开始的时候读取要修改的数据,amount(金额)

    2)做业务流程

    3)在update时,加锁且判断,现在的amount和开始的amount是否为一个值,如果是,说明这期间amount为改变,则更新;如果amount值改了,则不更新,交给业务来判断该怎么做。

     

    这样仅是在update这个语句加锁,大大的缩短的锁的时间提高了并发性;

     

    但是如果业务十分的繁忙,amount的值在不断改变,此时这个update 就不断的失败,整个事务就不断的失败,反而影响了 性能。那么该如何做呢?

     

    在开始的时候不读取数据,等到要提交的时候读取并加锁提交;

     

     总结


     

    •  更新丢失
    •  innodb意向锁:
      • 表锁
      • 自动施加、自动释放
      • 为了揭示事务下一行将被请求的锁类型
    •  S锁:in share mode

    •  X锁:for update
    •  innodb行锁特点:
      • 只有条件走索引才能实现行锁
      • 索引上有重复值可能锁住多个记录
      • 查询有多个索引可以走,可以对不同索引加锁
    •  gap lock:间隙锁,消灭幻读

    •  死锁解决:数据库挑回滚代价较小的事务回滚;
    •  死锁预防:
      • 单表,更新条件排序
      • 避免跨表事务,缩短事务长度
    •  锁升级:

      • 单独sql语句在单个对象的锁数量超过阙值
      • 锁资源占用的内存超过了激活内存的40%;
    •  innodb根据页进行加锁,并采用位图方式,定位到行的,所需资源较小

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:高并发和大数目存储方案,为啥开采职员必供给

    关键词:

上一篇:新葡亰496net:增删改查,电话薄设计

下一篇:没有了