阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL锁原理浅谈

191次阅读
没有评论

共计 3707 个字符,预计需要花费 10 分钟才能阅读完成。

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

锁的分类

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

锁的适用场景

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

MyISAM 表锁

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

MyISAM 写阻塞读的例子

session 1session 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 1session 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 1session 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 锁
请求锁模式是否兼容当前锁模式 XIXSIS
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 能自动检测死锁,并使一个较简单的事务回退并释放锁,另一个事务获得锁,继续完成事务。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3707字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中