mysql事物

mysql事物

隔离性的实现

• 共享锁
• 排他锁
• 独占锁
• 临键锁
• 间隙锁：根据当前id值判断，（-%,1),(1,3),(3,4)
• 自增锁
• 意向锁

持久性：事物提交，对数据库的更新是持久的，wal日志，write ahead log 预写日志

一致性是事物的根本追求

##原子性的实现

undo log（逻辑日志）、rdo log->innodb

bin log->mysql

原子性实现原理通过undo log，操作数据前会将数据放到undo log，如果用户恢复了操作则通过undo log将数据恢复到事物开始之前的状态。

当用户进行delete时，undo log中会记录一条insert语句。相当于手动维持事物（理解为这样，当然不是这样做的）。

字盘按照页的单元读取

物理日志表示某一页

逻辑日志表示物理日志上的某一条记录（相反的sql语句）

mvcc ->multipart version concurrcy controll

持久性原理

redo log ，回滚日志

写数据->缓存->磁盘

在事物提交前，只需要将redo log持久化，不关心数据是否持久化

调用commit后，每秒写入进程内存log buffer（与mysql进程相关），系统调用写入磁盘

调用commit后，每次提交写入os buffer，系统调用写入磁盘

调用commit后，每次提交时写入os（此时与mysql无关了），每秒系统调用写入磁盘

ib_logfile存储的redo log。

redo log为了应对高并发下，去数据库中查到数据，写入数据十分耗时，所以先写到一个log中。等到空闲再汇总数据，循环写log。

数据优先从内存中

一致性锁定读：根据隔离性有关，如果数据安全要求高则另一个读请求则会进行阻塞。

数据更新过程：

• 获取数据-查看数据是否在内存中-不在则查询到磁盘
• 更改数据
• 将新数据加载内存
• 数据保存至redo log（并没提交，准备阶段prepare阶段）
• 生成bin log
• 调用引擎提交事物，处于commit 状态，将redo改成commit状态（两阶段提交，为了保证redo 与 binlog数据一致性）

隔离级别

读取快照数据。

rc读取最新快照

rr读取事物开启之前的快照

脏读：一个事物可以读到另外一个事物中没有提交的数据（没有commit）

不可重复读：一个事物中读了两次，两次结果不一样，读到了别的事物提交的数据

将两个session开启为REPEATABLE READ，同时开启事务，再第一个事务中先select，然后在第二个事务里面update数据行，可以发现即使第二个事务已经commit，第一个事务再次select数据也还是没有改变，这就解决了不可重复读的问题。

我： 这里有个不同的地方就是在Mysql中，默认的不可重复读个隔离级别也解决了幻读的问题。

我： 从上面的演示中可以看出第一个事务中先select一个id=3的数据行，这条数据行是不存在的，返回Empty set，然后第二个事务中insert一条id=3的数据行并且commit，第一个事务中再次select的，数据也好是没有id=3的数据行。

幻读

幻读：一个事物insert了数据（未提交），另一个事物提交时会阻塞。当第一个事物提交时，第二个事物才会提交，但是显示主键重复（数据已有）

隔离级别设置越高性能越差

这个得从Mysq的锁说起，在Mysql中的锁可以分为分享锁/读锁（Shared Locks）**、排他锁/写锁（Exclusive Locks） 、间隙锁、行锁（Record Locks）、表锁。

在四个隔离级别中加锁肯定是要消耗性能的

而读未提交是没有加任何锁的，所以对于它来说也就是没有隔离的效果，所以它的性能也是最好的。

对于串行化加的是一把大锁，读的时候加共享锁，不能写，写的时候，家的是排它锁，阻塞其它事务的写入和读取，若是其它的事务长时间不能写入就会直接报超时，所以它的性能也是最差的，对于它来就没有什么并发性可言。

对于读提交和可重复读，他们俩的实现是兼顾解决数据问题，然后又要有一定的并发行，所以在实现上锁机制会比串行化优化很多，提高并发性，所以性能也会比较好

他们俩的底层实现采用的是MVCC（多版本并发控制）方式进行实现

mysql锁

共享锁是针对同一份数据，多个读操作可以同时进行，简单来说即读加锁，不能写并且可并行读；排他锁针对写操作，假如当前写操作没有完成，那么它会阻断其它的写锁和读锁，即写加锁，其它读写都阻塞

• 表锁
• 行锁

表锁:当前表只有一个用户能访问,表锁则锁的粒度大，加锁快，开销小，但是锁冲突的概率大，并发度低

行锁:当前行只有一个用户能访问,行锁锁定当前数据行，锁的粒度小，加锁慢，发生锁冲突的概率小，并发度高

mysql索引为b+树,需要读取磁盘块到内存中,有100万人访问数据库则要读取非常多次到磁盘块,所以很卡

mysaim适用于经常查询(表级锁,写锁优先级高,同时只能进行一种动作)

innodb适用于各种修改(InnoDB支持行锁并且支持事务)

共享锁,排他锁

mysql的表级锁有两种

• 表共享读锁
• 表独占写锁(排它锁)

myisam锁

myisam的表级锁,读共享操作会阻塞其他用户对同一表的写请求,只支持表锁

写操作,会阻塞其他用户对同一表的读请求

myisam在执行查询语句之前,会自动给涉及的所有表加读锁,

在执行更新操作前,会自动给涉及的表加写锁,写锁加上,其他用户就不能读了

myisam中存储引擎有一个系统变concurrent_insert

加入读锁后,当前session无法访问其他session加入的数据

innodb锁

• 共享锁
• 排它锁

共享锁:读锁,与myisam的读共享锁功能相同,粒度不同,myisam是表级锁,innodb是行级锁.对行施加了读共享锁后,其他线程可以读但是不可以改,多个事物共享一个读锁

排他锁,允许获取排他锁的线程进行更新数据,其他线程无法获取数据以及更新数据,innodb默认的更新语句都是加上排他锁的.普通select语句不会加锁,所以当一个事物使用更新语句并且自动加上了排他锁,则其他事物无法对这行数据使用更新语句.

innodb的行锁其实是通过给索引上的索引项加锁,只有通过索引查找数据时才会使用行级锁,否则也是表锁.for update表示排他锁,如果一个事物获取了这个行的排他锁,则其他事物无法获取该行的其他锁包括共享锁和排他锁.指一个数据行被加上排他锁后,其他事物无法再给他加上别的锁

innodb只会给仔索引上查询的数据加上行锁,不是根据索引查询的数据默认加表锁(经过实验)

排他查询实验:

BEGIN;
select * from user where id =1 for update;

SELECT * from `user` where id = 1 for update

第二条sql会阻塞住,锁等待,此时需要第一条sql进行commit才行

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

SELECT * from `user` where id = 1 lock in share mode

第一条sql给行加上了共享锁,则其他链接不允许给当前行加上除了共享锁以外的其他锁

innodb默认更新一句都是加上排他锁的,其他链接用普通查询时只能查询到修改之前的老数据,同时排他锁加上后就无法再加上其他锁

在and查询中,只要索引使用相同,如where id=1 and name=‘mc’ 和 where id = 1 and name=‘dd’.都需要等待锁

间隙锁

间隙锁则分为两种：Gap Locks和Next-Key Locks

总结(锁与事物的关系)

对于myisam表

• 共享读锁之间是兼容的,但是共享读锁与排他锁之间是互斥的,也就是说读写是串行的.
• 一定条件下,允许读写并发执行
• myisam默认写操作优先,可以设置
• 表锁粒度较大,读写又是串行,如果更新操作较多则最好使用innodb

对于innodb

• innodb的行锁是基于索引的,使用索引访问是进行行锁,否则进行表锁
• 锁的出现就是为了解决并发访问时,数据不一致的问题,如读共享锁,不会读取到其他用户已经提交的数据,行锁等

mvcc

而MVCC则引入了另外一种并发控制，它让读写操作互不阻塞，每一个写操作都会创建一个新版
本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回，由此解决了事务
的竞争条件

insert undo log

1. 是在 insert 操作中产生的 undo log。
2. 因为 insert 操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以 insert undo
   log 可以在事务提交后直接删除而不需要进行 purge 操作。

update undo log

1. 是 update 或 delete 操作中产生的 undo log
2. 因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此 update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作

InnoDB行记录有三个隐藏字段：

• rowid

• 事务号db_trx_id

• 回滚指针db_roll_ptr

其中db_trx_id表示最近修改的事务的id，db_roll_ptr指向回滚段中的undo log。

• trx_id ：每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给 trx_id 隐藏列。
• roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然
  后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
  • 1 刘备 undo_log_point(第五行)

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

[1.liubei (指针指向之前版本)->[1 zhaoyun -> […

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。所以设计InnoDB的大叔提出了一个ReadView的概念，这个ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见(读已提交,未提交,重复读)：

• 如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。

• 如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

• 如果被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同，我们来看一下。

READ COMMITTED — 每次读取数据前都生成一个ReadView

begin trxid=100

update 1 关羽

update 1 赵云

begin trxid=200

此时undolog里有刘备->关羽->赵云行记录指向了,用户执行select查询时,就会生成一个readview,里面包含了当前活跃的事物id,根据配置的隔离级别,返回可以查看的事物行记录

根据以上对比,这个活跃id为100,200.那么根据上述3个条件,id为100、200的都不能给当前事物看,只能看更早的刘备了

REPEATABLE READ —在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。

因为之前已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView中的m_ids列表就是[100, 200]。就算新事物提交了,也还是当成活跃事物