6 锁

数据库区别与文件系统的一个关键特性

6.1 什么是锁

锁机制用于管理对共享资源的并发访问。

Innodb引擎不仅仅是在行级别上使用锁，在其他方面也会使用。例如：操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素

6.2 InnoDB存储引擎中的锁

6.2.1 锁类型

两种标准的行级锁：

  1. 共享锁（S lock）：允许事务读一行数据
  2. 排他锁（X lock）：允许事务删除或更新一行数据

mysql ---- innodb3 （锁、事务） - 图1

在InnoDB Plugin之前，只能通过SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS等命令来查看当前的数据库请求，然后在判断当前事务中锁的情况。

新版本中在INFORMATION_SCHEMA架构下添加了INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS三张表。通过这三张表可以简单的监控当前的事务并分析锁的问题。

  1. INNODB_TRX字段
    • trx_id：事务id
    • Trx_state：当前事务的状态
    • trx_started: 事务开启时间
    • trx_requested_lock_id：等待事务的锁ID
    • trx_wait_started:事务等待开始的时间
    • trx_weight:事务权重，反应了一个事务修改和锁住的行数
    • trx_mysql_thread_id： mysql中的线程ID，show processlist 显示的结果
    • trx_query: 事务运行的sql，实际中有时会显示null
  2. INNODB_LOCKS的字段
    • lock_id：锁的id
    • lock_trx_id：事务的id
    • lock_mode：锁的模式
    • lock_type：锁的类型，行锁或者是表锁
    • lock_table：要枷锁的表
    • lock_space：Innodb引擎表空间的id
    • lock_page：被锁住的页的数量，若为表锁，则为null
    • lock_rec：被锁主的行的数量，若为表锁，泽伟null
    • lock_data：被锁住行的主键，欧维表锁，则为null
  3. INNODB_LOCK_WAITS的字段
    • requesting_trx_id：申请锁资源的事务id
    • requesting_lock_id：申请的锁的id
    • block_trx_id：阻塞的事务id
    • block_lock_id：阻塞的锁的id

6.2.2 一致性的非锁定读操作

非锁定读大大提高了读取的并发性，是Innodb引擎中默认的读取方式，即读取不会占用和等待表上的锁。

一致性的非锁定读（consistent nonlocking read）是指InnoDB引擎通过多版本控制(multi versioning)的方式来读取当前执行时间数据库中的数据。如果读取的数据正在执行DELETE、UPDATE操作，这是操作并不会等待锁的释放，而是会去读取行的一个快照数据。

mysql ---- innodb3 （锁、事务） - 图2

上图展示了Innodb引擎的一致性非锁定读（不需要等待X锁的释放）。快照数据是指该行数据之前版本的数据，该实现是通过Undo段实现的。而Undo用来在事务回滚数据，因此快照数据本身没有额外的开销（此外读取快照数据不需要上锁）

  • 注意：
    在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取的都是一致性读。
    在上图得知，快照数据并不是只有一个版本，一个行可能有不止一个快照数据（多版本技术）。由此带来的并发控制称为多版本并发控制（Multi Version Concurrency Control, MVCC）。
    • Read Committed
      在读已提交事务隔离级别下，对于快照数据，总是读取被锁定行的最新的一份快照数据。
    • Repeatable Read
      在可重复度事务隔离级别下，对于快照数据，总是读取事务开始时行的快照数据

上述情况的例子：

  1. -- 回话A：
  2. begin; -- 开启事务
  3. select * from table where id = 1; -- 此时查出id=1的数据   （没有提交事务）
  6. -- 会话B：（此时A没有结束事务）
  7. begin;
  8. update table set id = 3 where id = 1; -- 将id改为3  （事务同样没有提交）

经过上述步骤，此时的会话B给行加了一个X锁， 如果此时再次回到会话A中读取数据，根据Innodb引擎的特性，在Read Committed和Repeatable Read事务隔离级别下使用非锁定一致性读。
在上述两个隔离级别下，会话A中仍然能够查到id为1的数据（因为此时只有一份快照数据，因此不管哪个事务隔离级别，结果都一样）。
然后回到会话B中，执行commit，提交事务，然后在回到会话A中，执行查询，此时两种隔离级别的查询结构就不同了，
对于 ReadCommitted 事务隔离级别来说，他总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新的一个快照（fresh snapshot）。在上述情况下，会话 B 已经提交，所以 Read Committed事务隔离级别下，执行 sql select * from table where id = 1是查不到数据的
对于 Repeatable Read事务隔离级别，总是读取事务开始的行数据，因此执行 sqlselect * from table where id = 1仍然可以查到 id 为1的数据。

  1.     -- 查询当前事务隔离级别
  2.     select @@tx_isolation;

对于 Read Committed事务隔离级别来说，其实是违背了事务的 ACID 中的 I特性（隔离性）

6.2.3 SELECT .. FOR UPDATE & SELECT … LOCK IN SHARE MODE

在上边说过，默认情况下，innodb 引擎的 select 操作使用一致性非锁定读。 但在某些情况下，需要对读进行加锁操作。

读加锁操作的两种方式：

  1. SELECT … FOR UPDATE
    对读的行记录加一个 X 锁（排它锁），其他事务想在这些行上加任何锁都会被阻塞
    对于一致性非锁定读，此时让然可以进行读取
  2. SELECT … LOCK IN SHARE MODE
    对读取操作加一个 S 锁（共享锁），其他事务可以向被锁定的行加 S 锁，但是 对于X 锁，会被阻塞

另外，上述两种方式，必须在一个事务中，当事务提交了，锁也就失效了（因为在使用上边两个语句时，是要加上 BEGIN、START TRANSACTION、SET AUTOCOMMIT=0）

6.2.4 自增长和锁

对于含有自增长值的表都有一个自增长计数器（auto-increment counter）。当插入时，这个计数器会被初始化，执行以下 sql 来获取值：

  1. select MAX(auto_inc_col) from t for update;

插入操作会根据这个值加1给自增长列。即 AUTO-INC Locking，这种锁定机制其实是一种表锁机制，为了提高性能，锁不是在一个事务完成之后才释放，而是在完成对自增长值的插入后立即释放。（这种方式对于大批量数据会影响效率）

在 mysql5.1.22版本开始，提供了一种轻量级互斥量的自增长实现机制，参数 innodb_autoinc_lock_mode默认值为1。（不同的参数对于不同的插入有不同的影响）

插入方式分类：

  1. INSERT-like：指所有的插入语句，例如 INSERT、REPLACT、INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA等
  2. Simple inserts：指在插入之前就能确定插入的行数，如：INSERT、REPLACE，不包括INSERT ...ON DUPLICATE KEY REPLACE
  3. Bulk inserts：指插入之前不能确定行数，如INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA
  4. Mixed-mode inserts：指插入数据有一部分指是自增长的，有一部分指是确定的，如：INSERT INTO t1(c1,c2)VALUES (1,'A'),(NULL,'B'),(4,'C'),(NULL,'D');也可以指INSERT ...ON DUPLICATE KEY REPLACE这类 sql

innodb_autoinc_lock_mode有3个可选指：

  1. 0，mysql5.1.22版本之前的自增长实现方式
  2. 1，默认值，对于 Simple inserts 会使用互斥量产生自增长值，对于 Bulk Inserts 仍然使用 AUTO-INC Locking 的方式
  3. 2，对于所有的 INSERT-like 自增长值的产生都是通过互斥量，而不是 AUTO-INC Locking （性能最高的方式）
    innodb_autoinc_lock_mode=2，会有两个问题：一是并发插入，会导致自增长值不是连续的，二是，对于基于 Statement-Base Replication 的复制会出现问题（Row-Base Replication 没问题）

6.2.5 外键和锁

6.3 锁的算法

Innodb 引擎有3中行锁：

  1. Record Lock： 单个行记录上的锁（总是回去锁定索引的，如果没有索引，Innodb 引擎会使用隐式的主键来进行锁定）
  2. Gap Lock： 间隙锁，锁定一个范围，但是不包括记录本身
  3. Next-Key Lock：Gap Lock + Record Lock， 锁定一个范围，包括记录（Innodb 引擎对于行的查询都是基于这种算法，对于不同 sql 查询语句，可能设置共享Next-Key Lock 和排他的 Next-Key Lock）

例子：

  1. begin;
  2. select * from t where id < 5 lock in share mode;
  3. -- 在上述 情况下执行
  4. insert into t (id) values(3); -- 此时插入语句会阻塞
  5. -- 因为在 Next-Key Locking 算法下，锁定的是 id < 6的所有值，但是如果插入 id=9，是可以的
  7. -- 如果是查询指定 id 的值，是不会影响其他数据的插入的，因为 innodb 引擎会选择一个最小的算法模型

6.4 锁问题

丢失更新 脏读 不可重复读

6.4.1 丢失更新

两个查询，分别显示到两个页面，两个分别更新，第二次更新会直接覆盖第一次更新，导致了第一次更新丢失

解决：数据上加版本号，更新时对比版本号

6.4.2 脏读

是指读到了未提交的数据（这种情况只会发生在事务隔离级别设置为 Read Uncommitted的情况下）

6.4.3 不可重复读

多次读到的数据不同，发生在事务隔离级别为 Read Committed的情况下，违反了隔离性

不可重复读的问题是可以接受的，因此像 oracle、microsoft sql sever 的隔离级别都是读已提交

mysql 通过 Next-key Lock算法来避免不可重复读

6.5 阻塞

Innodb 引擎中，通过参数 innodb_lock_wait_timeout 来控制等待的时间（默认50秒），innodb_rollback_on_timeout用来设定是否在等待超时时对事物进行回滚操作（默认为 OFF，代表不回滚）

6.6 死锁

Innodb 存储引擎并不会回滚大部分的异常，但是死锁除外，当发生死锁时，Innodb 会马上回滚一个事务

6.7 锁升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。例如：数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。

Innodb 存储引擎中不存在锁升级的问题，1个锁和10000个锁时一样的，没有开销

7 事务