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

相关文章

来自专栏数据和云

返璞归真:实例会话RAC条件下的笛卡尔积种参数可能性

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 参数文件是Or...

28510
来自专栏battcn

MySQL - 常见的三种存储引擎

数据库存储引擎: 是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水...

671
来自专栏冷冷

MySQL 解决外键约束不能删除数据的问题

第一种方法是使用MySQL 官方提供的文档方法             这个禁用约束: 运行这个命令 SET FOREIGN_KEY_CHECKS=0; 之后数...

21510
来自专栏ImportSource

锁系列-Mysql中的锁

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。 目录: 1、行级锁、表级锁、页级锁 2、共享锁和排它...

36315
来自专栏MYSQL轻松学

MySQL 面试选择题15道(单选)

1、MySQL数据库四种特性,不包括() A.原子性 B.事务性 C.一致性 D.隔离性 2、MySQL报错error 1062 的意思是() A.连接数据库失...

5516
来自专栏枕边书

PHP中的数据库一、MySQL优化策略综述

前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身,而在于数据库。我们都知道,PHP开发中,数据的增删改查是核心。为了提升PHP的运行效率,程序员不光需...

2798
来自专栏杨建荣的学习笔记

数据紧急修复之启用错误日志 (r2第12天)

昨晚对测试环境进行了升级,同步了部分生产的数据。整个过程比较顺利,但是在最后一步启用foreign key constraint的时候报了错误。 ora-022...

2849
来自专栏lgp20151222

mysql explain 的extra中using index ,using where,using index condition,using index & using where理解

using where:查找使用了索引,不需要回表去查询所需的数据,查询结果是索引的一部分

831
来自专栏.NET技术

经典SQL语句大全之数据开发

1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Str...

1605
来自专栏杨建荣的学习笔记

迁移式升级的测试(二)(r10笔记第35天)

在之前写的一篇博文中,自己是打算对一台数据库使用Data Guard+TTS的方式来完成数据迁移和升级的工作,迁移式升级的新方案测试 (r10笔记第30天) 整...

3374

扫码关注云+社区