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

锁类型/引擎

行锁

表锁

页锁

MyISAM

InnoDB

BDB(被InnoDB取代)

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

锁的适用场景

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

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能自动检测死锁,并使一个较简单的事务回退并释放锁,另一个事务获得锁,继续完成事务。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入解析:你听说过Oracle数据库的更新重启动吗?

杨廷琨 云和恩墨高级咨询顾问, ITPUB Oracle 数据库管理版版主 ,人称 “杨长老”,十数年如一日坚持进行 Oracle 技术研究与写作,号称 ...

924
来自专栏用户2442861的专栏

MySQL详解--锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如...

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

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

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

3414
来自专栏乐沙弥的世界

ORA-00942: table or view does not exist

      在过程,包,函数,触发器中调用Oracle相关动态性能视图时,需要授予适当的权限,否则会收到表和视图不存在的错误提示。即使你可以单独查询这些视图。因...

1352
来自专栏MYSQL轻松学

MySQL InnoDB Lock(二)

MySQL InnoDB Lock主要从5个部分介绍,这篇文章承接 上一篇 ,会详细介绍后3部分。 ---- 数据库数据一致性 InnoDB事物一致级别 Inn...

3877
来自专栏性能与架构

mysql 索引碎片修复

image.png 在长期的数据更改过程中,索引文件和数据文件,都会产生空洞和碎片,会降低索引的运行效率 查看碎片 SHOW TABLE STATUS LIKE...

3545
来自专栏腾讯云Elasticsearch Service

mysql数据实时同步到Elasticsearch

业务需要把mysql的数据实时同步到ES,实现低延迟的检索到ES中的数据或者进行其它数据分析处理。本文给出以同步mysql binlog的方式实时同步数据到ES...

3.5K7
来自专栏枕边书

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

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

2888
来自专栏magicsoar

Mysql Innodb 锁机制 select * from table where?insert?delete?update?3个insert的死锁2个update的死锁3个以上delete的死

latch与lock latch 可以认为是应用程序中的锁,可以称为闩锁(轻量级的锁) 因为其要求锁定的时间必须要非常短,若持续时间长,则会导致应用性能非常差,...

3218
来自专栏用户画像

mysql 模拟试题一

  3.SQL语言的数据操纵语句包括 SELECT,INSERT,UPDATE和 DELETE, 最重要的,也是使用最频繁的语句是__A__。 

1104

扫码关注云+社区