【MySQL】锁机制

锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

Mysql用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。

MySQL锁机制

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，

• MyISAM：当操作数据时，MyISAM引擎下锁住的是整张表。所以也称之为表锁。它主要是作用于数据，而数据存储在一张表中，所以会锁住整张表。
• InnoDB：InnoDB的锁相比于MyISAM有些特殊：它即支持行锁也支持表锁。原因就是InnoDB的行锁是根据索引项的记录添加的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。

常见的MySQL锁：

• Record Locks（记录锁）：在索引记录上加锁。
• Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
• Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于 是Record Locks与Gap Locks的一个结合。
• 共享读锁和排他写锁：
  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的。
• 行锁和表锁：
  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 虽然这列出了很多，但大家要仔细看仔细理解这只是从不同维度对锁的分类，而起了一些不同的名字罢了所以不要被吓到！

InnoDB引擎下的锁

InnoDB的锁相比于MyISAM有些特殊：它即支持行锁也支持表锁。原因就是 InnoDB的行锁是根据索引项的记录添加的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。

锁机制：表锁：读锁、写锁；行锁：读锁、写锁；间隙锁：必须和行锁同时出现。

• 行锁：操作的数据肯定是在表中某一行或者某几行，那么如果我们只锁住这某几行数据就是行锁，例如：
  • select * from user where id >= 10 and id < 15;锁住5行数据；
  • select * from user where id = 10;锁住1行数据；
• 表锁：把数据所在的整张表锁住，就是表锁。

证明InnoDB即支持行锁，也支持表锁：

• 前期准备：
  • create table test_innodb_lock1(id int primary key,name varchar(10));
  • Insert into test_innodb_lock1 values(1,’Tom’),(2,’Jack’);
  • Select * from test_innodb_lock1;
• 接着，我们需要开启两个MySQL窗口，方便观察。并且两个窗口均需要开启事务。
• 第一步：开启事务
  • A窗口：set autocommit=0;
  • B窗口：set autocommit=0;
• 第二步：
  • A窗口：select * from test_innodb_lock1 where id = 1 for update；
    • 行锁分为：读锁（共享锁）和写锁（排他锁）；
    • 表锁分为：读锁（共享锁）和写锁（排他锁）；
    • 特点：读读不互斥，读写互斥，写写互斥（互斥即只能有一个操作成功）；
    • select 主要执行的是读锁（读操作、对数据不造成修改的操作）；
    • update/insert/delete 写锁（写操作、对数据造成改变的操作）；
    • for update 将默认形式的读操作强制改成写操作。使这条SQL语句可以获取到写锁，但是又不希望数据修改。
  • B窗口：select * from test_innodb_lock1 where id = 2 for update；

通过id属性查询发现数据查询依然成功，原因是InnoDB的行锁是根据索引项的记录添加的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。所以此时InnoDB执行的是行锁，由于所查询的是不同行的属性，所以此时可以查询成功。

• 接下来改变属性进行查询
  • A窗口：select * from test_innodb_lock1 where name = ‘Tom’ for update；
  • B窗口：select * from test_innodb_lock1 where name = ‘Jack’ for update；

通过name属性索引查询发现查询失败，原因是InnoDB的行锁是根据索引项的记录添加的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。所以此时InnoDB执行的是表锁，由于将读锁改变成为了写锁，且写写互斥，所以此时查询失败。

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

死锁：

MyISAM中的表锁不会出现死锁，InnoDB中的行锁会出现死锁。原因是MyISAM不支持事务，所以MyISAM引擎不会拿着锁不释放，使用完不需要commit/rollback直接释放表锁，因此不会出现死锁。

那么什么是死锁呢？

所谓死锁，是指两个或者两个以上线程在执行过程中，因争夺资源而产生互相等待的现象，若无外力作用，他们都将无法推进下去，此时，称系统处于死锁。

死锁演示：

• 第一步：
  • A窗口：
    • set autocommit = 0；
    • create table table1(id int primary key)；
    • insert into table1 values(1)；
  • B窗口:
    • set autocommit = 0；
    • create table table2(id int primary key)；
    • insert into table2 values(2)；
• 第二步：
  • A窗口：
    • select * from table1 where id = 1 for update; //1锁
  • B窗口：
    • select * from table2 where id = 2 for update; //2锁
  • A窗口：
    • select * from table2 where id = 2 for update; //2锁
    • 被阻塞（此时2锁正在被B窗口执行、A窗口在等待B窗口释放2锁）
  • B窗口：
    • select * from table1 where id = 1 for update; //1锁
    • 被阻塞（此时1锁正在被A窗口执行、B窗口在等待A窗口释放1锁）

间隙锁（Gap Locks）：

间隙锁（Gap Locks）：在索引存在的情况下，在添加行锁的基础上会添加间隙锁。原因是行锁是在有索引项的情况下加行锁，而索引是通过表中的数据建立的，由于表中的数据之间存在间隙，需要对间隙进行加锁，此时就需要间隙锁。

证明间隙锁：

• 前提准备：
  • create table test_innodb_lock1(id int primary key)；
  • insert into test_innodb_lock1 values(1),(4)；
  • select * from test_innodb_lock1；
• 开启事务：A窗口/B窗口：set autocommit = 0；
• 第三步：
  • A窗口：select * from test_innodb_lock1 where id > 1 for update；
  • B窗口：insert into test_innodb_lock1 values(3)；