MySQL锁机制

什么是锁

锁是计算机协调多个进程或线程并发访问某一资源的机制.

在数据库中,除了传统的计算资源(如CPU,RAM,I/O等)的争用外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性,有效性是所有数据库必须解决的问题.锁冲突也是影响数据库并发访问性能的一个重要因素.从这个角度来说,锁对数据库而言显得尤为重要,也更加复杂.

锁的分类

从对数据操作的粒度分: 表锁,行锁

从对数据操作的类型(读/写)分:

  • 读锁(共享锁):

    针对同一份数据,多个读操作可以同时进行而不会互相影响

    如果session1加了一个读锁 session1可以查 无法更新

    则同时也无法读另一个表 只要加了锁

    session2 无法更新session1 加锁的表 只有等session1 解了锁 session2 自动解除阻塞

  • 写锁(排他锁):

    当前写操作没有完成前,它会阻断其他写锁和读锁

    session1 加了写锁 可读可改

    同时session2 不可读不可写

表锁:

特点:

偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

加锁:

1
2
3
4
5
6
7
# 手动增加表锁

lock table 表名 read(write)

unlock tables

show open tables;

MySQL的表级锁有两种模式

表共享读锁(Table Read Lock)

**表独占写锁(**Table Write Lock)

锁类型 可否兼容 读锁 写锁
读锁
写锁

结合上表:

  1. 对MySIAM表的读操作(加读锁),不会阻塞其它进程对同一表的读请求,但会阻塞对同一表的写请求,只有当读锁释放后,才会执行其它进程的写操作
  2. 对MySIAM表的写操作(加写锁),会阻塞其它进程对同一表的读和写操作,只有当锁释放后,才会执行其它进程的读写操作

如何分析表的锁定

可以通过检查table_locks_waitedtable_locks_immediate状态变量来分析系统上的表锁定

1
show status like 'table%';
  • Table_locks_immediate:

    产生表级锁定的次数 表示可以立即获取锁的查询次数 每立即获取锁值加1

  • Tables_lock_waited:

    出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),这个数值高说明存在较严重的表级锁争抢的情况;

  • MyISAM的读写锁调度是写优先 这也是MyISAM不适合作为 写 为主表的引擎,因为写锁后,其它线程不能做任何操作,大量的更新会使得查询很难得到锁,从而造成永久阻塞

行锁:

特点:

  • 偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁
  • 锁定粒度最小,发送锁冲突的概率最低,并发度最高

行锁支持事务带来的问题:

  • 更新丢失(Lost Update):

    当两个或者多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其它事务的存在,就会发生丢失更新的问题

    (最后的更新覆盖了由其他事务所做的更新)

    1. A事务撤销时,把已经提交的B事务的更新数据覆盖了。
    2. A事务覆盖B事务已经提交的数据,造成B事务所做的操作丢失
  • 脏读(Dirty Reads):

    一个事务正在对一条记录做修改,在这个事务完成提交前,这条记录就会处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务就读取了这些**”脏”数据**,并据此做进一步的处理,就会产生未提交的数据依赖关系,这种现象被形象地叫做”脏读”.

    (如果事务A 读取了 事务B 已修改但是尚未提交的数据 还在这个基础上做了操作,此时如果B事务回滚,A读取的数据无效,不符合一致性要求)

    张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
    与此同时,
    事务B正在读取张三的工资,读取到张三的工资为8000。
    随后,
    事务A发生异常,而回滚了事务。张三的工资又回滚为5000。
    最后,
    事务B读取到的张三工资为8000的数据即为脏数据,事务B做了一次脏读

  • 幻读(Phantom Reads):

    一个事务按相同的查询条件 重新读取以前检索过的数据 却发现被其它事务插入了满足其查询条件的新数据,这种现象就成为幻读

    (事务A读取到了事务B提交的新增数据,不符合隔离性)

    目前工资为5000的员工有10人,事务A读取所有工资为5000的人数为10人。
    此时,
    事务B插入一条工资也为5000的记录。
    这是,事务A再次读取工资为5000的员工,记录为11人。此时产生了幻读。

  • 不可重复读(Non-Repeatable Reads):

    一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。

    在事务A中,读取到张三的工资为5000,操作没有完成,事务还没提交。
    与此同时,
    事务B把张三的工资改为8000,并提交了事务。
    随后,
    在事务A中,再次读取张三的工资,此时工资变为8000。在一个事务中前后两次读取的结果并不致,导致了不可重复读。

  • 脏读是 事务B 里面修改了数据 幻读是事务B里面新增了数据

事务隔离级别

读数据一致性和允许并发副作用隔离级别 读数据一致性 脏读 不可重复读 幻读 1类丢失 2类丢失
未提交读(Read Uncommited) 最低级别,只能保证不读取物理上损坏的数据
已提交读(Read Commited) 语句级
可重复读(Repeatable Read) 事务级
可序列化(Serializable) 最高级别,事务级

数据库的事务隔离级别越严格 并发副作用越小 但是付出的代价就越大 因为事务隔离实质上就是使事务在一定程度上串行化进行,这显然与并发是矛盾的.同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对”不可重复读”和”幻读”并不敏感,更加关心数据并发访问的能力.

查看当前数据库事务隔离级别

1
show variables like 'tx_isolation';

MySQL避免了 脏读和重复读

间隙锁

image-20200721091940183

什么是间隙锁?

当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁的时候,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做”间隙(GAP)

InnoDB也会对这个间隙加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)

危害

因为query执行过程中通过范围查找的话 锁定整个范围内所有的索引键值 即使这个键值不存在

间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使对某些不存在的键值也会锁定,而造成在锁定的时候无法插入锁定键值范围的任何数据.在某些场景下可能对性能造成很大危害

如何锁定 一行

image-20200721092200685

行锁分析

1
show status like 'innodb_row_lock%'

各个状态量的说明:

  • Innodb_row_lock_current_waits: 当前正在等待锁定的数量
  • Innodb_row_lock_time: 从系统启动到现在锁定总时间长度;
  • Innodb_row_lock_time_avg: 每次等待所花的平均时间;
  • Innodb_row_lock_time_max: 从系统启动到现在等待最久的一次所花时间
  • Innodb_row_lock_lock_waits: 系统启动后到现在等待的次数

尤其是 等待次数很高 每次等待时长也不小的时候 就要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划

资料参考: 尚硅谷