MySQL锁与事务

本文将对MySQL锁与事务相关做简要概述

1. MyISAM与InnoDB关于锁方面的区别

• MyISAM默认用的是表级锁，不支持行级锁

  即对前20000行数据进行查询的同时，无法对第20001行数据进行更新

  手动加读锁(也称共享锁)lock tables 表名 read;释放unlock tables;

  如果想对读操作上写锁(排他锁)查询语句 for update;读锁语句 lock in share mode

• InnoDB默认用的是行级锁，也支持表级锁

  对同一张表加读锁的同时可以对其他行进行写操作

  *InnoDB在sql没有用到索引时走的是表级锁

2. MyISAM与InnoDB的适用场景

MyISAM引擎的主键索引和辅助索引时独立的，而InnoDB辅助索引需要查两次，先查主键，再通过主键查数据

#因此MyISAM在纯检索(增删改较少)的场景中性能高于InnoDB

MyISAM适用场景

• 频繁执行全表count语句
• 对数据进行增删改频率不高，查询非常频繁
• 没有事务

InnoDB适用场景

• 数据增删改查都比较频繁
• 可靠性要求高，要求支持事务

数据库锁的分类

• 按锁的粒度划分，可分为表级锁、行级锁、页级锁
• 按锁级别划分，可分为共享锁、排他锁
• 按加锁方式划分，可分为自动锁、显式锁
• 按操作划分，可分为DML(操作表数据)锁、DDL(创建、更改表结构)锁
• 按使用方式划分，可分为乐观锁、悲观锁

3. 数据库事务

数据库事务的四大特性

ACID: 原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

事务并发访问引起的问题以及如何避免

• 更新丢失——mysql所有事务隔离级别在数据库层面均可避免

• 脏读——READ-COMMITTED(Oracle默认)事务隔离级别以上可避免

  是指一个事务读取到了另一个事务未提交的数据

  如何解决：READ-COMMITTED事务隔离级别以上可避免

  下面我们进行验证

  首先我们先使用下面的语句获取当前的事务隔离级别可知数据库的默认级别REPEATABLE-READ

  select @@tx_isolation;#查询隔离级别
  

  下面我们使用下面的语句将当前事务的隔离级别设置为可读未提交 READ-UNCOMMITTED(最低的事务隔离级别)

  set session transaction isolation level read uncommitted;#修改隔离界别为 可读未提交
  

  模拟步骤
  1. 首先我们新建两个控制台来模仿两个不同的事务并在两个控制台都执行下面的语句，并将事务隔离级别都改为READ_UNCOMMITTED
  2. 我们在控制台1中使用start transaction;来开始我们的事务
  3. 我们使用select * from learn_sql.learn_sql where id = 1;此时我们查询到的money为初值300.
  4. 此时我们使用update learn_sql.learn_sql set money = 400 where id = 1;执行过此命令之后我们再执行select * from learn_sql.learn_sql where id = 1;会发现此时的money已经改成了400.
  5. 我们使用控制台2使用select * from learn_sql.learn_sql where id = 1;查询可知此时的money已经变成了还未提交的400元。
  6.若此时控制台1未使用commit进行提交，而是rollback进行事务回滚，则此时就是发生了控制台2读取了未成功提交的数据。

  解决办法：使用下面的sql语句将事务隔离级别改为READ_COMMITTED，此时如果我们执行步骤4则会发现，控制台2获取的并不是400而是300。

  set session transaction isolation level read committed;#修改隔离界别为 只可读提交
  

• 不可重复读——REPEATABLE-READ事务隔离级别以上可避免

  事务1多次读取的数据不同。

  如何解决：REPEATABLE-READ事务隔离级别以上可避免

  下面我们开始模拟，首先我们先保证事务隔离级别为READ_COMMITTED

  模拟步骤
  1.首先我们在控制台1中使用start transaction;开始事务，此时我们使用select * from learn_sql.learn_sql where id = 1;可知money为300，然后我们使用update learn_sql.learn_sql set money = money + 100 where id = 1;
  2.此时我们使用控制台2中先使用start transaction;开始事务，再使用select * from learn_sql.learn_sql where id = 1;发现money为300。
  3.接着我们在控制台1使用commit;提交修改。这是我们控制台2中使用select * from learn_sql.learn_sql where id = 1;我们突然发现原来的300，突然变成了400.这就是发生了不可重复读的问题了，即多次查询结果不一致

  解决办法：我们使用下面的sql语句将事务隔离级别修改为REPEATABLE-READ

  set session transaction isolation level repeatable read;
  

  这时我们在步骤3中获取的money仍然为300，但是如果我们使用update learn_sql.learn_sql set money = money + 100 where id = 1;后再查询会发现money不是我们最后一步查询的300+100=400元，而是我们控制台1提交之后的400+100=500元，这就防止了在一次事务中多次读不一致，同时可以保证该事务提交结果正确。

• 幻读——SERIALIZABLE事务隔离级别可避免

  如何解决：SERIALIZABLE事务隔离级别可避免

  下面我们开始模拟，首先我们先保证事务隔离级别为READ_COMMITTED由于mysql在技术层面上避免了REPEATABLE-READ隔离级别下发生，但是理论上并不可避免

  模拟步骤
  1.首先我们在控制台1中使用start transaction;开始事务，此时我们使用select * from learn_sql.learn_sql;可得到一条数据。
  2.此时我们使用控制台2中先使用start transaction;开始事务，再使用insert into learn_sql.learn_sql (id,money);接着我们在控制台2使用commit;提交修改。
  3.接着我们在控制台1使用update learn_sql.learn_sql set money = 1000;我们会发现这个时候我们修改的语句条数为两句，这与我们之前查询的一条不同，这就是幻读。

  解决办法：把事务隔离级别修改为SERIALIZABLE 之后我们会发现在执行第二步是会被lock住无法操作，从而避免了幻读。

总结

innodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。如何做到保证数据是一致的（也就是一个事务，其内部读取对应某一个数据的时候，数据都是一样的），同时读取的数据是最新的数据。innodb提供了一个间隙锁的技术。也就是结合GAP锁与行锁，达到最终目的。当使用索引进行插入的时候，innodb会将当前的节点和上一个节点加锁。这样当进行select的时候，就不允许加x锁。那么在进行该事务的时候，读取的就是最新的数据。

快照读(snapshot read)

• 不加锁的非阻塞读

• 当前读(current read)

  • select … lock in share mode - select … lock in share mode
  • select … for update
  • insert
  • update
  • delete

  InnoDB怎样在可重复读的隔离级别避免的幻读？

• 表象：快照读(非阻塞读)

• 内在：next-key锁（行锁+gap锁）
  • 行锁
  • Gap锁
  • 对主键索引或者唯一索引时
    • 如说where条件全部命中，则不会用Gap锁，只会加记录锁
    • 如果where条件部分命中或者全不命中，则会加Gap锁
  • Gap锁会用在非唯一索引或者不走索引的当前读中

RC、RR级别下的InnoDB的非阻塞读如何实现

• 数据行里的DB_TRX_ ID（事务ID）、DB_ ROLL_PTR（回滚指针）、DB_ROW_ ID（行号）字段
• undo日志
  • 进行变更操作会产生undo日志，存储老版本数据
  • 分为insert undo log（存储insert日志，只在事务回滚时需要，事务提交后即可删除）和update undo log（存储delete和update日志，不仅回滚需要，快照读也需要，不可随便删除）

上图为将field2字段由12改为32。此时我们将原数据存出来undo log，我们将回滚指针指向undo log的原数据，同时DB_TRX_ ID加1。如果我们之后进行回滚，则从undo log中获取数据，进行回滚。

同样下图为两个undo log日志