MySQL锁原理浅谈

栏目: 数据库 · 发布时间: 6年前

内容简介:系统变量 concurrent_insert:用于控制并发插入行为事务隔离级别越高,并发副作用越小,代价越高,因为事务隔离从某种程度上说使得事务串行化。用范围而非等值搜索数据,并且请求共享/排他锁时,InnoDB会对所有符合条件的已有记录的索引项加锁,对键值在范围内但不存在的记录,即GAP-间隙,也会加锁。

锁的分类

  • 表锁:开销小,加锁快,不会死锁,粒度大,冲突率高,并发低。
  • 行锁:开销大,加锁慢,会死锁,粒度小,冲突率低,并发高。
  • 页锁:处于表锁和行锁之间,会死锁。

锁的适用场景

  • 表锁:更适用于查询为主,按少量索引条件更新。
  • 行锁:更适用于大量按索引并发更新少量不同数据,同时又有并发查询。

MyISAM表锁

  • 查看锁争用相关参数:show status like 'table%';
  • Table_locks_waited的值越高表示表锁争用越高。
  • MyISAM表的读操作,会阻塞同表的其他读请求,会阻塞同表写请求;
  • 写操作会阻塞同表的读请求和写请求。
  • 读与写、写与写之间串行,持锁线程可对表更新,其他线程读/写都会等待,直到锁释放。

MyISAM写阻塞读的例子

session 1 session 2
lock table user write;
select * from user; //返回查询结果 select * from user; //被阻塞,等待锁被释放
unlock tables; 获得锁,返回查询结果

注:

  1. lock tables时,要一次性锁定用到的所有表
  2. 对别名也需要锁定,如:lock table user as a read, user as b read;

MyISAM读阻塞写例子

session 1 session 2
lock table user read;
可查询:select * from user; 可查询:select * from user;
不能查询未锁定的表:select * from goods; //Table 'goods' was not locked with Lock Tables 能查询/更新未锁定的表
当前session更新锁定表会报错,Read Lock 更新锁定表会等待
Unlock tables; 获得锁,更新完成

MyISAM并发插入

系统变量 concurrent_insert:用于控制并发插入行为

  • 0 不允许并发插入
  • 1 表中没有被删除的行(即没有空洞),则允许一个进程读,另一个进程在表尾插入(默认设置)
  • 2 表中不论是否存在空洞,都允许在表尾并发插入

MyISAM读写并发

session 1 session 2
lock table user read local;
当前session无法对该表更新或插入 可以插入,但更新需要等待锁释放
无法访问其他session插入的数据
unlock tables; 获得锁,更新完成
可以查到其他session插入的数据

注:

  • 利用并发插入可以解决应用对同一个表查询和插入的锁争用;
  • 将cocurrent_insert设置为2,定期OPTIMIZE TABLE来整理空间碎片,回收删除记录产生的空洞。

MyISAM锁调度

  1. 读锁与写锁互斥;
  2. 读操作与写操作串行;
  3. 写进程先获得锁,即使读请求先到队列,也会被写请求插队,因为 mysql 认为写比读要重要(因此MyISAM不适合有大量更新/插入操作)。

调节MyISAM锁调度行为

  • low-priority-updates,给予读优先权利;
  • SET LOW-PRIORITY_UPDATES=1,降低更新请求优先级;
  • 指定INSERT、UPDATE、DELETE的LOW-PRIORITY属性,降低该语句优先级。

解决读写冲突的方法:

  • 系统参数 max_write_lock_count 设置合理值,表的读锁达到设定阈值后,mysql就将写请求优先级降低。
  • 一些需要长时间运行的读操作,需要拆分为多条短select sql,复杂查询放在数据库空闲时段进行,比如夜间执行。

InnoDB与MyISAM最大区别:

  1. 支持事务;
  2. 行级锁。

事务 - Transaction

事务操作 描述
BEGIN 或者 START TRANSACTION 开始事务
COMMIT 提交事务
ROLLBACK 回滚结束事务,撤销进行中的所有未提交的修改
SAVEPOINT identifier 设置保存点
RELEASE SAVEPOINT identifier 事务回滚到保存点
ROLLBACK TO identifier 撤销保存点
SET TRANSACTION = {READ UNCOMMITED,READ COMMITED,REPEATABLE READ,SERIALIZABLE} 设置事务隔离级别
SET AUTOCOMMIT = {0,1} 禁止/开启自动提交

事务的特性

  • A - Atomicity 原子性:全执行/全不执行
  • C - Consistent 一致性:数据状态一致
  • I - Isolation 隔离性:事务处理过程中的中间状态对外不可见,不受外部并发操作影响
  • D - Durable 持久性:事务完成后对数据修改是永久性的
并发事务问题 描述 解决方案
更新丢失 两个事务对同一行数据修改,先提交的被后提交的覆盖 应用程序对要更新的数据加锁
脏读 A事务改一行数据,B事务读到了A的改动“脏”数据,A回滚则B的数据有问题 数据库事务隔离,解决读一致性问题:1、读之前加锁,防止其他事务对数据修改;2、不加锁,生成快照,多版本并发控制
不可重复读 一个事务多次读取同一数据发现被改变/删除 同上
幻读 一个事务按先前的条件查询,发现其他事务插入了满足条件的新数据 同上

注:

事务隔离级别越高,并发副作用越小,代价越高,因为事务隔离从某种程度上说使得事务串行化。

MySQL事务隔离级别

隔离级别/并发问题 读一致性 脏读 不可重复读 幻读
未提交读 最低
已提交读 语句级
可重复读 事务级
可序列化 最高

获取InnoDB行锁争用情况

  • show status like 'innodb_row_lock%';
  • 锁争用严重时,InnoDB_row_lock_waits和InnoDB_row_lock_time_avg值较大。

InnoDB行锁类型

行锁类型 描述
共享锁 S 允许事务读一行,阻止其他事务获得排他锁
排他锁 X 允许事务更新数据,阻止其他事务获得共享读锁和排他写锁
意向共享锁 IS 事务打算给行加共享锁,先取得表IS锁
意向排他锁 IX 事务打算给行加排他锁,先取得表IX锁
请求锁模式是否兼容当前锁模式 X IX S IS
X
IX
S
IS

注:

  • 含I的锁与含I的锁兼容;
  • 单X与任何锁不兼容;
  • 单S与含X的锁不兼容;
  • 若一个事务请求的锁模式与当前的锁兼容,InnoDB将请求的锁授予该事务,不兼容就要等到锁释放;
  • 意向锁是InnoDB自动加的,DELETE、UPDATE、INSERT,InnoDB会自动加X锁,普通SELECT,InnoDB不加任何锁。

手动加锁的方法

  • 共享锁(S):SELECT * FROM user LOCK IN SHARE MODE;
  • 排他锁(X):SELECT * FROM user FOR UPDATE;

    注:

  • SELECT * FROM ... LOCK IN SHARE MODE; //若当前事务加了读锁,进行更新会死锁
  • SELECT * FROM ... FOR UPDATE; //一个事务加了写锁,其他事务加锁操作需要等待
  • InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索,才会使用行级锁,否则会用表锁;
  • 分析锁冲突时,检查 SQL 执行计划(利用explain),以确认是否真正走了索引,例如:SELECT * FROM user WHERE name = 123; //name字段是varchar类型且有索引,但条件中用了int型,类型能自动转换,但会进行全表扫描。

间隙锁(Next-key Lock)

概念描述

用范围而非等值搜索数据,并且请求共享/排他锁时,InnoDB会对所有符合条件的已有记录的索引项加锁,对键值在范围内但不存在的记录,即GAP-间隙,也会加锁。

例如:

  • user表,id从1~100共100个,执行:
  • SET AUTOCOMMIT = 0;
  • SELECT * FROM id > 99 FOR UPDATE;
  • 会对id等于100的记录的索引项加锁,对id大于99的间隙加锁。

    作用:

  1. 满足隔离级别要求,防止幻读;
  2. 满足恢复和复制需要(MySQL通过BINLOG录入执行成功的INSERT、UPDATE、DELETE等更新语句)

    存在的问题:

    按范围加锁机制会阻塞符合条件范围内的键值并发插入,造成锁等待。

    解决方法:

    优化业务逻辑,尽量用相等条件来检索数据。

注:

相等条件检索一个不存在记录加锁时,InnoDB也会使用间隙锁。例如:

  • 对上面的user表,执行:
  • SET AUTOCOMMIT = 0;
  • SELECT * FROM id = 101 FOR UPDATE;
  • 再在另一个 MySQL Session 中执行 INSERT INTO user ( id , name , password , description )
    VALUES
    (101, 'clive', '123456', 'psw'); //查询被阻塞,进入等待直至锁释放

死锁的概念

死锁是指多个事务在统一资源上,出现相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。

MyISAM和InnoDB在死锁上的区别

  • MyISAM不会出现死锁,因为MyISAM总是一次获得所需要的全部锁,要么全部满足,要么全等待;
  • InnoDB除了单SQL事务,锁是逐步获得的,因此可能出现死锁。一般InnoDB能自动检测死锁,并使一个较简单的事务回退并释放锁,另一个事务获得锁,继续完成事务。

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-10/155054.htm


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

The Master Switch

The Master Switch

Tim Wu / Knopf / 2010-11-2 / USD 27.95

In this age of an open Internet, it is easy to forget that every American information industry, beginning with the telephone, has eventually been taken captive by some ruthless monopoly or cartel. Wit......一起来看看 《The Master Switch》 这本书的介绍吧!

随机密码生成器
随机密码生成器

多种字符组合密码

SHA 加密
SHA 加密

SHA 加密工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具