MySQL进阶（数据库引擎）——MyISAM和InnoDB引擎的区别

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）

正文

（1）MySQL大致可以归纳为以下3种锁

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

（2）表锁

MyISAM会在执行select语句前，会自动给涉及的表加读锁，在执行增删改操作前会自动给涉及的表加写锁。

• MySQL的表锁有两种模式：

• 表共享读锁
• 表独占写锁

• 读锁会阻塞写，写锁会阻塞读和写。

• 对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它线程的写操作。
• 对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

读锁的演示

窗口1执行

窗口2的语句被阻塞

关闭窗口1，窗口2才执行成功

执行unlock tables释放锁

释放后才成功

create table dept(
deptno int not null auto_increment,
dname varchar(20),
loc varchar(20),
primary key(deptno)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

打开两个会话窗口

窗口1执行以下语句

lock table dept read;
select * from dept;

窗口2执行执行以下语句

select * from dept;
insert into dept values(null,‘财务部’,‘北京’);

此时，窗口2的请求被阻塞，必须等待会话1释放锁后才能执行;

释放会话1的锁，并观察会话2的执行结果。

unlock tables;

写锁的演示

会话1加写锁，会话2读操作，会被阻塞

会话1 释放锁

会话1加写锁

lock table dept write;
delete from dept where deptno = 1;

会话2读操作，会被阻塞

select * from dept;

会话1 释放锁

unlock tables;

观察会话2 查询结果

注意：如果持有表锁的session异常终止的话（比如说执行了“ctrl+z”），那么该session是不会主动释放锁的，这时候我们可以重启mysql服务器，不推荐。可以通过show processlist 命令来查看线程ID，通过kill 【线程ID】

总结：MyISAM不适合写表的引擎，写锁后，其它线程不能做任何操作。

（3）行锁

会出现死锁，发生锁冲突几率低，并发高。

• MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引选择的行上的，如果SQL语句没有走索引，则会进行全表扫描，行锁则无法实现，取而代之的是表锁；此时其它事物无法对当前表进行更新操作。
• 如果使用的是非主键索引，则行锁转为表锁。

新建一张表

插入两条数据

会话1的事务未提交，由于不是同一行，所以会话2

会话1 ，执行update，事务未提交。

start transaction;
update mylock set name = ‘hello’ where id = 1;

会话2 ，执行update，由于是通过主键更新，为行级锁；

#会话1和会话2更新的不是同一行数据，会话2可以执行成功

update mylock set name = ‘world’ where id = 2;

会话2 ，执行下面update语句，则会进行阻塞，

必须等待会话1提交事物释放锁。

update mylock set name = ‘test’ where id = 1;

上述案例的代码

create table mylock(
id int not null auto_increment,
name varchar(20),
update_time datetime,
primary key(id)
)engine=innodb default charset=utf8

会话1 ，执行update，事务未提交。

start transaction;
update mylock set name = ‘hello’ where id = 1;

会话2 ，执行update，由于是通过主键更新，为行级锁；

会话1和会话2更新的不是同一行数据，会话2可以执行成功。

update mylock set name = ‘world’ where id = 2;

会话2 ，执行下面update语句，则会进行阻塞，

必须等待会话1提交事物释放锁。

update mylock set name = ‘test’ where id = 1;

会话1执行commit之后，绘画2事务才能执行

会话1 ，执行update,事务未提交，

由于通过非主键或索引选中的，升级为表锁。

start transaction;
update mylock set update_time =‘2000-10-1’ where name=‘hello’

commit

会话2，无法执行写操作，必须等待会话1的事务提交。

update mylock set name=‘abc’ where id = 1;

显示加行锁的两种方式，行锁又分为共享锁和排他锁。

• 共享锁（乐观锁|S锁）：允许不同事务之间共享加锁读取，但不允许其他事务修改或者加入排他锁

select * from user where id=20 lock in share mode

• 排他锁（悲观锁|X锁）：当一个事务加入排他锁后，不允许其他事务加共享锁或排他锁读取

select * from user where id= 20 for update

• 行锁的前提有两个：1、必须是mysql的innoDb表。2、必须开启transaction事务。两者都有，锁才会生效。
• 若一个线程for update执行锁住某行数据，其他线程读取的时候，sql里没有for update，则可以正常读取。

如果是修改，则会被阻塞

只有会话1执行commit之后，会话2的update才成功

会话1 ，开启事物执行如下sql，事务未提交，开启读锁

start transaction;
select * from mylock where id=1 lock in share mode;

会话2 ，允许读操作

select * from mylock where id=1;

会话2，执行update写操作被阻塞，

必须等待会话1的事物提交释放锁。

update mylock set name = ‘test’ where id =1;

会话1，开启事务，执行如下sql，事务未提交，开启写锁

start transaction;
select * from mylock where id=1 for update;

会话2，执行读和写操作，会发生阻塞

select * from mylock where id=1 for update;
update mylock set name=‘test’ where id=1;

（4）产生死锁

• 表锁不会产生死锁
• 在Innodb中，行级锁并不是直接锁记录，而是锁索引。
• 索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引。
• 如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

创建一个发生死锁的情景，在Session A和Session B中分别执行两个事务，具体情况如下：

MySQL 中事务的开始语句为 START TRANSACTION 或 BEGIN。这两个语句的效果是相同的,都可以用来开始一个新的事务。

时间编号	SessionA	SessionB
1	BEGIN;
2		BEGIN;
3	SELECT * FROM mylock where id = 1 FOR UPDATE
4		SELECT * FROM mylock where id = 2 FOR UPDATE
5	SELECT * FROM mylock where id =2 FOR UPDATE 发生阻塞
6		SELECT * FROM mylock WHERE id = 1 FOR UPDATE ; 死锁发生，纪录日志，回滚一个事物

死锁产生，会回滚事务

分析：

1. 从第③步中可以看出，Session A中的事务先对mylock表聚簇索引的id值为1的记录加了一个X型锁。
2. 从第④步中可以看出，Session B中的事务对mylock表聚簇索引的id值为2的记录加了一个X型锁。
3. 从第⑤步中可以看出，Session A中的事务接着想对mylock表聚簇索引的id值为2的记录也加了一个X型锁，但是与第④步中Session B中的事务加的锁冲突，所以Session A进入阻塞状态，等待获取锁。
4. 从第⑥步中可以看出，Session B中的事务想对mylock表聚簇索引的id值为1的记录加了一个X型锁，但是与第③步中Session A中的事务加的锁冲突，而此时Session A和Session B中的事务循环等待对方持有的锁，死锁发生，被MySQL服务器的死锁检测机制检测到了，所以选择了一个事务进行回滚，并向客户端发送一条消息：

1213 - Deadlock found when trying to get lock; try restarting transaction

以上是我们从语句加了什么锁的角度出发来进行死锁情况分析的，但是实际应用中我们可能压根儿不知道到底是哪几条语句产生了死锁，我们需要根据MySQL在死锁发生时产生的死锁日志来逆向定位一下到底是什么语句产生了死锁。

（5）查看死锁日志

SHOW ENGINE INNODB STATUS命令来查看关于InnoDB存储引擎的状态信息，其中就包括了系统最近一次发生死锁时的加锁情况

| InnoDB | |

2023-10-25 10:48:04 0x3bd4 INNODB MONITOR OUTPUT

Per second averages calculated from the last 50 seconds

BACKGROUND THREAD

srv_master_thread loops: 23 srv_active, 0 srv_shutdown, 18353 srv_idle
srv_master_thread log flush and writes: 0

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 77
OS WAIT ARRAY INFO: signal count 76
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 4, rounds 29, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 7.25 RW-excl, 0.00 RW-sx

LATEST DETECTED DEADLOCK

#=======【死锁发生的时间】，后边的一串十六进制表示的操作系统为当前session分配的线程的线程id

2023-10-25 10:37:39 0x29f8
*** (1) TRANSACTION:

#=======为事务分配的id为419443，事务处于ACTIVE状态已经123秒了，
#=======事务现在正在做的操作就是：“starting index read”

TRANSACTION 419443, ACTIVE 123 sec starting index read

#=======此事务使用了1个表，为1个表上了锁

mysql tables in use 1, locked 1

#=======此事务处于LOCK WAIT状态，拥有3个锁结构（2个行锁结构，1个表级别X型意向锁结构)

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
ex read”

TRANSACTION 419443, ACTIVE 123 sec starting index read

#=======此事务使用了1个表，为1个表上了锁

mysql tables in use 1, locked 1

#=======此事务处于LOCK WAIT状态，拥有3个锁结构（2个行锁结构，1个表级别X型意向锁结构)

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）
[外链图片转存中…(img-kKhtgbpf-1713339121786)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！