数据库基础——锁与事务

  一、锁（MyISAM 和 InnoDB）

　　1. MyISAM： 默认表级锁，不支持行锁。

　　  select  读锁为共享锁。可以同时执行多个读的操作

　　  update、insert、delete 等操作为写锁，写锁是排他锁。 

    手动加锁： lock tables table_name read/write;

    为select 添加排他锁： select ..................................   for update;

　　2. Innodb 默认行级锁，也支持表级锁。

  表级锁：

• 表读写锁

    读为共享锁，写为排他锁。 加表锁可以使用 lock tables/table table_name read/write;

    命令show open tables，可以查看对应表锁的个数。

• 意向锁

    这是InnoDB引擎实现的表级锁，是为了全表更新数据时或者加其他表锁时性能提升，避免全表遍历排查某行数据是否加了行锁，对加了行锁的表，加上意向锁标识；

　　　　意向锁分为意向共享锁(IS锁)和意向排他锁(IX锁)，意向锁之间不互相排斥，只是作为表锁的标识，如表中多行记录可以既有共享锁和排他锁，那么导致表的意向锁就会既有IS锁和IX锁，这种意向锁就是为了给表加锁时可以更快的判断能加哪种锁。

• 自增锁

    自增锁是一种特殊的表级别锁。它是专门针对AUTO_INCREMENT类型的列，对于这种列，如果表中新增数据时就会去持有自增锁。简言之，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

  行级锁：通过给索引的索引项加锁实现的，所以只有通过索引条件检索的数据，InnoDB才能加行锁，否则会对所有行或行间隙加锁，表现为使用了表锁。根据锁定范围分为记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁(Next-Key Locks)和插入意向锁(Insert Intention Locks)。按照功能仍可以分为共享锁（S）和排他锁（X）。

  innodb 会自动给insert、update、delete语句加X锁。但select不会加任何锁（非Serializable隔离等级），除非显式的加锁： select ...... lock in share mode  (S锁) | select ...... for update (X锁)

• 记录锁

    锁定索引中一条记录。其实锁住的是索引(如果是主键索引，就锁住主键索引，如果是辅助索引，则锁住辅助索引和主键索引)，如果检索记录没有用到索引，那么就会将表的所有聚簇索引记录都锁定，表现为表锁；

• 间隙锁 （Gap）

    是一种区间锁，锁住的是索引开区间，即不包括两端，也就是不包括索引记录本身；间隙锁可以防止幻读，即在当前事务增删改中，用到间隙锁后，其他事务便无法加上间隙记录的X锁，也就不能增删改间隙记录；

• 临键锁 （Next-Key） = 行锁  + Gap锁

    锁定索引区间为左开右闭区间的锁。在 RR和Serializable级别下，会在当前读的操作中使用临键锁。InnoDB会根据不同SQL操作对临键锁进行优化：

    当主键索引或唯一索引全部命中时临键锁会降级为记录锁，即锁住记录本身，而不是范围；

    当主键索引或唯一索引部分命中时，行锁+Cap锁

    当辅助索引等值查询且有记录时，临键锁会锁住等值记录的主键索引项和辅助索引项，同时会加间隙锁锁住辅助索引区间项

    当范围匹配时，就使用临键锁，即Record Lock + Gap Lock。

• 插入意向锁

    是一种Gap Lock，不是意向锁，在insert操作时产生；插入意向锁不会阻止任何锁，对插入的记录进行锁定，防止其他事务插入相同主键记录，而不同主键记录之间的插入互不影响。

二、事务

  1. 数据库事务的四大特性 ACID

• 原子性 （Atomic）：事务包含的所有操作，要么全部执行，要么全部失败回滚。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态指数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，事物之间应该互不干扰。
• 持久性（Durability）：一个事务一旦提交，它对数据库的修改应该是永久的。持久性意味着当系统或者介质发生故障时，确保已提交事务的更新不能丢失，即对已提交事务的更新能恢复。一旦一个事务被提交，DBMS（数据库管理系统）必须保证提供适当的冗余，使其耐得住系统的故障。所以持久性主要在于DBMS的恢复性能。例如 innoDB会将所有的数据库修改保存在二进制日志中。

  2. 事务相关的SQL语句

• show variables like 'transaction_isolation'/select @@transaction_isolation;     查看当前session的隔离级别
• start transaction    开启事务
• commit  提交
• rollback 回滚
• set session transaction isolation level (read uncommitted)/ (read uncommitted)/ (repeatable read )/ （serializable） 

  3. 事务隔离级别以及各级别下的并发访问问题

    3.1 事务隔离的4个级别

　　  （1）读未提交——read uncommitted：在事务过程中，可以读取其他事务未提交的数据。

　　  （2）读已提交——read committed： 在事务过程中，可以读取其他事务已提交的数据。

　　  （3）可重复读——repeatable read：可重复读，事务中多次读取，数据一致。

　　  （4）串行化——serializable：任何操作都会请求响应的锁。能否执行要看能否获取到操作行的锁。按照锁的兼容关系执行。

    3.2 常见的几种并发问题

    （1） 脏读： 事务A读取数据的过程中，能读取到其他事务修改但未提交的数据。  RC级别以上可以避免。

    （2）不可重复读： 事务A多次读取同一条数据，取得的数据结果不一致。 RR级别以上可以避免。

    （3）幻读： 当事务操作时，有其他事务插入或删除了数据，导致当前事务进行了错误操作。 serializable级别可以避免。

  4. 当前读与快照读

    对于RC和RR两种模式，在RC模式下，会读取最新的已提交数据。RR模式，会保证多次读取数据一致性，是否读取最新版本，要看建立快照的时间。

    当前读：select...lock in share mode, select ... for update, update, delete, insert。 即加锁的操作都可以视为当前读。读取的是记录的最新版本，而且保证其它并发事务不能修改当前记录。 

   为什么 update，delete， insert等操作都称为当前读呢？？？以update为例，原因如下图所示：

   快照读： 不加锁的非阻塞读，（非serializable级别的）select

    InnoDB下数据行里除了数据字段还有一些隐藏字段，例如DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID等，这些就是实现快照读的关键。

•    DB_TRX_ID: 最后一次对本行做修改的事务ID，每处理一个事务，其值自动加1
•    DATA_ROLL_PTR 指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针
•    DB_ROW_ID，当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，否则聚集索引中不包括这个值，这个用于索引当中
•    DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除，真正意义的删除是在commit的时候

   当我们对一行数据进行修改时，innoDB会更新DB_TRX_ID 事务ID，并将更新前的行保存到undo日志中，然后DB_ROLL_PTR指向undo日志中的这一行。流程如下所示：

初始插入行：

事务1修改：

事务2修改：

  在RR级别下，事务开始时的第一次快照读会创建一个快照Read View 将系统中其他活跃的事务记录下来，此后当再次快照读的时候，仍使用同一个Read View。 RC级别下每次快照读都重新建立快照，这就是为什么RC模式读取的是最新提交的数据。

  InnoDB在RR与Serializable级别下如何避免幻读？ 临键锁！！！！临键锁会锁住记录本身和附近的区间，以防止幻读。例如A字段为int类型，且为普通索引，如下图所示。当事务1删除 A= 10的数据时，innoDB会锁定（7，10] 和（10，12]的区间。这时事务2想要插入A=9的数据，就会阻塞。