mysql锁

Innodb中如何解决 可重复读(RR) 中产生幻读的情况

锁模型

  1. LBCC (Lock Based Concurrency Control) 读之前加个锁，但这样可能会导致性能问题 => 读的时候加锁导致其他事务都不能读写了，性能低下
  2. MVCC(Multi Version Concurrency Control) 读的时候记录当时快照，别人来读取快照就行 => 性能消耗，存储消耗

这两种方案在Innodb中结合使用。RC的MVCC实现是对 同一个事务的多个读 创建一个版本 而 RR 是 同一个事务任何一条都创建一个版本

通过MVCC与LBCC的结合，InnoDB能解决对于不加锁条件下的 幻读的情况。而不必像 Serializable 一样，必须让事务串行进行，无任何并发。

MVCC在Innodb的实现

一、Innodb 地锁

    1. Shared and Exclusive Locks 共享和排它锁 =>（S、X）
    2. Intention Locks 意向锁 => 这里指的是两把锁，其实就是表级别的 共享和排它锁 => (IS、IX)

 

上面这四把锁是最基本锁的类型

    1. Record Locks 记录锁
    2. Gap Locks 间隙锁
    3. Next-key Locks 临锁

 

这三把锁，理解成对于上面四把锁实现的三种算法方式，我们这里暂且把它们称为：高阶锁

  1. Insert Intention Locks 插入锁
  2. AUTO-INC Locks 自增键锁
  3. Predicate Locks for Spatial Indexes 专用于给Spatial Indexes用的

上面三把是额外扩展的锁

二、读写锁深入解释

  1. 要使用共享锁，在语句后面加上lock in share mode 。排它锁默认 Insert、Update、Delete会使用。显示使用在语句后加for update。
  2. 意向锁都是由数据库自己维护的。（主要作用是给表打一个标记，记录这个表是否被锁住了） => 如果没有这个锁，别的事务想锁住这张表的时候，就要去全表扫描是否有锁，效率太低。所以才会有意向锁的存在。

补充：Mysql中锁，到底锁的是什么

锁的是索引，那么这个时候可能有人要问了：那如果我不创建索引呢？

索引的存在，我们上面讲过了，这里再回顾一下，有下面几种情况

  1. 你建了一个 Primary key， 就是聚集索引 （存储的是 完整的数据）
  2. 没有主键，但是有一个 Unique key 而是都不是 null的，则会根据这个 key来创建 聚簇索引
  3. 那上面两种都没有呢，别担心，innodb自己维护了一个叫 rowid 的东西，根据这个id来创建 聚簇索引

所以一个表里面，必然会存在一个索引，所以锁当然总有索引拿来锁住了。

当要给一张你没有显式创建索引的表，进行加锁查询时，数据库其实是不知道到底要查哪些数据的，整张表可能都会用到。所以索性就锁整张表。

如果是给辅助索引加写锁，比如select * from where name = ’xxx‘ for update 最后要回表查主键上的信息，所以这个时候除了锁辅助索引还要锁主键索引

发生死锁后的检查

  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_waits 一共出现过多少次等待
  2. select * from information_schema.INNODB_TRX 能查看到当前正在运行和被锁住的事务
  3. show full processlist = select * from information_schema.processlist 能查询出是 哪个用户 在哪台机器host的哪个端口上 连接哪个数据库 执行什么指令 的 状态与时间

死锁预防

  1. 保证访问数据的顺序
  2. 避免where的时候不用索引（这样会锁表，不仅死锁更容易产生，而且性能更加低下）
  3. 一个非常大的事务，拆成多个小的事务
  4. 尽量使用等值查询（就算用范围查询也要限定一个区间，而不要只开不闭，比如 id > 1 就锁住后面所有）

粒度锁

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

  • MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
  • BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
  • InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

不同粒度锁的比较：

    • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
      • 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
      • 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
    • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
      • 最大程度的支持并发，同时也带来了最大的锁开销。
      • 在 InnoDB 中，除单个 SQL 组成的事务外，
        锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
      • 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
    • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 

  • 死锁（Deadlock Free）

    • 死锁产生：
      • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
      • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
      • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
    • 检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
    • 死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
    • 外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
    • 死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

    MyISAM避免死锁：

    • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

    InnoDB避免死锁：

    • 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
    • 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
    • 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
    • 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
    • 改变事务隔离级别

    如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

    一些优化锁性能的建议

    • 尽量使用较低的隔离级别；
    • 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
    • 选择合理的事务大小，小事务发生锁冲突的几率也更小
    • 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
    • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
    • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
    • 不要申请超过实际需要的锁级别
    • 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
    • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能
posted @ 2020-12-22 16:47  Mr.peter  阅读(98)  评论(0)  编辑  收藏  举报