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
- 是在 insert 操作中产生的 undo log。
- 因为 insert 操作的记录只对事务本身可见,对于其它事务此记录是不可见的,所以 insert undo
log 可以在事务提交后直接删除而不需要进行 purge 操作。
update undo log
- 是 update 或 delete 操作中产生的 undo log
- 因为会对已经存在的记录产生影响,为了提供 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]
。就算新事物提交了,也还是当成活跃事物