前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL InnoDB Lock(二)

MySQL InnoDB Lock(二)

作者头像
MySQL轻松学
发布2018-03-09 15:18:11
3.5K0
发布2018-03-09 15:18:11
举报
文章被收录于专栏:MYSQL轻松学

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


本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2016-09-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 MYSQL轻松学 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档