MySQL 技术内幕：InnoDB存储引擎——读书笔记（七）

hfutwhy

    只有当实现本身会增加开销时，行级锁才会增加开销。InnoDB中一个锁和多个锁的开销是相同的，因此不像SQL Server那样有锁升级的现象。

    锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

    InnoDB中两种行级锁：共享锁(S Lock)和排他锁(X Lock)。同时，为了支持不同粒度上进行加锁操作，InnoDB提供了两种表级别的意向锁：意向共享锁(IS Lock，获得某几行的共享锁)、意向排他锁(IX Lock，获得某几行的排他锁)。意向锁不会阻塞除全表扫描以外的任何请求。

    SHOW ENGINE INNODB STATUS\G查看锁请求信息。Locks rec but not gap代表锁住的是一个索引，而不是一个范围。在InnoDB Plugin中，information_schema库增加了INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS三张表，可以监控当前事务并分析可能存在的锁的问题。

1. SELECT r.trx_id waitin_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query 
2. FROM information_schema.innodb_lock_waits w
   
3. inner join information_schema.innodb_trx b on b.trx_id=w.blocking_trx_id
   
4. inner join information_schema.innodb_trx t on t.trx_id=w.request_trx_id

一致性的非锁定读操作

    InnoDB通过多版本控制(multi versioning)的方式读取当前执行时间 数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作，此时读取操作会去读取行的一个快照数据（UNDO数据段），而不是等待行上锁的释放。（读取方式跟隔离级别有关）

MVCC（Multi Version Concurrency Control）

    在Read Committed和Repeatable Read下，InnoDB使用非锁定的一致性读，但是对于快照数据的定义不同。Read Committed下，读取被锁定行的最新一份快照数据；而Repeatable级别下，非一致性读总是读取事务开始时的行数据版本。

    SELECT … FOR UPDATE 对读取的行记录加一个X锁，其他事务在这些行上加锁时会被阻塞

    SELECT … LOCK IN SHARE MODE 对读取的行记录加S锁，其他事务可以继续加S锁，但是X锁则会被阻塞。

（一个事务内，事务提交，锁被释放。所以这种语句前应加BEGIN、START TRANSACTION或SET AUTOCOMMIT=0）

    InnoDB对每个含自增值的表都有一个自增计数器(auto-increment counter)，当有插入操作时，计数器会被初始化获取自增值（SELECT MAX(auto_inc_col) FROM t FOR UPDATE），即AUTO-INC Locking方式，完成对自增值插入的SQL语句后立即释放，而不是等COMMIT/ROLLBACK后才释放。

    但是从5.1.22开始，InnoDB提供了一个参数innodb_autoinc_lock_mode可以控制自增插入的方式，实现了一种轻量级互斥量的自增实现机制提高了性能。

    InnoDB下，自增列必须是索引，而且是索引的第一个列，否则报错。

    对于外键值的插入或更新，首先需要查询父表中的记录，此时会使用SELECT … LOCK IN SHARE MODE方式在父表上加S锁。若此时父表上有X锁，则子表上的操作会被阻塞。

三种锁的算法

• Record Lock：单行记录上的锁。锁住索引记录。
• Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。（如 < 6 时，依然可以插入6）
• Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。（如 < 6，插入6时会被阻塞）

    在REPEATABLE READ模式下，Next-Key Lock算法是默认的行记录锁定算法。

    锁提高了并发，但是却会带来问题：丢失更新、脏读（不同事务下，读取其他事务未提交的数据。产生的条件是READ UNCOMMITTED级别）和不可重复读。MySQL通过READ REPEATABLE和Nex-Key Lock算法，避免了不可重复读的现象。

    Innodb_lock_wait_timeout控制等待的时间(默认50秒)，innodb_rollback_on_timeout用来确定是否在等待超时时对进行中的事务进行回滚操作(默认OFF，不回滚，不可动态调整)

    对于死锁，InnoDB有后台的锁监控线程，检测到死锁后，会回滚一个事务。

注：如若涉及版权或者其他问题，请联系本人。