MySQL（七）—MySQL的锁（表锁、行锁）

锁，在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中，是协调多个进程或县城并发访问某一资源的一种机制。在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要。

一、锁概述

MySQL中有着Lock和Latch的概念，在数据库中，这两者都可以被称为“锁”，但是两者有着截然不同的含义。

图源见水印

Latch一般称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差，在InnoDB引擎中，Latch又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

Lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

1、锁的类型

对数据的操作其实只有读和写两种，而数据库在实现锁时，也会对这两种操作使用不同的锁；共享锁代表了读操作、互斥锁代表了写操作，所以我们可以在数据库中并行读，但是只能串行写，只有这样才能保证不会发生线程竞争，实现线程安全。

• 共享锁（读锁）：允许事务读一行数据。
• 排他锁（写锁）：允许事务删除或更新一行数据。

共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容。

2、锁的粒度

Lock锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有的锁粒度都不同。

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

二、MyISAM表锁

MYISAM存储存储引擎支持表锁，不支持事务处理，不支持外键
MYISAM并发比较简单，只支持表锁粒度，锁的粒度比较大并发能力，但是不会引起死锁，它支持表共享的读锁和表互斥的写锁。

对MYISAM表的读操作，不会阻塞其他用户对同一张表的读操作，但是会阻塞其他用户对同一张表的写操作
对MYISAM表的写操作，则会阻塞其他用户对同一个表的读和写操作
MYISAM的读与写之间互斥，写与写之间互斥，读与读之间共享

三、INNODB行锁

INNODB支持事务，支持外键，重要的是支持行及锁，并发程度高
INNODB实现两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他的事务获取相同的数据集的排他锁。
• 排它锁（X）：允许获得排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁

InnoDB在实现事务隔离级别的时候，采用的是一种叫做数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或MCC）机制（当然串行化除外），也经常称为多版本数据库。
MVCC机制会生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。

行锁：INNODB中行锁是通过给索引上的索引项加锁实现的，而不是给表中的行记录加锁，意味着，如果表中的行不存在索引，INNODB使用表锁，MySQL server实现加锁操作

• 主动获取锁：

  select ... for update; 
  //for update可以主动获取锁（X排它锁），直到事务提交完成
  

  • 窗口1进行查询

    select * from tb1 where id =1 for update;
    

  • 窗口2进行查询

    select * from tb1 where id =2 for update;
    

InnoDB中加索引和不加索引对应是是行锁和表锁的变化。

InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。

因为没有索引，存储引擎只能给所有的行都加锁，和表锁一样，把记录返回给MySQL Server，它会筛选出符合条件的行进行加锁，其余的行就会释放锁

验证INNODB没有索引时加的是表锁：

• 创建一个没有索引的测试表，并向表中添加数据不同数据

  mysql> create table test_innodb_lock(id int);
  Query OK, 0 rows affected (1.65 sec)
  mysql> insert into test_innodb_lock values(1),(2);
  Query OK, 2 rows affected (0.17 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  
  mysql> select * from test_innodb_lock;
  +------+
  | id   |
  +------+
  |    1 |
  |    2 |
  +------+
  2 rows in set (0.00 sec)
  

• 打开两个数据库操作窗口，设置为手动提交

  mysql> set @@autocommit=0;
  Query OK, 0 rows affected (0.00 sec)
  mysql> select @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            0 |
  +--------------+
  1 row in set (0.00 sec)
  

• 对test_innodb_lock进行数据操作，并强行获取排它锁

  mysql> select * from test_innodb_lock where id=1 for update;
  +------+
  | id   |
  +------+
  |    1 |
  +------+
  1 row in set (0.06 sec)
  

• 再用另一个窗口，对另外的数据进行操作，并强行获取排它锁

  mysql> select * from test_innodb_lock where id=2 for update;
  //看到这条sql语句一直卡在这，不结束也不报错误，等待获取锁
  //等待获取锁当中，证明左边窗口事务1在select id=1的记录时，获取的是表锁，而不是行锁！
  //因此我们可以得知在没有索引的情况下，INNODB加的表锁
  

  由于InnoDB的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是InnoDB引擎下表的不同行，但是如果使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行。

四、死锁

INNODB会产生死锁。
MyISAM 表锁是 deadlock free 的， 这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。
但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。

对表tb1和tb2进行数据查询，有两个窗口进行操作；
设置为的手动提交事务
窗口1：先查询tb1,在查询tb2,在进行事务提交commit
窗口2：先查询tb2,在查询tb1,在进行事务提交commit

（MySQL客户端命令行窗口1）事务1
（MySQL客户端命令行窗口2）事务2

• 创建table1

  mysql> set autocommit=0;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> create table table1(id int);
  Query OK, 0 rows affected (0.49 sec)
  
  mysql> insert into table1 values(1);
  Query OK, 1 row affected (0.14 sec)
  

• 创建table2

  mysql> set autocommit=0;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> create table table2(id int);
  Query OK, 0 rows affected (0.36 sec)
  
  mysql> insert into table2 values(2);
  Query OK, 1 row affected (0.00 sec)
  

• 在窗口1 执行操作

  mysql> select * from table1 where id=1 for update;
  +------+
  | id   |
  +------+
  |    1 |
  +------+
  1 row in set (0.00 sec)
  
  mysql> select * from table2 where id=2 for update;
  +------+
  | id   |
  +------+
  |    2 |
  +------+
  1 row in set (0.05 sec)
  

• 在窗口2执行操作
  
  

  mysql> select * from table2 where id=2 for update;
  //可以看出，由于table2的锁已经被事务2获取，所以事务1等待，发生死锁！
  

• 窗口1操作

  mysql> select * from table2 where id=2 for update;
  +------+
  | id   |
  +------+
  |    2 |
  +------+
  1 row in set (0.00 sec)
  
  MySQL自动检测死锁问题，把当前事务回滚，释放该事务持有的锁，此时左边的事务1就能够获取table2的表锁，执行select语句。
  

在上面的例子中，两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

这样的死锁问题，一般都是我们自己的应用造成的，和Java SE多线程死锁的情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题，因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题！