2019/04/16 事务并发和隔离级别

mysql 查询，缓存，索引，都是和性能优化相关的内容
索引，为了提高用户查询速度，都会在相应字段上建立索引，
在哪些字段上建立索引，就是一些经常作为搜索条件的，比如where
但是也要看这个字段是否合适，比如字符太长，或者就两种可能性男女
在建立索引的字段上，尽量不要出现太重复的字段，最好像主键一样
主键索引在innodb存储引擎采用的是聚集索引
还有一个叫非聚集
聚簇索引和非聚簇索引区别在，
所谓聚合在一起，就是是否索引列和数据是放在一起存放的，当然索引是按照一定顺序存放的
数据和索引放在一起，那么数据就和索引排列的顺序是一致的，因此代表了，在数据库中，聚簇所以只能有一个
因为有了两个数据排放顺序到底跟哪个索引顺序一致，就有问题
聚集索引在一个数据库中只有一个，而在innodb数据库中，主键就是个聚集索引，也反过来证明，主键只有一个，
myisam数据库数据和索引是分开存放的，换句话说myisam是不支持聚集索引的
sqlserver oracle聚集索引都是主键索引，但是即使如此，myisam数据库中，主键索引排列次序，和数据排列次序也是一致的，虽然不在一起，
主键索引对应的还有二级索引或复合索引，这两个区别是
主键索引是在主键上建立的，最大的特点是在数据存放的次序和我们索引的排放顺序是否一致，一致就是主键索引
二级索引是在非主键上，数据存放的次序和我们索引的排放顺序不是一致，不一致就有索引的叶子节点，存放的内容就是当前的索引以及对应的主键索引的指针
简单说二级索引最底层的是叶子节点，存的就是对应的索引和对应的主键的位置，也就是可以通过索引找到主键，通过主键聚簇索引，通过聚簇索引找到具体数据所在
主键通过搜索条件性能应该比二级索引更好，所以搜索条件的时候尽量使用主键索引
因为主键索引，原来排放都是按照顺序，突然插入或者删除一条数据可能会影响周边的数据排放，所以一般在数据库排放，都会预留一部分空间，不会把数据撑满，这样后面的数据就不会整体往后移了

大部分情况在mysql下用的BTREE索引，极少数用到HASH

锁和事务是密切相关的
事务在进行工作的时候，就存在一个并发性的问题，并发性如何确保数据是一致的
通常有两种事务，一种是显性事务，一种是隐性事务

显性事务就是明确的规定，事务的开始，比如加一个标记说事务开始了，begin，后面+sql语句，最后end结束
mysql数据库中默认用的是隐性事务，隐性事务就是说，当我们执行一个update命令，默认就是一个事务，当你执行完，数据库的更改就发生了，也就是自动地完成对数据的更改，不能撤销

事实上事务执行的时候有两种情况

在事务没有结束之前可以撤销，rollback，commit确认提交了，数据就发生了更改
有时候这样的效率是比较低的，比如我现在一下子插入10条记录，每条都提交效率很低，不如写完一下子提交
通常mysql数据库中在执行大量代码的时候，可能会改成显示事务
sqlserver和mysql都是默认隐性事务，但是oracle默认是显示事务
oracle不写commit或者rollback就不会结束事务
写一个命令一直到commit才会提交

如何来修改显示或者隐式事务

autocommit是个变量
自动提交on 写一个提交一个

insert语句只要一回车，就会更新到数据库

意味着另外一个用户登录数据库将会看到数据库的更改

基于session级别设置的，能够成功，但是在另外的终端是无效的
意味着在session级别设置的只影响自己，如果要所有终端就加global

虽然是针对全局的，但是其他用户需要重新登录才生效，但是这样mysql服务重启就又丢失，想要永久生效还是得存磁盘

写到文件里，重启服务


出现问题，说明这么写是有问题的
没有-


既可以当选项又可以当变量
–代表参数，可以当mysqld的参数

重新登录后就是off了
、

还能看到修改

新开一个终端窗口，别的就看不到新添加的数据，没有真正把事务提交

就可以后悔，rollback，撤销

如果中间停电了，事务会写入磁盘吗

断电

当系统重启，这个事务就会撤销，因为这个事务时以显示事务开始的，结束就必须以明确显示事务结束，commit或者rollback
这条记录也就不会加进去

重启机器一看到事务不完整，机器就rollback取消
即使我时

即使时隐式的，先关闭选项

重新启动服务

再次去尝试开启事务

代表自动提交 的，但是也可以通过指令来明确事务什么时候开始，什么时候结束，就是start transaction

什么时候看到commit，或者rollback才会确定事务结束

再去插入数据
因为是开启的显示事务，是没有提交的

在别的终端上是看不到的

本人是可以看到的，这就叫事务的隔离性

这些数据还没有提交，这些数据就叫脏数据
只要没有提交之前都可以撤销

刚才的事务就没有了，看不到数据了

但是如果commit了就没有办法撤销了，属于直接进行修改了，对于数据库的更改也是永久性的

在其他终端就可以看到了

但是对于终端来讲很奇怪，上次还 没有，这次突然多了两条数据
这叫不可重复读，

在事务的执行的时候，还有个保存事务执行节点，
可以在执行事务的时候 ，定义一个位置，带来的好处就是，可以在标签的地方，选择性的撤销
如果没有标签，之前rollback是全部都撤销了

撤销a的位置，就是a前面的都保留着，撤销b的位置就是b的前面都保留着

就要用到savepoint，就可以定义状态标签或者位置

现在开启一个事务，对当前的事务进行修改，删除
定义一个保存节点，但是这边报错是因为sql的标识符是不能用数字的，必须要加字母

加字母就可以了，再删除一个28的

在定义一个28节点，再删除26的

现在都没有提交，就是数据库还未进行更改，比如可以回到28点


26就回来了

也可以定义到恢复27

但是再回到28就没有了

只有一次机会，再一次rollback，就全部撤销了

都撤销了

也可以把保存点删除，release释放


事务的隔离级别严重的影响了多个事务并发访问数据库的表现
隔离级别分成4个：
read uncommitted 读到未提交的数据，t1事务还未结束，t2就能看到了，脏数据


能查看提交的数据，未提交的看不到，叫不可重复读，
因为多个事务再修改的时候，导致你每次看到的数据都不一样
每次读取的数据都不一样


就有要求，至少再你这个事务里看到的数据是一样的
就有可重复读,保证一个事务里，数据是一致的

未提交的读事务将阻塞另外一个修改事务，就不会造成之前的问题，数据是最稳定的但是并发性能差


事务隔离级别的一些操作


参数和变量，全局和会话都支持，可以动态

目前的状态是重复读
修改成不可重复读，并且同时开启两个事务


同时开启两个事务后对数据库进行修改
脏数据还未提交，在自己的终端上可以看到记录


在另外一个终端也可以看到这个脏数据

最不严谨的级别

撤销之前的操作


修改事务级别，不可重复读

另外一个终端也修改

都开启一个事务

去增加一条记录

自己看没有问题

另外个终端就看不到

read commit只能看到提交的，现在提交，另外个终端就能看到了

退出当前事务

可重复读

两边都设置可重复读


都开启一个事务

再添加一条数据

没有提交另外的终端就看不到数据，提交了，另外的事务也看不到，这个是mysql的默认设置，可重复读

但是在新终端commit或者rollback、，就能看到数据了


原来终端添加一条数据

提交

但是另外一个终端因为是可重复读就看不到这个修改的数据

另外一个终端插入数据就会报错，看到没有30，想插入31，就报错，很奇怪

为了避免这种情况，就有可串行化

两边都修改成可串行化

都开启一个事务

未提交情况下读都可以

现在进行操作就卡住了，未提交的读操作，刚才是查了，但是还没提交退出，没有commit就会阻塞另外修改的事务

现在结束这个操作

超时

重新两边创建一次会话，重复上面操作就成功了

还在事务里修改

但是再另外终端里可以查到

重新操作，定义隔离级别,和开启事务
修改表


在另外一个终端是否能看到。就卡在这


这种就是和银行去使用，对数据要求较高
要修改还是加在文件里

注意这里变量名和选项不一致


现在做实验恢复到默认的时候

毫不相干，可以同时操作,t1事务可以操作 t2事务也可以操作

t1事务修改表2就需要等待t2事务提交

着个就变成你等我，我等你，死锁

死锁的解决访问，会自动发现，就需要牺牲一个事务
判断一个事务等的时间越长就去牺牲这个，认为牺牲最小
两边都开启一个事务

删除一个数据

另外一个终端去删除另外一个表

原来的终端对teacher表操作，卡在这的

后一个被牺牲了，
谁等待的时间短就牺牲哪个

原来的成功了

结束会话

死锁一般都能系统组件解决
但是开启两个会话在两边都修改30

什么时候另外提交的另外才能继续下去

现在又开启一个事务



刚才就锁住了，现在为什么可以，
刚才是都改30
现在是个30，一个31
innodb是行级锁
可以改善并发访问的效果