Mysql数据库事务

事务的ACID

1. 原子性(Atomicity)：当前事务的操作要么同时成功，要么同时失败。原子性由undolog日志来实现。
2. 一致性(Consistent)：使用事务的最终目的，由其它3个特性以及业务代码正确逻辑来实现。
3. 隔离性(Isolation)：在事务并发执行时，他们内部的操作不能互相干扰。隔离性由MySQL的各种锁以及MVCC机制来实现。
4. 持久性(Durable)：一旦提交了事务，它对数据库的改变就应该是永久性的。持久性由redolog日志来实现。

事务的隔离级别

Mysql的默认隔离级别是可重复读；Oracle的默认隔离级别是读已提交

隔离级别	脏读	不可重复读	幻读
读未提交	Y	Y	Y
读已提交	N	Y	Y
可重复读	N	N	Y
串行化	N	N	N

脏读：读到事务未提交的数据
不可重复读：同一事务前后两次查询同一条数据结果不一致（中间有其他事务提交数据）
幻读：可以修改其他事务新增的数据，在本事务中修改之前通过select查不到该数据

锁

1. 锁分类：
   1.1 从性能上分为乐观锁（用版本对比或CAS机制）和悲观锁，乐观锁适用于读多的场景，悲观锁适用于写多的场景。
   1.2 从对数据的操作粒度上分为：表锁、页锁（只有BDB存储引擎支持页锁）、行锁。
   1.3 从对数据库的操作类型分为：读锁、写锁和意向锁。读锁和写锁都是悲观锁。
2. 读锁（共享锁，S锁）：一般的select语句不会加锁，如果想加锁使用select…lock in share mode。读锁是共享的，多个事务可以同时读取同一资源，但是不允许其他事务修改。
   隔离级别为串行化时，读操作底层会自动加上读锁，其他隔离级别的读操作不会自动加读锁。
3. 写锁（排他锁，X锁）：insert,delete,update,select…for update 都会加写锁。写锁是排他的，会阻塞其他的写锁和读锁。
4. 意向锁（I锁）：针对表锁，主要为了提高加表锁的效率。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。
   意向锁主要分为：意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
   意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。
5. 表锁：锁住整张表，一般用在整张表数据做迁移的场景。
6. 行锁：每次锁住一行数据。InnoDB相对于MYISAM的最大不同有两点：InnoDB支持事务（TRANSACTION）、InnoDB支持行级锁。行锁其实是对列的索引加锁（做标记）（如果查询列没有索引或索引失效，RR隔离级别下行锁会升级为表锁，RC不会升级为表锁）。
7. 间隙锁（Gap Lock）：间隙锁是在可重复读隔离级别下才会生效。只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，这样就能防止其它事务在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。
8. 临界锁（(Next-key Lock）：行锁与间隙锁的组合。

锁相关分析

1. 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';
对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）

2. 查看INFORMATION_SCHEMA系统库锁相关数据表

--查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
--查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * fromINFORMATION_SCHEMA.INNODB_LOCKS;
--查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
‐‐释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
--查看锁等待详细信息
show engine innodb status;

MVCC（多版本并发控制）

1. 3个字段：rowid，trx_id(事务id)，roll_pointer(回滚指针)
2. 如果有主键，rowid用不着
3. trx_id: 修改当前数据的事务id
4. roll_pointer：指向当前数据的上一个版本，如果是insert语句指向回滚语句（undolog链）
5. read-view：一致性视图，这个视图由活跃事务id数组、最小活跃事务id以及已经生成的最大事务id组成。在RR隔离级别下，read-view仅在第一次查询时生成，后面都用这一个，即快照读。在RC隔离级别下，每次查询都会重新生成read-view，即当前读。写操作都是当前读。
6. 每次读操作，都会使用生成的read-view，从版本链中最新的数据开始回溯，使用可见性算法校验可以读取到版本链中的哪条数据，可见性算法如下：
   1.如果此条数据的trx_id<min_id，表示这个版本是已提交的事务生成的， 这个数据是可见的；
   2.如果此条数据的trx_id>max_id，表示这个版本是由将来启动的事务生成的，是不可见的；
   3.如果此条数据的min_id<=trx_id<=max_id，那就包括两种情况
   a.若trx_id在视图（活跃事务）数组中，表示这个版本是由还没提交的事务生成的，不可见；若trx_id就是当前自己的事务是可见的；
   b.若trx_id不在视图（活跃事务）数组中，表示这个版本是已经提交了的事务生成的，可见。

事务优化

1. 将查询等数据准备操作放到事务外
2. 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
3. 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
4. 更新等涉及加锁的操作尽可能放在事务靠后的位置
5. 能异步处理的尽量异步处理
6. 应用侧(业务代码)保证数据一致性，非事务执行–应用性能要求很高，且业务相对简单，不然容易出bug

锁优化

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能减少检索条件范围，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
5. 尽可能用低的事务隔离级别