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

相关文章

来自专栏农夫安全

注入学习之sqli-labs-3(第二关)

前言 本来是想一个个关卡讲下去,后来自己测试了一下,发现第二、三、四这三关跟第一关,起始原理是一样的,只不过是单引号,双引号,带不带括号的区别,只要我们带入的语...

3416
来自专栏跟着阿笨一起玩NET

SQL语句使用总结(一)

1>. FROM 2>. WHERE 3>. GROUP BY 4>. HAVING 5>. SELECT 6>. ORDER BY

611
来自专栏java达人

MySQL 加锁处理分析

背景 MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有同事咨询这方面的问题。同时,微博上也经常会收到MySQL锁相关的私信...

2905
来自专栏Clive的技术分享

Mysql锁相关锁的分类锁的适用场景MyISAM表锁MyISAM写阻塞读的例子MyISAM读阻塞写例子MyISAM并发插入MyISAM读写并发MyISAM锁调度调节MyISAM锁调度行为解决读写冲突的

锁类型/引擎 行锁 表锁 页锁 MyISAM 有 InnoDB 有 有 BDB(被InnoDB取代) 有 有 锁的分类 表...

4595
来自专栏battcn

MySQL - 常见的三种存储引擎

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

801
来自专栏冷冷

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

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

22810
来自专栏ImportSource

锁系列-Mysql中的锁

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

37315
来自专栏文渊之博

比较列存储索引与行索引

原因:     之前已经写过一篇关于列存储索引的简介https://cloud.tencent.com/developer/article/1032222,很粗...

2256
来自专栏数据和云

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

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

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

MySQL 5.7 General Tablespace学习(r11笔记第34天)

MySQL里面的文件蛮有意思,之前大体有两个参数来做基本的控制。一个是innodb_data_file_path就是一个共享表空间,数据都往这一个文件里放,也就...

3809

扫码关注云+社区

领取腾讯云代金券