innodb事务隔离级别、MVCC

隔离级别#

未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

MVCC#

InnoDB是一个多版本的存储引擎:为了支持事务的一些特性诸如并发和回滚,它保持着被修改行的旧版本信息。这些信息被存储在一个被叫做回滚段(rollback segment)的表空间中。InnoDB在回滚段中用这些信息来执行undo操作,以此支持事务回滚。它也用这些信息来构造行的更早的版本,以此支持一致性读(快照读)。

在内部,InnoDB为数据库中存储的每一行添加三个隐藏字段。

DB_TRX_ID:表明插入或者修改这一行的最后一个事务的事务标识符。如何查看行事务ID

DB_ROLL_PTR:指向回滚段中的一个undo log记录,如果行被修改了,那么这个undo log记录包含的信息必须先于行修改被重新修改。

DB_ROW_ID:单调递增的行ID。如果InnoDB自动生成了一个聚集索引,那么这个索引包含行ID值,否则DB_ROW_ID列不会出现在任何索引中。

锁类型#

共享锁(读锁,S锁):若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放S锁。

排他锁(写锁,X锁):若事务T对数据对象A加上X锁,则只允许T读取和修改A,其他事务不能再对A加作何类型的锁,直到T释放A上的X锁。

意向共享锁(IS锁):事务T在对表中数据对象加S锁前,首先需要对该表加IS(或更强的IX)锁。

意向排他锁(IX锁):事务T在对表中的数据对象加X锁前,首先需要对该表加IX锁。

意向锁补充#
  • InnoDB 支持多粒度锁,特定场景下,行级锁可以与表级锁共存。
  • 意向锁之间互不排斥,但除了 IS 与 S 兼容外,意向锁会与 共享锁 / 排他锁 互斥。
  • IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突。
  • 意向锁在保证并发性的前提下,实现了行锁和表锁共存且满足事务隔离性的要求。

2pl 两阶段锁#

两段锁协议,先随便加,最后commit才能一起释放.

GAP锁有何用#

其实这个多出来的GAP锁,就是RR隔离级别,相对于RC隔离级别,不会出现幻读的关键。

确实,GAP锁锁住的位置,也不是记录本身,而是两条记录之间的GAP。

所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生

快照读 & 当前读#

快照读:就是select

1
select * from table ….;

当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。

1
2
3
4
5
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;

聊天讨论#

全快照、全当前,不幻读, 先快照,再当前,幻读

「 球球: 其实产生幻读的原因,就是以为读走了mvcc的副本, 」


读如果也加gap锁,就真的不会幻读了,但代价太大

「 宏伟: 张三账户余额有10000元;要转账给李四9000元,转给王五5000元;这两次转账肯定有一次因余额不足转账失败;如果在 rr 下,可重复读, 两次update 张三都会成功? 」


这个如果两个事务并发,都先读的1w元快照读,然后第1个事务直接update张三减钱, commit, 第2个事务 接着update张三减钱,都会成功,除非
1 用数据库乐观锁
2 自己搞个分布式锁,将读写操作原子化
3 用kafka让同一用户操作串行化

敏: 交易相关的就不要快照读了, 交易要算钱,select的时候就要锁,不然这条数据之后未必可用

总结#

RR级别下,通过MVCC, 解决了两个快照读的幻读问题,
通过next-key锁,解决了两个当前读的幻读问题,但是快照读后的当前读是会幻读的.

mysql创建100张表

使用存储过程,创建100张表

Innodb索引原理及其优化

一、目的

了解索引结构,explain方法,优化原理

二、索引名词

2.1 聚簇索引

1

  • 聚簇索引也称为聚集索引,聚类索引,簇集索引,聚簇索引的顺序就是数据的物理存储顺序。
  • 由于聚簇索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚簇索引。
  • Innodb聚簇索引根据每张表的主键构造一棵B+树,叶子节点中存放的即为整张表的行记录数据,也称为数据页。
  • 对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据

mysql技术内幕第二章、Innodb存储引擎

Innodb

Innodb体系结构

存储引擎结构图

InnoDB 存储引擎有多个内存块,这些内存块组成了一个大的内存池,主要负责如下工作:

  • 维护所有进程/线程需要访问的多个内部数据结构
  • 缓存磁盘上的数据, 方便快速读取, 同时在对磁盘文件修改之前进行缓存
  • 重做日志(redo log)缓冲

mysql技术内幕第一章、mysql体系结构和存储引擎

Mysql体系结构

mysql体系结构

MySQL由以下几部分组成:

  • 连接池组件。
  • 管理服务和工具组件。
  • SQL接口组件。
  • 查询分析器组件。
  • 优化器组件。
  • 缓冲(Cache)组件。
  • 插件式存储引擎。
  • 物理文件。

从图1-1还可以看出,MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管 理和服务支持,这些标准与存储引擎本身无关,可能是每个数据库系统本身都必需的,如SQL分析器和优化器等,而存储引擎是底层物理结构的实现,每个存储引 擎开发者都可以按照自己的意愿来进行开发。

注意:存储引擎是基于表的,而不是数据库。请牢牢记住图1-1所示的MySQL体系结构图,它对于你以后深入了解MySQL有极大的帮助

windows安装mysql zip包

1 . 先解压,修改my_default.ini 文件里 basedir 和 datadir 的路径

2 . 修改环境变量,将mysql的bin添加到path,

3 . 以管理员身份打开cmd 窗口

执行mysqld -install

4 . 将my_default.ini 复制一份到 bin ,改为my.ini ,执行

mysqld –initialize –user=mysql –console

则生成data文件夹,并生成root 账号的密码!!

aa

5 . 命令行下执行 net start mysql 开启mysql服务

6 . mysql -u root -p 登录

输入上图中的密码,即可登陆

7 . 修改root密码

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘root’);

ok

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×