mysql常用面试题-事务与锁-我打赌80%人没仔细看过原理

什么是事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

注意:

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
• 事务用来管理insert,update,delete语句

事务的 acid 特性

条件:

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（可靠性）【翻译为持久性】

• 1、事务的原子性：一组事务，要么成功；要么撤回。
• 2、一致性 : 有非法数据（外键约束之类），事务撤回。
• 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得，.

commit 和 rollback:

在MySQL中，事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。

COMMIT & ROLLBACK:
这两个关键字提交和回滚主要用于MySQL的事务。

当一个成功的事务完成后，发出COMMIT命令应使所有参与表的更改才会生效。

如果发生故障时，应发出一个ROLLBACK命令返回的事务中引用的每一个表到以前的状态。

操作:

mysql> show create table t1;

// 用来查看 engine 是innodb
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

操作事例:花费10mins;

set autocommit=0;

select * from t1;

delete from t1 where id=2; // 注意:这里没有生效,xshell中仍然存在id为2的数据  可以大家看一下xshell看

savepoint p1;

delete from t1 where id =3;

savepoint p2;

delete from t1 where id = 5;

rollback to p2;

select * from t1;  ------

rollback to p1;

select * from t1;

注意: 事物回滚 p2到p1; 可以, 但如果 p3到 p2;到p1;

可以, 那么 p3到 p1;p1 再到p2.则不行. 这是一个流式.

事务特性可能会单独跟你吹牛逼，这地方请背下来。比如问你，如果没有其中某一个特性会有什么后果？

ps：真是脑溢血问题。跟问我如果你不用if else 如何实现switch一样

• 1、事务的原子性：一组事务，要么成功；要么撤回。
• 2、一致性 : 有非法数据（外键约束之类），事务撤回。
• 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得，.

原理问题：Innodb事务4大特性（ACID）的实现原理

原子性：单个事务的执行要么整体成功，要么整体失败。主要通过undo log实现，undo log中记录了修改前的数据版本，在事务执行失败时，通过undo log回滚。

持久性：事务在提交后，对数据的修改是持久化的。主要通过redo log实现。innno db通过内存缓存来提高性能，在执行查询（select）时，先查内存缓存，没有再查磁盘，并将数据缓存到内存；在执行修改（update,insert,delete）时，先更新缓存，再延期统一刷入磁盘，以减少磁盘io次数，但由于内存缓存在服务器宕机时会丢失，所以就需要redo log，在每次修改前，先写redo log再更新缓存。

隔离性：并发执行的多个事务之间是隔离的。主要通过锁和mvcc实现，”写-写“通过锁进行并发隔离；”读-写“通过mvcc进行并发隔离。

一致性：事务执行前后数据库的整体状态是一致的。通过上三种特性的实现来保证一致性。

补充：

redo log的写入性能很高： ①redo log也不是每次修改时直接写入文件，也是先写入内存日志缓存，再以不同策略统一落盘；②redo log是预生成文件，文件写入是顺序io。
mvcc的实现依赖记录中的 2个隐式字段（DB_TRX_ID–最近修改事务ID、DB_ROLL_PTR–回滚指针，指向undo log中这条记录的上一个版本）、undo log和Read View。

————————————————
原文链接：blog.csdn.net/u014294083/article/d...

深究，InnoDB的事务是如何回滚的？

InnoDB实现回滚的关键是回滚日志。回滚的过程比上面描述的会复杂一些，比如在并发情况下，多个事务之间，回滚时需要区分当前事务可见的回滚日志，然后再根据回滚日志进行回滚，这实际上已经属于事务隔离性的范畴了，这里暂不深入探讨。

innodb 原子性 原理是什么？

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。当开启一个事务对数据进行增删改时，InnoDB会生成一条对应的回滚日志(修改前的数据信息)到回滚日志缓冲中。如果事务执行失败或调用了rollback命令，导致事务需要回滚时，InnoDB就会根据回滚日志中的信息，执行与当前事务相反的工作，将数据回滚到增删改之前的状态。

InnoDB的回滚实现原理，简单理解，就是在需要回滚事务时，InnoDB根据回滚日志的信息，对于每个insert语句，执行一条对应的delete语句；对于每个update语句，会执行一条更新为原值的update语句；对于每条delete语句，会执行一条对应的insert语句。

这样，通过回滚日志，InnoDB可以在任何需要回滚的地方将相关数据回滚到事务前的状态。

深究文章参考：blog.csdn.net/weixin_39623805/arti...

这个文章会一直讨论到 多个事务回滚，如何隔离的。那么面试官，可能继续深挖你的原理。
所以我们再准备一下 下一个问题。

innodb是如何巧妙实现事务隔离级别

www.cnblogs.com/volcano-liu/p/9893...

InnoDB是如何保证隔离性的？

利用锁和 MVCC 机制。这里简单的介绍一下 MVCC 机制，也叫多版本并发控制，在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务下，每条记录在更新的时候都会同时记录一条回滚操作，就会形成一个版本链，在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

事务持久性，如何持久的？直接写入磁盘吗？

回答： MySQL的innoDB存储引擎，使用Redo log保证了事务的持久性。并不是直接写入磁盘。因为直接写入磁盘，谁的磁盘抗的住啊？梁静茹的磁盘吗？

** 解释**：
所谓MySQL事务持久性就是事务一旦提交，就是永久性的，不会因为宕机等故障导致数据丢失(外力影响不保证，比如磁盘损害)。持久性是保证了MySQL数据库的高可靠性(High Reliability)，而不是高可用性(Hign Availability)。

MySQL的innoDB存储引擎，使用Redo log保证了事务的持久性。当事务提交时，必须先将事务的所有日志写入日志文件进行持久化，就是我们常说的WAL(write ahead log)机制。

这样才能保证断电或宕机等情况发生后，已提交的事务不会丢失，这个能力称为 crash-safe。

Redo log包括两部分，重做日志缓冲(redo log buffer)和重做日志文件(redo log file)，前者是易失的缓存，后者是持久化的文件。

原文链接：blog.csdn.net/weixin_32553639/arti...

都聊了，肯定还会细问你 一致性? 那么一致性到底是什么？我们打破砂锅问到底。

答：
定义
一致性简单一点说就是数据执行前后都要处于一种合法的状态，比如身份证号不能重复，性别只能是男或者女，高考的分数只能在0～750之间，红绿灯只有3种颜色，房价不能为负的等等， 只有符合这些约束的数据才是有效的，比如有个小孩儿跟你说他高考考了1000分，你一听就知道他胡扯呢。数据库世界只是现实世界的一个映射，现实世界中存在的约束当然也要在数据库世界中有所体现。如果数据库中的数据全部符合现实世界中的约束（all defined rules），我们说这些数据就是一致的，或者说符合一致性的。

实现
要保证数据库的数据一致性，要在以下两个方面做努力：

利用数据库的一些特性来保证部分一致性需求：比如声明某个列为NOT NULL 来拒绝NULL值得插入等。
绝大部分还是需要我们程序员在编写业务代码得时候来保证。

这里附上大神的数据库acid属性，实现原理

blog.51cto.com/u_14230003/2466408

事务隔离级别及解决的问题

事务的问题

多事务并发有三种异常情况，违反了隔离性：

脏读：读到了其他事务还没有提交的数据。
不可重复读：对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。
幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读

不可重复读和幻读的区别

相同点
不可重复读 和 幻读都是在先后两次读取的时候发现不一致的情况
不同点
不可重复读是同一条记录的内容被修改了，重点在于UPDATE或DELETE
幻读是查询某一个范围的数据行变多了或者少了，重点在于INSERT

可重复读（repeatable read）级别如何避免幻读？

• 在快照读读情况下，mysql通过mvcc来避免幻读。
• 在当前读读情况下，mysql通过next-key来避免幻读。

什么是next-key锁

可以简单的理解为X锁+GAP锁

临键锁(Next-key Locks)：是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

• • 临键锁主要是为了避免幻读。如果把事务的隔离级别降级为RC，临键锁则会失效。

什么是快照读和当前读

*   快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

    *   select \* from table where ?;
*   当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

    *   select \* from table where ? lock in share mode;
    *   select \* from table where ? for update;
    *   insert into table values (…);
    *   update table set ? where ?;
    *   delete from table where ?;

事务隔离级别

事务隔离级别用来解决以上三种情况，四种隔离级别从低到高分别是：

事务隔离级别

查看隔离级别

SHOW VARIABLES LIKE ‘transaction_isolation’;
设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

总结
1、隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能性会更大。在实际使用过程中我们往往需要在性能和正确性上进行权衡和取舍，没有完美的解决方案，只有适合与否；

2、MySQL 默认是事务自动提交，这里我们还需要将 autocommit 参数设置为 0，（两个连续的事务，第二个Begin；执行时，第一个会被隐式提交/commit）

SET autocommit = 0;

本题来源：blog.csdn.net/Zdelta/article/detai...

注意： *InnoDB默认使用的是可重复读隔离级别. *

数据库事务的使用的规范有哪些？

1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

a)尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；

b)合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

c)尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

d)尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

###（2）由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的小建议：

a)类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

b)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

c)对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

原文链接：【mysql锁相关讲解及其应用——《深究mysql锁》】blog.csdn.net/zcl_love_wx/article/...

什么是多版本并发控制（MVCC：multi-version concurrency control ）

MVCC定义：多版并发控制系统。可认为是行级锁的一个变种，它能够避免更多情况下的加锁操作。

作用：避免一些加锁操作，提升并发性能。

实现：通过在每行记录的后面保存行的创建时间和过期时间或删除时间（它们是隐藏的），这两个时间实际都是系统的版本号。每开始一个新的事务，版本号都会自动增加。

打破砂锅！InnoDB的MVCC实现原理是什么？ 具体原理

4.1） select：innoBD查询时会检查以下两个条件：一个是数据行的版本号早于当前事务的版本号；另一个是行的删除版本号，要么没有，要么大于当前事务的版本号。

4.2）insert/delete：innoDB将当前的系统版本号作为新插入(删除)的数据行的版本号。

4.3）update：先新插入一行数据，并将当前系统版本号作为行的版本号，同时将当前系统版本号作为原来行的删除版本号。更新主键时，聚集索引和普通索引都会产生两个版本；而更新非主键时，只要普通索引会产生两个版本。

注意：MVCC只在read committed和repeatable read两个隔离级别下工作。
[参考：《高性能mysql》]
————————————————
版权声明：本文为CSDN博主「zcl_love_wx」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：blog.csdn.net/zcl_love_wx/article/...

mysql 查看锁_你应该了解的MySQL锁分类

可以从四个维度给锁分类，分别如下：

[MySQL]中的锁

锁是为了解决[并发]环境下资源竞争的手段，其中乐观并发控制，悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段(具体可见我之前的文章)，而MySQL中的锁就是其中的悲观并发控制。

MySQL中的锁有很多种类，我们可以按照下面方式来进行分类。

第一 按读写：独占锁 共享锁

从数据库的读写的角度来分，数据库的锁可以分为分为以下几种：

• 独占锁：又称排它锁、X锁、写锁。X锁不能和其他锁兼容，只要有事务对数据上加了任何锁，其他事务就不能对这些数据再放置X了，同时某个事务放置了X锁之后，其他事务就不能再加其他任何锁了，只有获取排他锁的事务是可以对数据进行读取和修改。
• 共享锁：又称读锁、S锁。S锁与S锁兼容，可以同时放置。
• 更新锁：又称U锁。它允许再加S锁，但不允许其他事务再施加U锁或X锁，当被读取的数据要被更新时，则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务，并同时确保事务A自从上次读取数据后数据没有被更改，因此可以减少X锁和S锁的冲突，同时避免使用S锁后再升级为X锁造成的死锁现象。注意，MySQL并不支持U锁，SQLServer才支持U锁。

兼容性矩阵如下(+ 代表兼容， -代表不兼容)

第二 按粒度 ： 行级锁、页级锁、表级锁

MySQL支持不同级别的锁，其锁定的数据的范围也不同，也即我们常说的锁的粒度。MySQL有三种锁级别：行级锁、页级锁、表级锁。不同的存储引擎支持不同的锁粒度，例如MyISAM和MEMORY存储引擎采用的是表级锁，页级锁仅被BDB存储引擎支持，InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。

特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。数据库引擎总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁从而避免死锁。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行锁总是逐步获得的，因此会出现死锁。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

下面详细介绍行锁和表锁，页锁由于使用得较少就不介绍了。

行锁

按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引上，通过非聚簇索引查询的时候，先锁非聚簇索引，然后再锁聚簇索引。如果一个where语句里面既有聚簇索引，又有二级索引，则会先锁聚簇索引，再锁二级索引。由于是分步加锁的，因此可能会有死锁发生。

MySQL的行锁对S、X锁上做了一些更精确的细分，使得行锁的粒度更细小，可以减少冲突，这就是被称为“precise mode”的兼容矩阵。(该矩阵没有出现在官方文档上，是有人通过Mysql lock0lock.c:lock_rec_has_to_wait源代码推测出来的。)

第三：细分行锁的分类：兼容矩阵

• 间隙锁(Gap Lock)：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。
• 记录锁(Record Lock)：只锁记录，特定几行记录。
• 临键锁(Next-Key Lock)：同时锁住记录和间隙，前开后闭区间(a,b]。
• 插入意图锁(Insert Intention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。

S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。精确模式的检测，用在S、X和X、X之间。从这个矩阵可以看到几个特点：

• INSERT操作之间不会有冲突：你插入你的，我插入我的。

• GAP，Next-Key会阻止Insert：插入的数据正好在区间内，不允许插入。

• GAP和Record，Next-Key不会冲突

• Record和Record、Next-Key之间相互冲突。

• 已有的Insert锁不阻止任何准备加的锁。

• 间隙锁(无论是S还是X)只会阻塞insert操作。

  注意点

• 对于记录锁，列必须是唯一索引列或者主键列，查询语句必须为精确匹配，如“=”，否则记录锁会退化为临键锁。

• 间隙锁和临键锁基于非唯一索引，在唯一索引列上不存在间隙锁和临键锁。

表锁与锁表的误区

只有正确通过索引条件检索数据(没有索引失效的情况)，InnoDB才会使用行级锁，否则InnoDB对表中的所有记录加锁，也就是将锁住整个表。注意，这里说的是锁住整个表，但是Innodb并不是使用表锁来锁住表的，而是使用了下面介绍的Next-Key Lock来锁住整个表。

表级锁

直接对整个表加锁，影响表中所有记录，表读锁和表写锁的兼容性见上面的分析。

MySQL中除了表读锁和表写锁之外，还存在一种特殊的表锁：意向锁，这是为了解决不同粒度的锁的兼容性判断而存在的。

让行锁 和 表锁 共存的： 意向锁

因为锁的粒度不同，表锁的范围覆盖了行锁的范围，所以表锁和行锁会产生冲突，例如事务A对表中某一行数据加了行锁，然后事务B想加表锁，正常来说是应该要冲突的。如果只有行锁的话，要判断是否冲突就得遍历每一行数据了，这样的效率实在不高，因此我们就有了意向表锁。

意向锁的主要目的是为了使得 行锁 和 表锁 共存，事务在申请行锁前，必须先申请表的意向锁，成功后再申请行锁。注意：申请意向锁的动作是数据库完成的，不需要开发者来申请。

意向锁是表级锁，但是却表示事务正在读或写某一行记录，而不是整个表， 所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。

意向锁分为意向读锁(IS)和意向写锁(IX)。

第四：锁机制。分为乐观锁和悲观锁

乐观锁和悲观锁是什么，如何实现？

1、乐观锁：先修改，保存时判断是够被更新过，应用级别，说白了就是自己写代码实现，是一种思想，可以基于版本号、更新时间戳可以实现，

2、悲观锁：先获取锁，再操作修改，数据库级别，比如sql后缀写 for update，是基于MySQL自带的功能。

锁的兼容性，知道一下。 上面写了很多很长

此题来自：segmentfault.com/a/119000002386957...

本作品采用《CC 协议》，转载必须注明作者和本文链接