MySQL事务及锁机制

Marvin-Fox

已于 2024-09-04 11:22:01 修改

阅读量1.3k

点赞数 1

分类专栏： Database 文章标签： mysql 数据库 java

于 2020-05-04 15:20:03 首次发布

本文链接：https://blog.csdn.net/fox_bert/article/details/105059116

版权

Database 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、事务的四大特性（ACID）

原子性（Atomicity）：不可拆分，全部成功或全部失败
一致性（Consistent）：完整性约束不能被破坏（自定义完整性、数据库自身完整性）
隔离性（Isolation）：并发情况下，行和表的操作，相互不影响，相互隔离开
持久性（Durable）：提交成功，就是永久性的。不能服务一重启后就变回原来的、

二、事务提交的三大问题

脏读、幻读、不可重复读

场景：两个事物对同一数据进操作

1、脏读

指的是一个事务读到了其他事务未提交的数据

没有提交的数据是保存在内存的缓冲池里面的，因为只有commit才会将数据写入到磁盘。

在内存中的数据叫脏数据，因为有可能会回滚；使用会带来数据一致性的问题

2、不可重复读（update、delete）

前后两次执行相同的查询语句，但是得到不一样的结果

因为读取到了其他事务已提交的数据造成的（update、delete）

如果事务A 按一定条件搜索，期间事务B 删除或者修改了符合条件的某一条数据，导致事务A 再次读取时数据少了一条。这种情况归为不可重复读

3、幻读（insert）

官方文档表示：只有增加行是幻读，而修改和删除是不可重复读

一个事务在前后两次查询同一个范围时，后一次查询看到了前一次查询没有看到的行

事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert

三、事物隔离级别（transaction isolation）

read uncommitted（读未提交）
read committed（读已提交）：解决脏读
repeatable read（可重复读）：解决脏读、不可重复读
serializable（串行化）：解决事务并发的所有问题

MySQL默认使用：repeatable read（可重复读）

可以查看当前DB的事务隔离级别：show global VARIABLES like "tx_isolation"

四、事务隔离级别可重复读的解决方案

1、LBCC（加锁读）：

在读取数据前，对其加锁，阻止其他事务对数据进行修改---Lock Based Concurrency Control（LBCC）

一个事务读的时候，其他事务不能做写操作

2、MVCC（不加锁读）：

读不加锁，读写不冲突，保证事务可重复读，并且不影响其他事务写

生成一个数据请求时间点的一致性数据快照（snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取---Multi Version Concurrency Control（MVCC）

一个事务读的时候，不影响其他事务做写操作

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

问：MVCC如何同时保证事务可重复读、不影响其他事务写

答：innodb默认是支持MVCC的，在每行数据后面都会有几个隐藏字段如下，一次事务为一个版本号，事务ID会递增

一个事务内查询到数据的符合条件（只能查找到）并且关系

创建版本小于等于当前事务ID的数据
删除版本大于当前事务ID的行（或未删除）

模拟举例：

1、初始化：事务1在DB中初始化了两条数据

隐藏字段：

创建版本号：这两条数据是这一个事务提交的，所以创建版本号相同，都为1

2、事务2查询：第一次查询（本次事务id为2）

可以查到两条（注意：此次事务先不结束）

SQL有begin开始没有end结束

3、插入：事务3插入了一条数据，并且提交

隐藏字段：

创建版本号：会记录为当前的事务ID 为3

4、事务2查询：第二次查询（本次事务id为2）

一个事务内查询到数据的符合条件（只能查找到）并且关系

创建版本小于等于当前事务ID的数据
删除版本大于当前事务ID的行（或未删除）

依然只能查到两条（注意：此次事务先不结束）

因为事务3插入的数据创建版本（3）大于当前事务（2），所以其他事务添加的数据不能被查出

tom:创建时间大不满足

5、删除：事务4删除了一条数据，并且提交

隐藏字段：

删除版本号：会记录为当前的事务ID 为4

6、事务2查询：第三次查询（本次事务id为2）

依然只能查到两条（注意：此次事务先不结束）

根据之前的规则：因为事务4删除的数据删除版本（4）大于当前事务 （2），所以其他事务删除的数据仍然可以被查出

jack：删除时间大，满足

tom：创建时间大，不满足

7、更新：事务4删除了一条数据，并且提交

隐藏字段：

删除版本号：数据qingshan会记录为当前的事务ID 为5

创建版本号：数据盆鱼宴会记录为当前的事务ID 为5

8、事务2查询：第四次查询（本次事务id为2）

依然只能查到两条（此次事务结束）

根据之前的规则：因为事务5在数据qingshan上添加了删除版本（5），在数据盆鱼宴添加了创建版本（5）

qingshan：删除时间大，满足

jack：删除时间大，满足

tom：创建时间大，不满足

盆鱼宴：创建时间大，不满足

五、锁

1、锁的粒度

表锁与行锁的区别

锁定粒度：表锁 > 行锁

加锁效率：表锁 > 行锁（表锁直接锁定，行锁还要找到行数据）

冲突概率：表锁 > 行锁

并发性能：表锁 < 行锁

2、行锁（锁定行数据）

共享锁（S锁）Shared Locks：

又称为读锁，共享锁就是多个事务对于统一数据可以共享一把锁，都能访问到数据，但是只能读不能修改；

加锁方式：select * from student where id = 1 LOCK IN SHARE MODE;

释放锁：commit、rollback

排他锁（X锁）Exclusive Locks：

又称写锁，排他锁不能与其他锁并存，如果一个事务获取了数据行的排它锁，其他事务就不能再获取该行的锁（共享锁、排它锁），只有该获取来了排它锁的事务是可以对数据行进行读取和修改

加锁释放锁方式：

自动：delete、update、insert 默认加 X 锁

手动：select * from student where id = 1 FOR UPDATE

3、表锁（意向锁不是真正锁定数据，而是给表加一个锁定的标识）

表锁是意向锁，一个事务能够成功给一张表加上表锁的前提：

没有其他的任何事务已经锁定了这张表的任意一行数据

意向共享锁（IS）

意向锁是由数据引擎自己维护的,用户无法手动操作意向锁。

意向共享锁(Intention Shared Lock,简称IS锁)表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁。

意向排它锁（IX）

意向排他锁(Intention Exclusive Lock,简称IX锁)表示事务准备给数据行加入排他锁

说明事务在一个数据,行加排他锁前必须先取得该表的IX锁。

4、锁的原理

锁的作用

为了解决资源竞争的问题

问：锁到底锁住了什么？

答：锁住的是索引

是行数据（Row / Record）吗？

是一个字段（Column）吗？

在INNODB中（只有主键索引和辅助索引两种），锁住的是索引

在发生全表扫描的时候，会将表里所有的聚集索引全部锁住

1、不使用索引（没有手动创建任何索引）

不手动创建任何索引：rowid - 聚集索引

在没有手动创建任何索引的情况下，默认会使用 隐藏字段rowId 作为索引来使用

2、使用主键索引

手动创建主键索引：primary key - 聚集索引

3、使用唯一索引

手动创建唯一索引：unique key 1 not null

1、为什么会锁表?

没有索引,锁住什么？

答：因为在SQL语句里用不到索引，没有办法，只能遍历这张表查找你的数据，所以当发生了全表扫描的时候，会把这张表里所有的聚集索引全部锁住，这个时候才会造成锁表的现象

2、锁住二级索引,为什么主键索引也会被锁住?

是根据INNODB索引结构的实现方式相关，

因为二级索引中存储的只是主键信息，最终都会再去查找主索引，拿到数据

六、mysql查看被锁住的表

#查询是否锁表
 
show OPEN TABLES where In_use > 0;
 
#查看所有进程
 
#MySQL:
 
show processlist;
 
#mariabd:
 
show full processlist;
 
#查询到相对应的进程===然后 kill id
 
#杀掉指定mysql连接的进程号
 
kill $pid
 
#查看正在锁的事务
 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
 
#查看等待锁的事务
 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
 
#查看innodb引擎的运行时信息
 
show engine innodb status\G;
 
#查看造成死锁的sql语句，分析索引情况，然后优化sql语句；
 
 
 
#查看服务器状态
 
show status like '%lock%';
 
#查看超时时间：
 
show variables like '%timeout%';