mysql锁

蝎子莱莱爱打怪
416 阅读6分钟

mysql锁

1. 分类

从表结构维度分类为:————————————————表锁，页锁，行锁————————————————

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高 ，并发度最低。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

从锁的形式上区分为:————————————————乐观，悲观————————————————:

2. 乐观锁详解: (注意:需要我们自己实现)

  • 给数据库中的A表加个字段 version 当我们每次update时候 首先读取数据 并使用读取后的version作为更新时候的where条件 并将version+1 更新至数据库中。 优点: 当多读少写时候效率很高 缺点: 当多写时候 冲突急剧上升 降低数据库性能

3. 悲观锁详解:（注意:悲观锁是数据库自带的）

  • 共享锁（也被称为读锁）:

    特点: 被事务A 获取到的共享锁，事务B事务n都可以对其进行读取，但是不可以写操作。

    在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。 其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

注意:加上共享锁后，对于update，insert，delete语句会自动加排它锁。

  • 演示

    加上共享锁后其他事务可以加共享锁但是不能加排他锁的演示见图:（注意 演示时候把autocommit设置位0关闭mysql的自动提交事务属性，方便我们演示）

image.png 新打开窗口 我们发现读是可以的 但是update却失败 并提示 如下所示:

   1205 - Lock wait timeout exceeded; try restarting transaction, Time: 50.133000s

image.png

  • 排他锁: 注意: insert , update , delete , select * from table where id=1 for update; 都会加排他锁

若某个事物对某一行加上了排他锁，那么只能该事务对该记录进行读写且在此事务结束之前，其他事务不能对其进行加任何锁(排它锁会阻塞其他所有事务申请排它锁和共享锁的请求线程)，其他进程不能进行写操作，需等待其释放。在查询语句后加上for update;即可申请排他锁。

  • 演示

image.png

4. 行锁

1. 行锁的劣势：开销大；加锁慢；会出现死锁

2. 行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

3. 加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁 如 在select后加 for update;

行锁总结与特点:
1. InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
2. 当批量更新表数据时候 行锁会升级为表锁

5.间隙锁

  • 什么是间隙锁

当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

  • 间隙锁的危害

范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在 1、3、5、7 中插入 2，这个时候 1-7 都被锁定住了，根本无法插入 2。在某些场景下会对性能产生很大的影响 若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。

  • 间隙锁演示
select * from  xzll_student100 where   id > 1 and id <7 for update;

演示前的数据情况 : image.png

演示中 : image.png

结果:
image.png

1205 - Lock wait timeout exceeded; try restarting transaction, Time: 50.146000s

6. 表锁

Innodb中的行锁与表锁

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候只锁住一行呢？ 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

7. 死锁

  • 两个线程/事务 互相等待对放的锁

解除正在死锁的状态有两种方法

第一种：

  1. 查询是否锁表 show OPEN TABLES where In_use > 0;
  2. 查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
    show processlist
  3. 杀死进程id（就是上面命令的id列）
    kill id

第二种：

  1. 查看当前的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
  2. 查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  3. 查看当前等锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    杀死进程
    kill 进程ID
  • 产生死锁的四个必要条件：
    1.  互斥条件：一个资源每次只能被一个进程使用。
    2.  请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
    3.  不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
    4.  循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚的操作由应用程序重新提交。

注意: 下列方法有助于最大限度地降低死锁：

  1. 按同一顺序访问对象。
  2. 避免事务中的用户交互。
  3. 保持事务简短并在一个批处理中。
  4. 使用低隔离级别。
  5. 使用绑定连接。
ps:  本人在三年前有幸见过一次数据库死锁现象(线上级别的) 可惜年少无知 未曾记录当时的场景与原因 遗憾!!
(不过我知道怎么解决的嘿嘿)
正所谓好记性不如烂笔头啊!!!

mysql锁总结完毕----end~~~

~ 如有不对欢迎指正 不胜感激!

avatar
文章
阅读
粉丝