Mysq事务

1. 概述

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能
就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。

为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题

2. 事务属性ACID

• 原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
• 一致性(Consistent) ：事务开始和完成时,数据都必须保持一致状态 （数据的完整性）
• 隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的独立环境执行
• 持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持

并发事务处理带来的问题

• 更新丢失(Lost Update)或脏写
  • 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 （最后的更新覆盖了由其他事务所做的更新 ）
• 脏读（Dirty Reads）
  • ​ 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态 ；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”
  • 事务A读取到了事务B已经修改但尚未提交的数据
• 不可重读（Non-Repeatable Reads）
  • 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改
    变、或某些记录已经被删除了！
  • 事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
• 幻读（Phantom Reads）
  • 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数
    据，这种现象就称为“幻读”
  • 事务A读取到了事务B提交的新增数据，不符合隔离性

3. 事务隔离级别

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的

常看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation='REPEATABLE-READ';
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

4. 锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
  • 读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
  • 读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
• 从对数据操作的粒度分，分为表锁和行锁
  • 表锁
    • 每次操作锁住整张表。开销小，加锁快；不会出现死锁
    • 锁定粒度大，发生锁冲突的概率最高 ，并发度最低
    • 一般用在整表数据迁移的场景
    • 手动增加表锁 ：lock table 表名称 read(write),表名称2 read(write);
    • 查看表上加过的锁 ：show open tables;
    • 删除表锁 ：unlock tables;
  • 行锁：
    • 开销大，加锁慢
    • 会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
    • InnoDB与MYISAM的最大不同有两点：
      InnoDB支持事务（TRANSACTION）
      InnoDB支持行级锁
• 间隙锁
  • 间隙锁，锁的就是两个值之间的空隙
  • 间隙锁是在可重复读隔离级别下才会生
• 临键锁
  • 行锁与间隙锁的组合

注意：

​ 锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁

5.案例分析

读未提交（读到未提交的数据）

set tx_isolation='read-uncommitted';

读已提交
set tx_isolation='read-committed';

可重复读

set tx_isolation='repeatable-read';

可串行化

set tx_isolation='repeatable-read';

**行锁分析 **

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:系统启动后到现在总共等待的次数

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

‐‐ 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
‐‐ 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
‐‐ 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
‐‐ 查看锁等待详细信息
show engine innodb status\G;

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