「MySQL高级篇」MySQL锁机制&事务

引言

锁锁锁，到哪到离不开这桩琐事，并发琐事，redis琐事，如今是MySQL琐事，这其中琐事，还跟MySQL另一个重要的东西--事务息息相关。

这篇将从以下几点，带你解开这把爱情的苦锁：

🎏本篇速览脑图

🎯常规表锁&行锁

这一部分较为常规，若有前置知识，可以直接跳到下边的【表级锁扩展】部分开始阅读 建议借助侧边栏，有emoji表情的属于重点

锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁分类

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。

2） 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁(了解)
MyISAM	支持	不支持	不支持
InnoDB	支持	支持(默认)	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这3种锁的特性可大致归纳如下 ：

锁类型	特点
表级锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

粒度小，自然发生锁冲突的概率就低

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

如何加表锁

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

显示加表锁语法：

加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；

读锁案例

准备环境

create database demo_03 default charset=utf8mb4;

use demo_03;

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');

CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

读操作

客户端一对book表加了锁，并拿到了book表的锁，在该锁未释放之前，不能去查别的表；
而客户端二能查到book和其他表，是因为读锁是共享锁，他并没有真正拿到这把锁，自然可以肆意妄为，不受未释放锁的束缚；

写操作

• 客户端①直接报错，因为读锁会排斥写操作

• 客户端②陷入了阻塞状态，得等待客户端①释放锁

   

  

• unlock后，客户端②的写操作就能正常执行了。

   

  

总结

• 读锁对于加锁的客户端：会限制对其他表的查询以及对任何表的写操作
• 读锁对于其他客户端：不会限制任何查询，但会阻塞对该表的写操作

🎈助记

自己拿到了读锁，那自己当然不能再去读其他表，而又因为读锁不会影响到其他客户端读的结果，那其他客户端自然可以任意读。

而对于写操作：自己还在读，就别想着去做写操作了！而对于其他客户端，如果对该表写操作。肯定会影响到当前客户端的读取结果，所以其他客户端不能对该表进行写操作

• 简而言之：自己不能三心二意【操作其他表】，而对他人则考虑自己所做的操作会不会导致两个客户端拿到不一致的数据，会的话就是不允许的。

写锁案例

客户端 一 :

1）获得tb_book 表的写锁

lock table tb_book write ;

2）执行查询操作

select * from tb_book ;

3）执行更新操作

update tb_book set name = 'java编程思想（第二版）' where id = 1;

更新操作执行成功 ；

客户端二 :

4）执行查询操作

select * from tb_book ;

• 陷入阻塞状态，因为写锁是排他锁，排斥其他客户端的写和读操作。