MySQL InnoDB Lock(二)

MySQL InnoDB Lock主要从5个部分介绍,这篇文章承接 上一篇 ,会详细介绍后3部分。


  • 数据库数据一致性
  • InnoDB事物一致级别
  • InnoDBLock基础知识
  • RR一致级别下SQL对应InnoDBLock情形
  • InnoDBLock定位分析

InnoDBLock基础知识

InnoDB锁按照级别分:

表级别(MySQL Server提供)MySQL Internal Lock或意向锁

行级别(InnoDB存储引擎提供)

InnoDB表级别锁按照操作性分:

IS Lock(意向共享锁):在对表的记录获取S lock前,表必须先获取表级别的IS锁或者更高级别的锁。

IX Lock(意向排它锁):在对表记录获取X lock前,表必须先获取到表级别的IX锁

自增lock (AUTO-INC lock)

5.1.22后引入了轻量级互斥自增长机制。

innodb_autoinc_lock_mode=1(default)。

根据Insert类型为simple-insert,bulk-insert(INSERT…SELECT,REPLACE…SELECT,LOAD DATA),mixed-mode-insert,innodb_autoinc_lock_mode=(0,1,2)

0=所有insert采用传统AUTO-INC机制;

1=bulk-insert采用轻量级;

2=所有insert采用轻量级(但是replication只能row-base)

InnoDB行锁按照影响范围分:

Record Lock:锁定单条记录(基于主键的扫描或基于唯一辅助Index的扫描只有record lock,无gap lock)

Gap Lock:锁定一个范围的记录,但不包括记录本身(基于非唯一辅助Index的扫描,有record lock和gap lock,也就是Next Key Lock,范围为(pre_nextkey,nextkey) )

Next Key Lock:锁定一个范围的记录,并且包含记录本身。MySQL在RR事物隔离级别下,通过Next Key Lock解决幻读问题。

Note:MySQL InnoDB行级锁,其实是index记录锁(Oracle是block锁)

RR事物级别下,基于无Index列扫描的修改(delete、update)操作,将锁定所有行记录,现象像lock了整个表。

1. 主键Record lock

2.唯一 Index Record lock

3. 非唯一Index Next Key Lock

4.无Index 全记录Lock

RR一致级别下SQL对应InnoDB Lock情形

  • Select … from 无lock,SERIALIZABLE存在S Next-Key lock
  • Select … from… lock in share mode存在S Next-Key lock
  • Select… from… for update存在X Next-Key lock
  • Insert… values(…)存在X row record lock;对于存在auto_increment列,存在X lock在auto_increment列的index,特殊的表级AUTO-INC lock;如果insert产生duplicate-key错误,则在duplicate index record设置S lock,如果多个session插入同一行则可以产生deadlock(示例Insert duplicate)(5.1版本,deadlock会等待,5.6deadlock快速释放,无需等innodb_lock_wait_timeout
  • Insert into T select … from S where…存在T表的X row record lock,S表存在S的Next-Key lock
  • Create table … select…from S,S表存在S的Next-Key lock
  • Replace…如果无主键冲突,X row record lock,否则X Next-Key lock
  • Update… where存在X Next-Key lock
  • Delete from… where存在X Next-Key lock
  • 表存在Foreign Key,表的Insert、Update、Delete,存在S的record lock
  • Lock table是显示增加一个表级lock

Note:RR隔离级别下,为了减少Next-key lock可以设置innodb_locks_unsafe_for_binlog=1(不建议),就是disable Next-Key lock,或者修改事物隔离级别为RC(建议)

示例Insert duplicate

InnoDB Lock定位分析

  • 直接使用show engine innodb status不能获取lock的根源
  • 使用mysqladmin debugmysqladmin -S /DATA/mydata/mysql.sock debug
  • 使用innodb_lock_monitor需要创建表: (任意DB中,不使用时drop此表)CREATE TABLE innodb_lock_monitor(a INT) ENGINE=INNODB; InnoDB monitor有:innodb_monitor,innodb_lock_monitor,innodb_table_monitor,innodb_tablespace_monitor,开启后定期执行将结果输出到errorlog(前2个20s,后两个60s,Note:不使用时注意drop相关表,停止monitor)
  • 5.5版本以后,使用information_schema中相关表TRANSACTIONS.mht

参考:

http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html


原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-09-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Phoenix的Android之旅

Android广播发送流程深度分析

备注:这个方法在6.0之后移到了 BroadQueue 类中, AMS 的代码也有相应的调整

682
来自专栏乐沙弥的世界

绑定变量及其优缺点

    绑定变量是Oracle解决硬解析的首要利器,能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快,使起来锋利,却容 易折...

772
来自专栏Java技术

MySQL开发规范与使用技巧总结

1.库名、表名、字段名必须使用小写字母,并采用下划线分割。 a)MySQL有配置参数lower_case_table_names,不可动态更改,Linux系统...

733
来自专栏JavaWeb

Mysql索引原理深入剖析

3053
来自专栏数据和云

【千里之行始于足下】谈Oracle的system表空间

编辑手记:SYSTEM表空间是Oracle数据库最重要的表空间,在创建数据库时被最先创建,其中包含了数据库的元数据,对于数据库来说生死攸关。对于很多初学者,全面...

3065
来自专栏orientlu

FreeRTOS 软定时器实现

考虑平台硬件定时器个数限制的, FreeRTOS 通过一个 Daemon 任务(启动调度器时自动创建)管理软定时器, 满足用户定时需求. Daemon 任务会在...

532
来自专栏技术博客

SQL知识点(一)

    主数据文件有且只有一个,二级数据文件0-N个     扩展名:主数据文件:MDF             二级数据文件:NDF */

663
来自专栏java达人

join和where区别以及各类join的示例

1 WHERE子句中使用的连接语句,在数据库语言中,被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。(其他JOIN参数也是显性连接...

17510
来自专栏机器学习从入门到成神

临界区、互斥量、信号量

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

552
来自专栏吴生的专栏

30多条mysql数据库优化方法,千万级数据库记录查询轻松解决

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

39010

扫描关注云+社区