事务、锁、MVCC

1.什么是事务？

其实就是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；
事务是一组不可再分割的操作集合；

2.如何开启事务？

手工：begin/start transaction
设定事务是否自动开启（不建议）：set session autocommit = on/off
commit/rollback：事务提交或回滚

注：查看事务是否开启状态：show variables like ‘autocommit’，默认是自动提交的

jdbc：connection.setAutoCommit(boolean)
spring事务aop编程：expression=execution(com.liyz.dao..(..))

3.事务的四大特性ACID

A->原子性：最小工作单元，要么全部成功，要么全部失败
C->一致性：事务中操作的数据及状态是一致的，即写入资料的结果必须完全符合设定的规则，不会因为出现系统的意外等原因导致状态不一致
I->隔离性：一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）
D->持久性：事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

4.事务并发带来了哪些问题？

·脏读
·不可重复读
·幻读
·串行化

5.事务的隔离级别

·Read Uncommitted（读未提交）：未解决并发问题，脏读
·Read Committed（读已提交）：解决了脏读问题，未解决不可重复读问题，大多数数据库默认update/delete
·Repeatable Read（可重复读）：解决了不可重复读的问题，为解决幻读问题，mysql默认的 insert
·Serializable（串行化）：最高的隔离级别，解决了幻读问题，强制事务串行化执行

注：怎么实现的？其实是通过锁以及mvcc来实现事务的隔离级别的

查询隔离级别：select @@tx_isolation

6.锁：表锁、行锁

锁是用于管理不同事务对共享资源的并发访问

表锁与行锁的区别：
锁的粒度：表锁>行锁
加锁效率：表锁>行锁
冲突概率：表锁>行锁
并发性能：表锁<行锁

注：Innodb引擎支持行锁和表锁（另类的行锁）

7.Innodb锁类型

·共享锁（行锁）：Shared Locks ->Lock in share mode：只能读不能修改
·排它锁（行锁）：Exclusive Locks ->delete/insert/update/for update
·意向共享锁（表锁）：Intention Shared Locks ->可以认为是一个标志位，增加效率，是引擎自己拥有的，不需要人为去干预
·意向排它锁（表锁）：Intention Exclusive Locks ->可以认为是一个标志位，增加效率，是引擎自己拥有的，不需要人为去干预
·记录锁：index lock
·间隙锁：
·插入意向锁：
·自增锁：Auto-INC-Locks -> 特殊的表级别的锁 命令：show variables like ‘innodb_autoinc_lock_mode’
·空间索引的谓语锁：

行锁的算法：
记录锁（Record Locks）：锁住记录+区间（左开右闭）：当sql执行按照索引进行数据的检索时，查询条件为范围查找（between and、<、>等）并有数据命中则此时sql语句加上锁为next-key locks，锁住索引的记录+区间
间隙锁（Gap Locks）：锁住数据不存在的区间（左开右开）：当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时sql语句加上的锁即为Gap locks，锁住索引不存在的区间，只会在隔离级别“可重复读”的情况下存在
临键锁（Next-Key Locks）：锁住具体的索引项：当sql执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时sql语句加上的锁即为记录锁，锁住具体的索引项

举例：如果数据库中有数据主键为1、4、7、10的数据，那么分成的区间为(-∞,1],(1,4],(4,7],(7,10],(10,+∞]，而执行select from t where id >5 and id < 9 for update，则锁住的区间为(4,7],(7,10]；如果执行select from t where id > 4 and id < 6 for update，则锁住的区间为(4,7)；

注：Innodb默认选择临键锁作为锁的默认算法

那为什么mysql默认选择该算法呢？
防止幻读，因为B+tree本身是从小到大进行排列的

Innodb的行锁其实是通过索引上的索引项进行加锁来实现的
表锁命令：lock table xx read/write

RR与RC怎么选择？
1.RR的间隙锁会导致锁范围的扩大
2.条件列未使用到索引，RR会缩表，RC会锁行
3.RC的“半一致性”读会增加update操作的并发性

8.死锁

1.互斥
2.不可剥夺
3.形成等待环路

死锁发生的条件：多个并发事务
·每个事务都要持有锁
·每个事务都需要再继续持有锁
·事务之间产生加锁的循环等待，形成死锁

死锁的避免：
·控制访问顺序（表）
·数据排序（同表数据）
·申请足够级别的锁
·避免没有where条件（不命中索引）的操作
·大事务拆小
·使用等值查询而不是范围查询

怎么查看有没有死锁？
show processlist、show status like ‘%lock%’等等

9.MVCC：多版本并发控制

为了解决并发访问数据库，避免写操作的堵塞，引起读操作的并发问题。
那怎么实现的呢？
其实在每个表中，mysql默认加了两个隐藏的列：DB_TRX_ID（数据行的版本号）、DB_ROLL_PT（删除版本号）、ROW_ID（这里和它没有不关系）
DB_TRX_ID：（insert/update）数据行的版本号其实就是事务ID
DB_ROLL_PT：（delete）删除版本号就是当前事务ID

举例：
首先insert两条记录，可以把mysql事务id看成一个自增的id

然后delete了id=2的数据，假设这时候事务id=22

这时候我再进行修改update，id=1的数据，把age修改为60，假设事务id=33

假设这时候全局事务id=44，我们查询数据是这样的
查询规则：
1.查询数据行版本号早于当前事务版本的数据行（也就是说，行的系统版本号小于或者等于事务的系统版本号），可以确保事务读取的行，要么是在事务开始前已经存在，要么是事务自身插入或者修改过的
2.查找删除版本号要么为null，要么大于当前事务版本号记录，确保取出来的记录在事务开启前没有被删除

select from user == select from user where DB_TRX_ID <= 44 and (DB_ROLL_PT is null or DB_ROLL_PT > 44)
所以只能查出数据：

MVCC实现是通过 read view 一致性视图。
m_ids{}：当前系统活跃的事务id
min_trx_id{}：m_ids中的最小值
max_trx_id{}：系统分配下一个事务id
creator_trx_id{}：生成当前read view的事务id
如果trx_id=creator_trx_id，则可见
如果trx_id如果trx_id>max_trx_id，说明该事务是在创建事务id的之后事务，则不可见
如果min_trx/-id<=trx_id<=max_trx_id，则看他是或在m_ids里面，如果在，则不可见；如果不在则可见

10.Undo log

其实就是事务开始之前，把要操作的数据先备份到一个地方。
其实它的出现是为了实现事务的原子性而出现的产物：比如在执行过程中出现了异常或者执行了rollback，mysql可以利用Undo log中的备份，将数据恢复到事务开始之前的状态，所以就可以实现多版本的并发控制了，所以在事务为提交之前，Undo log保存了未提交之前的数据版本，所以Undo log中的数据可作为数据旧版本快照供其他并发事务进行快照读
流程：现将旧数据备份到Undo buffer，buffer在向log中写数据

11.当前读、快照读

快照读：sql读取的数据是快照版本数据，普通的select就是快照读
当前读：sql读取的数据是最新版本，利用锁机制保证的。update/delete/insert/select for update/select lock in share mode

12.Redo log

指事务中操作的任何数据，将最新的数据备份到一个地方
流程：将修改后的数据先备份到Redo buffer中，然后最终将提交的数据写入Redo log中，最后再同步到磁盘中。
Redo log是为了实现事务的持久性而出现的产物：防止在某个时间点发生故障，但是还未同步到磁盘中，重启时，会重新将Redo log中的数据写入磁盘中。
Redo log记录在{datadir}/ib_logfile1&ib_logfile2，可通过innodb_log_group_home_dir配置指定目录存储
指定Redo log日志文件组中的数量innodb_log_files_in_group，默认为2
指定Redo log每一个日志文件最大存储两innodb_log_file_size，默认48M
指定Redo log在cache/buffer中buffer池大小innodb_log_buffer_size，默认16M

Redo buffer持久化到Redo log的策略，innodb_flush_log_at_trx_commit：
取值0：每秒钟提交Redo buffer—>Redo log OS cache—->flush cache to disk【可能丢失一秒钟内的事务数据】
取值1：默认值，每次事务提交执行Redo buffer—>Redo log OS cache—->flush cache to disk【最安全，但是效率最差】
取值2：每次事务提交执行Redo buffer —> Redo log Os cache 再每一秒执行 —->flush cache to disk

redo log有一个两阶段的提交操作，第一个为 prepare，另一个为 commit，是为了保持redo log与bin log数据一致设计的
一般有四种情况：
redo log 无数据；undo log无书，则回滚
redo log prepare；undo log无数据，则回滚数据
redo log prepare；undo log有数据，则需要恢复数据，commit
redo log commit；undo log有数据，则数据正常

13.全局配置文件

mysql —help
可以找到mysql 的默认配置，统一配置文件
/etc/my.cnf/etc/mysql/my.cnf/usr/etc/my.cnf~/.my.cnf

有哪些常见的配置呢？
最大连接数：max_connections 查看 show variables like ‘max_connection
需要重启 service mysqlid restart’，有可能修改的值达到一定程度没法再大了，影响的有哪些呢？
linux的句柄数：ulimit -a —> open files
mysql本身的句柄限制：LimitNOFILE

port：端口
lower_case_table_names=0：表明区分大小写：0：区分；1：不区分
server_id=1：主从复制时需要，集群的唯一标识
tmp_table_size=16M：临时表的大小
transaction_isolation=REPEATABLE-READ：指定数据库事务隔离级别
ready-only=1：是由super权限才能写，否则只能读，主从复制

mysql内存参数配置
sort_buffer_size：connection排序缓冲区大小，建议256k（默认值）~2M之间：需要文件排序时，马上为connection分配内存大小
join_buffer_size：connection关联查询缓冲区大小，建议256k（默认值）~1M之间：用于关联查询，立马分配内存
连接占用内存：max_connection*(sort_buffer_size + join_buffer_size)

很关键的一个配置：innodb_buffer_pool_size（默认128M）：大的缓冲区可以减少磁盘IO，访问相同表数据可以提供性能
参考计算公式：innodb_buffer_pool_size=(总物理内存-系统运行所用-connection所用)*90%

14.buffer pool

buffer pool size=128m，默认
注：但是一般我们在生产环境中这个值要设置的大一点，可能占用数据内存的80%
这个内存回收算法是什么？
LRU Algorithm
在mysql中把buffer pool分成了两个区域，一个热数据区 new subList（5/8），一个冷数据区 old subList（3/8）；
当获取一个新数据的时候会现在冷数据的head中，随着访问的次数提高会在热数据区往上升

change buffer是buffer pool的一部分，默认占用1/4，是负责（insert、update、delete），但是是有限制的，是非唯一索引的数据修改