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 条评论
登录 后参与评论

相关文章

来自专栏流柯技术学院

MySQL执行计划解读

id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

781
来自专栏互扯程序

java多线程-概念&创建启动&中断&守护线程&优先级&线程状态

在现代操作在运行一个程序时,会为其创建一个进程。例如启动一个QQ程序,操作系统就会为其创建一个进程。而操作系统中调度的最小单位元是线程,也叫轻量级进程,在一个进...

793
来自专栏about云

spark2 sql读取数据源编程学习样例2:函数实现详解

问题导读 1.RDD转换为DataFrame需要导入哪个包? 2.Json格式的Dataset如何转换为DateFrame? 3.如何实现通过jdbc读取和保...

3316
来自专栏雨过天晴

原 Go 的 MogonDB 包 mgo

1663
来自专栏IT开发技术与工作效率

MySQL 循环查询树 函数

1285
来自专栏数据库

mysql总结(一)

一、数据库管理 1.1查询所有数据库 mysql> show databases; +--------------------+ Database +-----...

1886
来自专栏PHP技术

如何在 MySQL 中判断中文字符?

原文出处: 温国兵 一 引子 在生产环境中,经常会有这样的场景:获得中文数据。那问题就来了,怎么才能匹配出中文字符呢? 本文提供两种方法。 二 演示 2.1 ...

27911
来自专栏小白客

学习SQL【2】-数据库与SQL

一:数据库是什么? 1:定义 ● 将大量数据通过计算机加工而成的可以进行高效访问的数据集合称为数据库(DB)。 ● 用来管理数据库的计算机系统称为数据库管理系...

3319
来自专栏乐沙弥的世界

视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异

      视图v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines 是几个经常容易混淆的视图,主要是提供libr...

1203
来自专栏张俊红

数据分析系列——SQL数据库

数据分析系列——SQL数据库 总第49篇 ▼ 本文知识只是用作于常用的数据分析中,并未涉及专业数据库搭建等知识。全篇分为四个部分:初识数据库、数据库的操作、数据...

3388

扫码关注云+社区