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在启动过程中需要恢复数据,因为没有检查而写入不支持格式的表中,导致数据丢失。

posted @ 2019-04-03 16:33  春困秋乏夏打盹  阅读(186)  评论(0编辑  收藏  举报