MySQL Principles

37 阅读7分钟

MySQL Principles

数据库的常用语言

本节为数据库的基本原理

1 常用关键词

主键:数据库中一行算是一个记录,一列是一个属性,主键是其中的一个属性,这个属性的特殊之处在于它的值可以唯一标识表中的每一行

事务:对数据库进行一次操作的指令,它通常是好几条单独的指令组成的指令集。单独写一个SQL命令算是一个事务,或者用start transaction;开头,用commit;结尾,中间可以包含一组SQL命令集。

ACID:数据库事务具有4个核心属性,①原子性=Atomicity,事务里面的指令要么全部执行,要么全部不执行,不存在执行到一半的情况,如果真的执行到一半宕机了,那么就要回滚。②一致性=Consistency,在这次事务执行前,数据库已经满足了各种范式和约束,那么这次事务执行以后,数据库也得满足这些范式和约束。③隔离性=Isolation,数据库事务通常是并发进行,隔离性要求这些并发进行的事务互不打扰,并发的最终结果应该和串行进行取得的最终结果是相同的。④持久性=Duration,事务一旦执行了,那么相关的更改就永远被保留了,即使是发生系统故障也不能丢。

事务隔离级别:对应ACID的Isolation特性,既然事务是并发执行的,那么可以指定他的隔离级别。先看事务并发执行通常导致的几个问题

脏读,不可重复读,幻读,丢失更新

Read Uncommited: 可以理解成没有隔离级别,实际中很少采用这种隔离级别,user1和user2开启两个并行事务,他们各自的更改,即使是没有提交的更改,都可以实时被对方获取到。

Read Commited: 可以解决脏读(解决脏读等同于同步解决脏写),①解决脏读:初始X=1,user1在写入X=2,user2在读的时候只看user1提交的那个时刻,提交之前读出来就是X=1,提交以后读出来就是X=2,也就是说,数据库不允许读取未经提交的数据。②解决脏写,user2是不允许同时和user1对X进行写入操作,user2只可以等到user1的写入完成以后才可以继续操作,中间这段时间,user2一直处于等待状态

Repeatable Read: 和RC的情况做一个对比,RC中多个事务并发进行的时候,user2读取user1的更改是按照commit这个时刻来进行区分的,这个时刻前user2读取user1的更改是读取不到的,这就存在了一个灰度时间间隔,就是user1进行update以后并且在commit之前的这一段时间,在这段时间间隔,user1已经更改了对应的值,但是从user2看来还没有更改,这就容易出问题,比如某人的银行账户,如果在这段灰度时间间隔查询,就造成了一个误解,明明已经更新了,但是却没有显示出来,这就有了新的隔离级别Repeatable Read,它可以保证在user2读取时,用到的是这一时刻最新的更新。

Serialization: 串行读取,所有的事务都串行进行,可以解决所有的并行问题,隔离级别最高但是性能太差

MySQL InnoDB引擎默认使用RR隔离级别

levelup.gitconnected.com/understandi…

术语描述
(1)脏读事务A读取到一条记录,拿着这条记录去做业务逻辑,事务B在事务A拿到数据后进行回滚操作,这样事务A拿到的数据就是错的,术语称之为脏数据。
(2)不可重复读事务A两次读取同一条记录,但是得到两个不同的结果。原因是在两次读取的时间间隔内,有其他事务对这条记录进行了update操作。
(3)幻读事务A两次读取,得到的记录的条数是不一样的。原因是在两次读取的时间间隔内,有其他事务对这条记录进行了insert/ delete操作。其实和不可重复读本质上是一样的,都是其他事务对数据表进行了更改。
(4)丢失更新两条事务操作同一条记录,事务A的更新被事务B覆盖。例如,初始X=5,事务A,B的操作均为X-1,那么正确的值应该为X=3,实际事务A的更新被事务B覆盖后得到结果X=4。

对应的四个隔离级别

级别解决问题解决方法
Read Uncommited不解决任何问题
Read Commited解决(1)脏读以事务的commit为节点,事务能读取到的数据都是其他事务commit以后的,这样就不存在事务A读取以后事务B又回滚的问题了,因为只有事务Bcommit了以后事务A才能读取到事务B的修改
Repeatable Read解决(2)不可重复读(3)幻读见下文详细解释
Serialization解决所有问题所有事务串行运行,虽然隔离级别高,但是效率极差

关于可重复读的思考

解决脏读的本质是限制事务未被提交的改动不能被读取,这是符合一般的逻辑的,事务没被提交,随时有可能做出改动,因此也就不能被直接读取。

解决丢失更新是限制在某一个事务对数据库进行写操作的时候,其他事务不能进行写操作,可以进行读操作,这个是一种非常严格的隔离级别,可以用Serialization来解决,但为了避免过大的性能消耗通常是业务方自行用锁的方式实现。

但是夹在中间的不可重复读和幻读就非常尴尬, 很难从定义上理解它到底是什么意思,为什么要限制两次读取的行信息和行数是完全相同的,所以不如从具体业务实例中理解不可重复读和幻读的含义和RR的用处。

具体实例,一个事务中包含两个对账服务,第一次对账消费条目数,第二次对账消费总金额,那么如果在第一次对账和第二次对账中间客户又发生了一次消费,那么明显用RC的隔离级别处理这次事务是失败的,用RR对两次都按可以得到第一次对账的那个表,也就是中间发生的消费都不计入,这次失误就是成功的。

总结:不可重复读和幻读的意思都是一样的,合并一起处理,就是在一个事务中存在两个不同时刻的读操作,要求这两个读操作读到的数据是一致的,这并不是要求在这两个读操作的间隔其他事务不能写了,读到的不是实时的数据也没关系,只要两次读取的内容相同即可

RR解决不可重复读和幻读的方法:MVCC,说起来又非常长的故事了

以上隔离级别逐级严格,Serialization虽然隔离级别高,可以解决所有问题,但是效率极差,因此也不会被采用,常用的隔离级别是Repeatable Read级别,例如MySQL的InnoDB引擎默认就是这个级别。

新的问题,采用Repeatable Read级别隔离需要业务方自己处理丢失更新的问题

范式