Mysql事务

事务相关命令

• 查询事务隔离级别

  select version();
  show variables like ‘%engine%’;
  show global variables like “tx_isolation”; //查询事务隔离级别
  

• 查看及设置事务自动开启或关闭
  
  关闭是 set session autocommit = off;

• 手动开启一个事务

  begin;
  	update XXXX;
  commit/rollback;
  

  

事务并发的三大问题

• 脏读
  在一个事务中读取到另外一个事务未提交的数据，造成的前后两次数据读取不一致
  

• 不可重复读
  在一个事务中读取到另外一个事务提交过的数据，造成的前后两次数据读取不一致
  

• 幻影读
  在一个事务中读取到另外一个事务提交过的insert数据，造成的前后两次数据读取不一致
  

事务四种隔离级别

SQL92 ANSI/ISO标准： http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
MySQL默认的事务隔离级别是RR级别（可重复读）

• Read Uncommitted（未提交读） --未解决并发问题
  事务未提交对其他事务也是可见的，脏读（dirty read）

• Read Committed（提交读） --解决脏读问题
  一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读（nonrepeatable read）

• Repeatable Read (可重复读) --解决不可重复读问题
  在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题

• Serializable（串行化） --解决所有问题
  最高的隔离级别，通过强制事务的串行执行

Innodb引擎对隔离级别的支持程度(在RR级别解决了幻读问题)，如下:

理解表锁，行锁

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

表锁与行锁的区别：

1. 锁定粒度：表锁 > 行锁
2. 加锁效率：表锁 > 行锁
3. 冲突概率：表锁 > 行锁
4. 并发性能：表锁 < 行锁

InnoDB存储引擎支持行锁和表锁（另类的行锁）

Innodb锁的类型

共享锁(行锁): Shared Locks
排它锁(行锁): Exclusive Locks
意向锁共享锁(表锁): IntentionShared Locks
意向锁排它锁(表锁):Intention Exclusive Locks
自增锁:AUTO-INC Locks

共享锁（Shared Locks）VS 排它锁（Exclusive Locks）

共享锁

• 又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改;

• 加锁释锁方式:

  select * from users WHERE id=1 LOCK IN SHARE MODE;
  commit/rollback //释放锁
  

排他锁

• 又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁(共享锁、排他锁)，只有该获取了排他锁的事务是可以对数据行进行读取和修改，(其他事务要读取数据可来自于快照)(问题: 排它锁与数据快照之间是如何配合使用的？)

• 加锁释锁方式:

  delete / update / insert 默认加上X锁
  SELECT * FROM table_name WHERE ... FOR UPDATE
  commit/rollback //释放锁
  

意向共享锁（IS）VS 意向排它锁（IX）

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁

• 意向共享锁(IS)
  表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁， 意向共享锁之间是可以相互兼容的

• 意向排它锁(IX)
  表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁， 意向排它锁之间是可以相互兼容的

• 意义:
  当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁

自增锁AUTO-INC Locks

针对自增列自增长的一个特殊的表级别锁

show variables like 'innodb_autoinc_lock_mode’; //默认取值1，代表连续，事务未提交ID永久丢失

InnoDB行锁到底锁了什么？

• 简述
  (1) InnoDB的行锁是通过给索引上的索引项加锁来实现的。
  (2) 只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB 将使用表锁(锁住索引的所有记录)
  (3) 给表加锁:

  lock tables xx read/write;
  

• 举例描述

  • 创建三张表
    (1) 不使用索引 —> t1
    
    (2) 主键索引 —> t2
    
    (3) 唯一索引 —> t3
    

• 三张表数据完全相同
  

• 对三张表分别执行相同的SQL

  begin; 
  select * from t1/t2/t3 where id = 1 for update;
  

  (1) t1表锁住的是，数据库给它生成的全部的默认主键(因为做了全表扫描全部锁住，相当于锁住了整张表)
  (2) t2和t3表锁住的是，主键索引
  

• 对t3表执行SQL

  begin; 
  select * from t3 where name = 4 for update;
  

  (1) 先锁住辅助索引(name 的唯一索引)
  (2) 再锁住name索引指向的主键索引
  

记录锁（Record）& 间隙锁（Gap）& 临健锁（Next-key）

• 临健锁（next-key）
  
  (1) 锁住索引的记录+区间(左开右闭)
  (2) 当sql执行按照索引进行数据的检索时,查询条件为范围查找(between and、<、>等)并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间(左开右闭)
  (3) InnoDB的引擎会用1,4,7,10（根据索引字段的关键字去划分区间，这个例子中索引字段是id,关键字是1,4,7,10，id是不自增长的）把索引划分为5个区间，都是左开右闭区间，如果搜索过程是type=range，即通过范围查询而且走了索引，还命中了数据，会锁住命中数据的区间以及下一个区间。
  (4) 为什么innodb选择临健锁作为行锁的默认算法？
  是为了解决幻读的问题，因为B+树是从左到右，从小到大排列的，把命中数据的区间以及相邻区间锁住，就插不进数据了。

• 间隙锁（Gap）
  
  (1) 锁住数据不存在的区间(左开右开)
  (2) 当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为 Gap locks，锁住索引不存在的区间(左开右开)
  (3) 注意:

  • 第一个事务执行 begin;select * from t3 where id = 6 for update; 会锁住(4,7)
  • 第二个事务执行 insert into t2(id, name) values (5,’5’); 会被阻塞住
  • 如果第二个事务执行 begin;select * from t3 where id = 6 for update; 不会被阻塞住，会查出空数据。因为mysql认为阻塞住没有什么实际意义

• 记录锁（Record）
  
  (1) 锁住具体的索引项
  (2) 当sql执行按照唯一性(Primary key、Unique key)索引进行数据的检索时，查询条件等值匹配且查询的数据是存在的，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项
  (3) 如果索引不是唯一索引或主键索引，而是一个普通索引且查询条件是等值匹配，还命中了数据，这时会锁住（1,7）开区间。(问题: 普通索引与数据是一对多的关系，它的索引原理是怎样的呢？)

InnoDB解决并发问题的原理

• 利用锁怎么解决脏读
  
  脏读的主要原因是在数据修改的时候允许其他人读取这个数据，如果在修改这条数据时加上X锁（排它锁，写锁），其他事务就读不到这条数据了，解决了脏读。

• 利用锁怎么解决不可重复读的问题
  
  不可重复读是因为第一次查询没有把数据锁定，如果加上S锁（共享锁，读锁），其他事务就不能进行修改了。

• 利用锁怎么解决幻读的问题
  
  幻读，一般是查询范围的时候会出现，加临键锁。

死锁

• 死锁产生的原因
  (1) 多个并发事务（2个或者以上）；
  (2) 每个事务都持有锁（或者是已经在等待锁）;
  (3) 每个事务都需要再继续持有锁；
  (4) 事务之间产生加锁的循环等待，形成死锁。

• 死锁的避免
  (1) 类似的业务逻辑以固定的顺序访问表和行。
  (2) 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
  (3) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
  (4) 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择
  (5) 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁(或者说是表锁)