MySQL并发控制

介绍

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。而这种并发问题的直接后果就是数据不一致问题。那么如何解决这种问题呢？Mysql需要采用锁定机制来解决。 mysql数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

全局锁、表级锁、行级锁。

1. 按照锁的粒度:

• 全局锁，锁的是整个database，使用场景：数据备份。
• 表记锁，锁的是整个table。
• InnoDB和xtradb存储引擎支持。行级锁，锁的是某N行数据。

2. 按照锁的功能：共享读锁和排他写锁。
3. 除此之外，还有悲观锁（排他写锁）和乐观锁（使用某一版本列或者唯一列进行逻辑控制）的定义。

• Mysql的InnoDB和MyISAM存储引擎最大的区别：事务的支持、行级锁的支持。

行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

表级锁定（table-level）

和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

• 总的来说，MySQL这两种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

表锁

介绍

• 使用场景
  1. 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
  2. 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
• MySQL的表级锁有两种：
  1. 一种是表锁。
  2. 一种是元数据锁（meta data lock，MDL)。
• 表锁有两种表现形式
  1. 表共享读锁（Table Read Lock）
  2. 表独占写锁（Table Write Lock）
• 查看表级锁定的争用状态变量 mysql> show status like "table%";

  	* table_locks_immediate：产生表级锁定的次数；
  	* table_locks_waited：出现表级锁定争用而发生等待的次数；
  

• 手动增加表锁 lock table 表名称 read(write),表名称2 read(write)，其他;
• 查看表锁情况 show open tables;
• 删除表锁 unlock tables;

加表锁表现:

• lock table mytable read;
  • 当前session1
    1. 可以查询
    2. 不能查询其他的表
    3. 插入更新锁定表会提示错误
  • 其他session2
    1. 可以查询其他或者更新其他的表
    2. 更新锁定表会处于等待获得锁 ( session1 释放锁后执行)
• lock table mytable write;
  • 当前session1
    1. 对当前锁定表可以执行 增/删/改/查
  • 其他session2
    1. 查询锁定的表会被阻塞, 需要等待到表锁被释放