MySQL中的锁

一、对数据的操作类型划分

1、读锁

读锁也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。

2、写锁

写锁也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。

二、从数据操作的粒度划分

1、表锁

① 表级别的S锁、X锁

InnoDB存储引擎：一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 ，因为InnoDB存储引擎提供了行锁。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动 获取InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写：

  • LOCK TABLES 表名 READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。

  • LOCK TABLES 表名 WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。

② 意向锁 （intention lock）

意向锁是由存储引擎 自己维护的，用户无法手动操作意向锁，意向锁不会与行级的共享 / 排他锁互斥！

  • 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

  • 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

③ 自增锁（AUTO-INC锁）

我们可以为表的某个列添加 AUTO_INCREMENT 属性，该列便拥有了自增锁

④ 元数据锁（MDL锁）

MDL（Metadata Lock）保证读写的正确性，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁

2、行锁

① 记录锁（Record Locks）

记录锁也就是仅仅把一条记录锁上。

记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。

  • 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；

  • 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

② 间隙锁（Gap Locks）

gap锁的提出仅仅是为了防止插入幻影记录（幻读）而提出的

比如，把id值为8的那条记录加一个gap锁的示意图如下。

图中id值为8的记录加了gap锁，意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 ，其实就是id列的值(3, 8)这个区间的新记录是不允许立即插入的。

③ 临键锁（Next-Key Locks）

有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提出了一种称之为 Next-Key Locks 的锁

begin;
select * from student where id <=8 and id > 3 for update;

④ 插入意向锁（Insert Intention Locks）

一个事务在 插入 一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁 （ next-key锁也包含 gap锁 ），如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个 间隙 中 插入 新记录，但是现在在等待。

事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

3、页锁

页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我

们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销

介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

三、从对待锁的态度划分

1、悲观锁（Pessimistic Locking）

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁

比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。

Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现

2、乐观锁（Optimistic Locking）

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。

实现方式

  • 版本号机制

  • 时间戳机制

3、适用场景

  • 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。

  • 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。

四、按加锁的方式划分

1、隐式锁

  • 情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的 事务id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的的就是当前事务的 事务id ，如果其他事务此时想对该记录添加 S锁 或者 X锁 时，首先会看一下该记录的trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X锁 （也就是为当前事务创建一个锁结构， is_waiting 属性是 false ），然后自己进入等待状态也就是为自己也创建一个锁结构is_waiting 属性是 true ）。

  • 情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 PageHeader 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复 情景一 的做法。

隐式锁的逻辑过程如下：

  • InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。

  • 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务，首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)。

  • 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。

  • 等待加锁成功，被唤醒，或者超时。

  • 写数据，并将自己的trx_id写入trx_id字段。

2、显式锁

通过特定的语句进行加锁，我们一般称之为显示加锁，例如：

显式加共享锁：

select .... lock in share mod

显式示加排它锁：

select .... for update

五、其它锁

1、全局锁

全局锁就是对 整个数据库实例 加锁；让整个库处于 只读状态 ；

全局锁的典型使用 场景 是：做 全库逻辑备份 。

全局锁的命令：

Flush tables with read lock

2、死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环

mysql对于死锁提供了两种解决方案

  • 等待超时，超过一定时长放弃锁的获取

  • 发现有死锁，直接放弃锁的争夺

六、锁监控

1、innodb_row_lock

mysql> 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、使用information_schema库

MySQL把事务和锁的信息记录在了 information_schema 库中，涉及到的三张表分别是

MySQL5.7中：

  • INNODB_TRX :查询正在被锁阻塞的sql语句

  • INNODB_LOCKS：查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况

  • INNODB_LOCK_WAITS：查询锁等待情况

MySQL8.0中

  • INNODB_TRX：查询正在被锁阻塞的sql语句

  • DATA_LOCKS：不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁

  • DATA_LOCK_WAITS：查询锁等待情况