mysql 锁

• table-level locking（表级锁）
• row-level locking（行级锁）
• page-level locking（页级锁）
1、auto-inc锁
2、全表更新、全索引更新
3、使用SR事务隔离级别
1、record lock（行/记录锁）
2、gap lock（间隙锁）
3、next-key lock （record lock + gap lock）
MyISAM表的锁
• 读锁，LOCK TABLE USER READ，自身只读，不能写；其他线程仍可读，不能写。多个线程都可提交read lock
• 写锁，LOCK TABLE USER [LOW_PRIORITY] WRITE ，自身可读写；其他线程完全不可读写。
• 释放锁，UNLOCK TABLES
• SELECT自动加读锁
• 其他DML、DDL自动加写锁

InnoDB是通过给索引上的索引项加锁来实现行锁
InnoDB有几种锁：
• 共享锁（S - LOCKING），允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
• 排它锁（X - LOCKING），允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他锁
InnoDB还独有的实现了2种锁：
• 意向共享锁（IS），事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
• 意向独占锁（IX），事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
注意：
1）在不通过索引条件查询的时候，InnoDB使用的是表锁（默认地，全表所有行加锁，和表级锁相当，例外条件是 RC + innodb_locks_unsafe_for_binlog 组合选项），而不是细粒度行锁。
2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
加共享锁：SELECT * FROM xx WHERE … LOCK IN SHARE MODE
加排他锁：SELECT * FROM xx WHERE … FOR UPDATE
INNODB_TRX
INNODB_LOCKS
InnoDB_LOCK_WAITS

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

innodb锁

主键索引 = record lock（但外键约束、唯一约束检测仍然使用gap lock）
唯一辅助索引 = record lock（但外键约束、唯一约束检测仍然使用gap lock）
非唯一辅助索引 = next-key lock
上述结论的前提：
1、RR级别
2、innodb_locks_unsafe_for_binlog = 0

对无索引的字段检索更新时，升级成表级锁（表中全部记录被锁，除非在RC或innodb_locks_unsafe_for_binlog=1模式下，采用semi-consitent read机制）
SELECT … FROM，一致性非锁定读，除非是SERIALIZABLE隔离级别，在其影响的索引记录上设置一个共享锁
LOCK IN SHARED MODE,使用共享next-key lock
FOR UPDATE使用排他next-key lock锁，会阻止LOCK IN SHARED MODE请求
UPDATE/DELETE，排他next-key lock
INSERT，排他record lock，而非next-key lock，但在写入新记录之前需要加意向插入gap lock（insertion intention gap lock）--插入意向锁
INSERT ... ON DUPLICATE KEY UPDATE，排他next-key lock（即将被UPDATE的记录上）
REPLACE，没冲突/重复时，和INSERT一样，否则（有冲突时是先DELETE后INSERT）加next-key lock
INSERT INTO T SELECT ... FROM S WHERE，T表上排他record lock；事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog并且隔离
级别不是SERIALIZABLE时，S表上采用无锁一致性读。否则，加排他next-key lock(RC不加锁，RR加next-key lock)
CREATE TABLE..SELECT，和INSERT…SELECT一样
REPLACE INTO t SELECT ... FROM s WHERE或 UPDATE t ... WHERE col IN (SELECT ... FROM s ...)，都会在s表上加next-key lock
AUTO_INCREMENT列上写入新数据时，索引末尾设置排他锁。请求自增列计数器时，InnoDB使用一个AUTO-INC表锁，只对请求的那个SQL有影响，不会影响整个事务。该锁被持有时，其他会话不能往InnoDB表中写入新行
LOCK TABLES，设置表锁

几个重点的锁类型，需要关注下：
• 如果辅助索引上的搜索及锁定是排他的，则会取回其相应的聚集索引，并且在它上面加锁
• 对无索引的字段检索更新时，升级成表级锁
• SELECT … FROM，一致性非锁定读，除非是SERIALIZABLE隔离级别
• INSERT INTO T SELECT ... FROM S WHERE，T表上排他record lock；事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog
并且隔离级别不是SERIALIZABLE时，S表上采用无锁一致性读。否则，加排他next-key lock
• INSERT，排他record lock，非next-key lock，但加意向插入gap lock（还有一种叫做 意向插入（insertion intention） 的gap lock，
如果两个不同事务想往同一个gap lock中写入数据，但写入位置不一样时，是无需等待，可以直接写入的，因为没有冲突）
• AUTO_INCREMENT列上写入新数据时，索引末尾设置排他锁。请求自增列计数器时，InnoDB使用一个AUTO-INC表锁，只对请求的那个SQL有影响，
不会影响整个事务。该锁被持有时，其他会话不能往InnoDB表中写入新行

SHOW ENGINE INNODB STATUS，只显示最后的死锁信息
设置 innodb_print_all_deadlocks = 1，在日志中记录全部死锁信息
自动检测死锁，并优先回滚小事务（影响较小的事务）
加表锁时，不会发生死锁
事务中，如果SELECT调用存储函数/存储过程失败了，对应的SQL会回滚事务。如果再显式执行ROLLBACK，那么整个事务都回滚。
事务回滚时，会释放全部锁。个别情况下，如果个别SQL因为某些错误回滚事务的话，它所持有的行锁可能无法释放，因为InnoDB的行锁信息并没有记录是哪个SQL持有的，这时候，建议执行一次显式的ROLLBACK
事务尽快提交，小事务越不容易发生死锁
加FOR UPDATE、LOCK IN SHARE MODE读锁时，最好降低事务隔离级别，例如用RC级别，降低死锁发生概率
事务中涉及多个表，或者涉及多行记录时，每个事务的操作顺序都要保持一致，降低死锁概率，最好用存储过程/存储函数固化
通过索引等方式优化SQL效率，降低死锁发生概率（减小扫描/锁范围，降低概率）

InnoDB多版本的实现机制：
• 为了实现多版本，InnoDB必须在表空间中保存行的旧版本信息。这些信息被保存在回滚段中。
• 在内部，InnoDB为每个行增加了两个域，一个6-byte的域来指示最后插入或更新这个行的事务标识符（DB_TRX_ID/Trx id），删除标志也被认为是一个更新，
因为它在提交前只是在行上做了一个标记。另外一个7-byte的域被称为回滚指针(DB_ROLL_PTR/roll pointer)，回滚指针指向一个由回滚段写入的undo日志记
录。如果一个行被更新了，undo日志记录包含了重建这行更新前信息的一些必要数据。
• InnoDB使用回滚段的信息来执行事务回滚所必须的一些undo操作，而且也使用这些信息来重建更新前的行信息。
• 回滚段中的undo日志被分为插入日志和更新日志。插入日志仅在事务回滚的时候有用，事务提交之后就可以马上删除掉。更新日志在一致性读的时候需要使
用，但是，如果当前没有事务再可能使用回滚段中的记录的时候，这些记录就可以删除掉了。因此，你必须经常提交你的事务，就算这些事务只是进行一致性
读操作而已。否则，InnoDB不能删除掉某些更新日志，这样回滚段将变得越来越大。
read only transaction，从5.7开始支持
• 回滚段中undo日志记录的物理大小要比其对应的插入或更新的行要小很多。
• 在多版本方式下，当你使用SQL语句删除某一行的时候，该行并不会马上从数据库的物理文件上移除。只有当InnoDB能够删除掉更新日志记录的时候，
那些行及其对应的索引记录才会真正从物理上删除掉。这个移除操作称为purge
• rollback segment（回滚段）保存历史修改版本信息，历史版本数据放在undo log里
• 7字节的DB_ROLL_PTR，指向undo tablespace的回滚指针，undo tablespace了存储了行记录修改前后有变化的数据，而不是整行记录（所以如果undo log损坏
的话，会导致事务回滚失败，无法恢复数据，InnoDB报错）
• 6字节的DB_ROW_ID，指向对应的行记录，每次新写入一行，该ID自增。InnoDB没指定主键而自动创建隐含的聚集索引时，则该聚集索引会包含该ROWID的值
（自主创建聚集索引时，则不会包含ROWID的值）
• undo log里是区分INSERT、UPDATE（DELETE认为是特殊的UPDATE）的，INSERT事务提交后，立刻可以PURGE了；而UPDATE事务必须等所有一致性读事务都提交后，
确认无需再次读取了，才能PURGE。此外，UPDATE产生的undo比INSERT产生的undo更大
• 所有事务尽快提交，包括一致性只读事务，否则一些UPDATE产生的undo log没办法及时释放，导致rollback seg、undo log越来越大
• DELETE事务删除的行记录并不真正立刻被删除，而是先打标记，直到所有相关事务都结束，然后才执行PURGE操作
• 适当调整innodb_max_purge_lag，避免事务PURGE堆积，影响性能

• innodb的逻辑存储单元由大到小分别是 tablespace =&get; segment =&get; extent =&get; page(block)组成
段(segment)
• 常见的segment有数据段、索引段、回滚段
• innodb是索引聚集表，所以数据就是索引，索引就是数据
• 数据段即是B+树的页节点(leaf node segment)
• 索引段即为B+树的非索引节点(non-leaf node segment)
• 段的管理是由引擎本身完成
区(extend)
• 区是由64个连续的页主成
• 每个页大小为16K
• 即每个区的大小为(64*16K)=1MB
• 对于大的数据段，mysql每次最多可以申请4个区，以此保证数据的顺序性能
页(page)
• 页是innodb磁盘管理最小的单位
• innodb每个页的大小是16K
• 常见的类型有
• 数据页 B-tree Node
• undo页 Undo Log Page
• 系统页 System Page
• 事务数据页 Transaction system Page
• 插入缓冲位图页 Insert Buffer Bitmap
• 插入缓冲空闲列表页 Insert Buffer freeBitmap
• 未压缩的二进制大对象页Uncompressed BLOB Page
• 压缩的二进制大对象页 Compressed BLOB Page
行（row）
• innodb存储引擎是面向行的(row-oriented)，也就是说数据的存放按行进行存放
• 每个页最多可以存放16K/2～200行,也就是7992个行--每页至少存2行记录，由于b+tree的双向链表决定
• 每个page大小16KB
• 每64个连续的page组成一个extend（1MB）
• 多个extend和page组成一个segment
• segment初始化时，会先初始化32个page，之后根据需要会将extent分配给 segment，单次最多会分配4个extends给segment
• InnoDB中一个索引（B-tree）由两个segment组成。其中，所有的叶子节点（leaf nodes）存放在一个segment中（更连续，更高效），所有的非叶子节点（nonleaf nodes）存放在一个segment中
• 行记录（row）存放在数据页（page）里，page由：page header、page trailer、page body组成

在系统表空间中，innodb会维护一些系统信息：
• Internal data dictionary
• Rollback segments
• Undo Space
• Insert buffer
• Double write buffer
• MySQL replication info
• data page默认16KB，当有新索引记录写入时，会预留1/16（1KB）空闲空间用于以后的索引记录写入
• 当数据记录按照顺序（正序、倒序）写入时，最理想的结果是数据页能填充15/16；如果是随机无序写入，则数据页填充率可能会从1/2 ~ 15/16，
当fill factor（填充因子）小于1/2时，会开始收缩数据页，释放空闲空间
• 5.6开始支持修改page size，支持4K、8K、16K，初始化时指定，后续无法修改，不同page size的实例间也不能直接迁移使用。5.7开始支持扩展到32KB、64KB。
• Insert Buffer的作用，是将辅助索引上的IUD操作从随机变成顺序，提高IO写入效率（约15倍）。Insert Buffer合并到辅助索引树中，可能会有多个Buffer合并在一起
4种row format：REDUNDANT、 COMPACT、COMPRESS、DYNAMIC
当file format为 Antelope时，支持：REDUNDANT、 COMPACT 这两种行格式,发生行溢出时，在当前page会存储前768字节，多余的放在off-page。
当file format为 Barracuda时，支持：COMPRESS、DYNAMIC 这两种行格式，并且兼容前两种，并且兼容前两种，在当前page会存储前20字节，多余的放在off-page
选项
innodb_file_format_max = Barracuda
innodb_file_format = Barracuda
要设置成一样，并且同时最好把 innodb_file_format_check设置为1（默认值也是1），避免InnoDB在启动过程中需要恢复数据，因为没有检查而写入不支持格式的表中，导致数据丢失。