Mysql锁机制

发表于 | 更新于: | 分类于 ， |
字数统计: 4.7k | 阅读时长 ≈ 16

总览

MySQL/InnoDB的加锁，一直是一个常见的话题。例如，数据库如果有高并发请求，如何保证数据完整性？产生死锁问题如何排查并解决？下面是不同锁等级的区别

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

  • 页面锁
    开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

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

查看数据库支持的存储引擎：

SHOW ENGINES

InnoDB存储引擎

乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。

何谓数据版本？

即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。

  • 当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。

  • 当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

举例：
1、数据库表三个字段，分别是id、value、version

select id,value,version from TABLE where id = #{id}

2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}

悲观锁

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。

另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

共享锁

共享锁又称读锁 (read lock)，是读操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。如下图所示。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。

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

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

排它锁

排他锁Exclusive Lock（也叫writer lock）又称写锁。

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取, 不能进行写操作，需等待其释放。

若事务 1 对数据对象A加上X锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事物 1 释放A上的锁。这保证了其他事务在事物 1 释放A上的锁之前不能再读取和修改A。排它锁会阻塞所有的排它锁和共享锁.

读取为什么要加读锁呢？

防止数据在被读取的时候被别的线程加上写锁。

排他锁使用方式：在需要执行的语句后面加上for update就可以了

1
select status from TABLE where id=1 for update;

排他锁，也称写锁，独占锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

排他锁之所以能阻止update,delete等操作是因为update，delete操作会自动加排他锁，
也就是说即使加了排他锁也无法阻止select操作。而select XXX for update 语法可以对select操作加上排他锁。 所以为了防止更新丢失可以在select时加上for update加锁, 这样就可以阻止其余事务的select for update (但注意无法阻止select).

要使用排他锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

行锁

多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题。

1
2
3
4
5
6
7
8
9
10
# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;

# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;  ## 被事务A阻塞
Query OK, 1 row affected (37.51 sec)

现实：当执行批量修改数据脚本的时候，行锁升级为表锁。其他对订单的操作都处于等待中，，，

原因：InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁！ 而模拟操作正是通过id去作为检索条件，而id又是MySQL自动创建的唯一索引，所以才忽略了行锁变表锁的情况

总结：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

  • 行锁的劣势
    开销大；加锁慢；会出现死锁

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

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

从上面的案例看出，行锁变表锁似乎是一个坑，可MySQL没有这么无聊给你挖坑。这是因为MySQL有自己的执行计划。

当你需要更新一张较大表的大部分甚至全表的数据时。而你又傻乎乎地用索引作为检索条件。一不小心开启了行锁(没毛病啊！保证数据的一致性！)。可MySQL却认为大量对一张表使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突问题，性能严重下降。所以MySQL会将行锁升级为表锁，即实际上并没有使用索引。

我们仔细想想也能理解，既然整张表的大部分数据都要更新数据，在一行一行地加锁效率则更低。其实我们可以通过explain命令查看MySQL的执行计划，你会发现key为null。表明MySQL实际上并没有使用索引，行锁升级为表锁也和上面的结论一致。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

行锁根据锁定范围又分为间隙锁、临键锁和记录锁。

临健锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的临键锁（Next-Key锁）。

危害(坑)：若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。

间隙锁

记录锁


记录锁在锁定非主键索引时，也会一并锁定主键；

表锁

Innodb 的行锁是在有索引的情况下，没有索引的表是锁定全表的。

在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候只锁住一行呢？

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

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

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

死锁（Deadlock）

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

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

  • 1、查询是否锁表 

    1
    show OPEN TABLES where In_use > 0;

  • 2、查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程） 

    1
    show processlist

  • 3、杀死进程id（就是上面命令的id列） 

    1
    kill id

第二种：

  • 1、查看当前的事务 
1
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

  • 2、查看当前锁定的事务 

    1
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

  • 3、查看当前等锁的事务 

    1
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

  • 4、杀死进程 

    1
    kill 进程ID

产生死锁的四个必要条件：

  • 互斥条件：一个资源每次只能被一个进程使用。
  • 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
  • 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
  • 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚的操作由应用程序重新提交。

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

  • 按同一顺序访问对象。
  • 避免事务中的用户交互。
  • 保持事务简短并在一个批处理中。
  • 使用低隔离级别。
  • 使用绑定连接。

MyISAM存储引擎

InnoDB和MyISAM的最大不同点有两个：

  • InnoDB支持事务(transaction)；MyISAM不支持事务
  • Innodb默认采用行锁， MyISAM是默认采用表锁。

MyISAM不适合高并发

共享读锁

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读操作，但会阻塞对同一表的写操作。只有当读锁释放后，才能执行其他进程的写操作。在锁释放前不能读其他表。

独占写锁

对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。在锁释放前不能写其他表。

总结：

  • 表锁，读锁会阻塞写，不会阻塞读。而写锁则会把读写都阻塞。

  • 表锁的加锁/解锁方式：MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作 (UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

如果用户想要显示的加锁可以使用以下命令：
锁定表

1
LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解锁表

1
UNLOCK TABLES

在用 LOCK TABLES 给表显式加表锁时, 必须同时取得所有涉及到表的锁。

在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表;

如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

在自动加锁的情况下也基本如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。

对表test_table增加读锁：

1
2
LOCK TABLES test_table READ 
UNLOCK test_table

对表test_table增加写锁

1
2
LOCK TABLES test_table WRITE
UNLOCK test_table

当使用 LOCK TABLES 时，不仅需要一次锁定用到的所有表,而且,同一个表在 SQL 语句中出现多少次，就要通过与 SQL 语句中相同的别名锁定多少次，否则也会出错！
比如如下SQL语句：

1
select a.first_name,b.first_name, from actor a,actor b where a.first_name = b.first_name;

该Sql语句中，actor表以别名的方式出现了两次，分别是a,b，这时如果要在该Sql执行之前加锁就要使用以下Sql:

1
lock table actor as a read, actor as b read;

并发插入

上文说到过 MyISAM 表的读和写是串行的, 但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。 MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。

0: 不允许并发插入;
1: 如果MyISAM表中没有空洞(即表的中间没有被删除的行), MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL 的默认设置;
2: 无论MyISAM表中有没有空洞, 都允许在表尾并发插入记录;

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。

MyISAM的锁调度

MyISAM 存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，MySQL 如何处理呢?

答案是写进程先获得锁。

不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为 MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！

幸好我们可以通过一些设置来调节 MyISAM 的调度行为。

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

  • 通过执行命令SET LOWPRIORITYUPDATES=1, 使该连接发出的更新请求优先级降低。

  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性, 降低该语句的优先级。

  • 另外, MySQL也供了一种折衷的办法来调节读写冲突, 即给系统参数max_write_lock_count 设置一个合适的值, 当一个表的读锁达到这个值后, MySQL就暂时将写请求的优先级降低, 给读进程一定获得锁的机会。

总结

  • MySQL的MyISAM引擎支持表级锁。

  • 表级锁分为两种：共享读锁、互斥写锁。这两种锁都是阻塞锁。

  • 可以在读锁上增加读锁，不能在读锁上增加写锁。在写锁上不能增加写锁。

  • 默认情况下，MySql在执行查询语句之前会加读锁，在执行更新语句之前会执行写锁。

  • 如果想要显示的加锁/解锁的花可以使用LOCK TABLES和UNLOCK来进行。

  • 在使用LOCK TABLES之后，在解锁之前，不能操作未加锁的表。

  • 在加锁时，如果显示的指明是要增加读锁，那么在解锁之前，只能进行读操作，不能执行写操作。

  • 如果一次Sql语句要操作的表以别名的方式多次出现，那么就要在加锁时都指明要加锁的表的别名。

  • MyISAM存储引擎有一个系统变量concurrent_insert, 专门用以控制其并发插入的行为,其值分别可以为0、1或2。

  • 由于读锁和写锁互斥，那么在调度过程中，默认情况下，MySql会本着写锁优先的原则。可以通过low-priority-updates来设置。

利用锁实现事务

脏读

修改加上排他锁，那么其他事务就无法读取到正在修改的数据行；

不可重复读

查询加上读锁，则其他事务就无法修改；

幻读

范围查询会通过间隙锁使其他事务无法插入范围数据，这不会出现幻读；

参考

全面了解mysql锁机制（InnoDB）与问题排查