MySQL数据库学习笔记之事务和引擎（Linux系统）-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_64448698/article/details/138577432

1.事务的引例

事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

例如转账问题：

A的账户余额有1000元，B的账户余额有1000元，执行A给B转账500元，SQL语句如下：

update 表 set A的余额=500 where name='A';

update 表 set B的余额=1500 where name='B';

假如执行完第一句之后出现了意外（断电，系统挂掉了等问题），那么就会导致第一句成功，第二句失败，那么整个执行过程是有问题的。A的余额变为了500，B的余额还是1000，转账失败了，但是A的余额不对了，这样数据就不可靠了。所以事务就是为了解决这种现象的。

我们应该把上面的SQL语句中的两句作为一个执行单元，这个执行单元要么全部执行，要么全部不执行。

2.相关概念

事务：事务是由单独单元的一个或者多个SQL语句组成，在这个单元中，每个SQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚，所有受到影响的数据将返回事务开始以前的状态；如果单元中的所有SQL语句均执行成功，则事务被顺利执行。

回滚：就是把刚才所有的操作进行撤销，回到最初的状态。

隔离性：事务的隔离性是指在一个事务执行过程中，其内部的操作及使用的数据对并发的其他事务是隔离的，即不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间，并发执行的各个事务之间不能互相干扰。换句话说，一个事务的执行不能被其他事务干扰，保证了数据的独立性和一致性。

总之，事务的隔离性是确保数据库在并发环境中保持数据一致性贺完整性的重要手段之一。

通俗来讲，就是一点一点执行SQL语句，其他语句对这些已经执行过的语句能否看见，又能看见多少。

3.存储引擎

（1）概念：数据库存储引擎是数据库底层软件组织的核心组件，它负责管理和存储数据，以及提供数据访问和操作的接口。不同的数据库管理系统（DBMS）通常支持多种不同的存储引擎。在mysql中的数据用各种不同的技术存储在文件（或内存）中。

（2）通过show engines；来查看mysql支持的存储引擎。

（3）在mysql中用的最多的存储引擎有：innodb，myisam，memory等，其中innodb支持事务，而myisam.memory等不支持事务。

（4）有的书上也将存储引擎成为表类型

其中innodb是默认的存储引擎，myisam是5.5版本之前默认的存储引擎，memory也是比较常用的存储引擎。

4.事务的ACID属性（面试题）

（1）原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。

（3）隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

正常来讲，一个事务的执行是不受其他事务的干扰的，但是在实际操作中，多个事务可能并发地操作相同的数据库中的数据。

正常来讲，多个事务应该隔离开来，互不干扰，但是这个互不干扰也是通过隔离级别来控制的。

（4）持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

例如：删除就是一个事务，一旦提交就是永久性的，就没有办法改变。

总结ACID：

原子性：一个事务不可再分割，要么都执行，要么都不执行；

一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态；

隔离性：一个事务的执行不受其他事务的干扰（受不受干扰，受干扰的程度由隔离级别决定）

持久性：一个事务一旦提交，则会永久的改变数据库的数据（例如删除提交后不可恢复，除非重新插入数据）

5.银行转账的事务演示

（1）准备工作

首先先建立一张表作为准备工作：

（2）开启事务正常提交

//1.开启事务
set autocommit=0;  //把自动提交关闭，我们要手动提交   
start transaction; //或简写为begin，//可省略，默认开始了，因为上一句自动提交关闭了

//2.执行转账sql语句
update bank_account set balance_money=500 where username='A';
update bank_account set balance_money=1500 where username='B';

//3.提交事务
commit;

如何回滚呢？

//1.开始
begin;

//2.执行转账sql语句
update bank_account set balance_money=500 where username='A';
update bank_account set balance_money=1500 where username='B';

//3.回滚
rollback;

结束事务之前，数据保存在内存中，等提交的时候才决定执行到磁盘文件还是撤销刚才的操作，也就是说，只有有了结束的标记才决定是否提交到磁盘文件。

6.事务的并发问题

（1）事务的并发问题是如何发生的?

多个事务同时操作同一个数据库的相同数据时；

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题；

（2）并发的问题都有哪些？

脏读，不可重复读，幻读

脏读：事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读，即：一个事务读取了其他事务还没有提交的数据，读到的是其它事务“更新”的数据，若其他事务回滚，那么读取的内容就是临时且无效的。

不可重复读：一个事务的开始时，只能看见已经提交的事务所做的更改。换句话说，一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的。这个级别有的时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果，即：对两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。

幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。

脏读和幻读的区别：

脏读和幻读很像，脏读一般针对的是更新（没有提交），而幻读一般针对的是插入和删除

也可以这么说，脏读是另一个事务修改了数据，幻读是另一个事务新增了数据

（3）如何解决并发问题：

通过设置隔离级别来解决并发问题

7.数据库事务的隔离性

数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。

一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。

数据库提供的4种事务隔离级别（从低到高）：

隔离级别：

READ UNCOMMITTED（读未提交）：允许事务读取未被其他事务提交的数据，那么脏读，不可重复读，以及幻读都会出现；

READ COMMITTED（读已提交）：只允许事务读取已经被其他事务提交的数据，那么可以避免脏读，但是不能避免不可重复读和幻读；

REPEATABLE READ（可重复读）：确保事务可以多次从一个字段种读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新（当然，其他事务更新的数据本事务提交之后就能看见），那么可以避免脏读和不可重复读，但是不能避免幻读；

SERIALIZABLE（串行化）：确保事务可以从一个表种读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题都可以避免，但是性能十分低下。

8.隔离级别总结

事务的隔离级别

	脏读	不可重复读	幻读
read uncommitted(读未提交)	可出现	可出现	可出现
read committed(读已提交)	可避免	可出现	可出现
repeatable read(可重复读)	可避免	可避免	可出现
serializable(串行化)	可避免	可避免	可避免

注意：

从上往下级别逐次增高，但是性能逐渐降低；

项目中不太使用读未提交（Read-Uncommitted）和串形化（Serializable）两个隔离级别.（一个隔离级别太低，一个隔离级别太高，太低的问题太多，太高的效率低下）

Oracle默认的事务隔离级别为：READ COMMITTED(读已提交)。

Mysql默认的事务隔离级别为REPEATABLE READ(可重复读)。

9.事务的隔离性的演示

（1）准备工作

不知道前面设置过什么隔离级别，可以把服务重启一下

重启：service mysql restart
停止：service mysql stop
启动：service mysql start

（2）查看默认的隔离级别以及设置隔离级别的命令

查看默认的隔离级别：
select @@tx_isolation;(以前的版本用这个命令)
select @@transaction_isolation;(8.0的版本用这个命令)

设置隔离级别：
set session|global transaction isolation level 隔离级别

（3）开启事务：begin

（4）提交事务：commit

（1）演示脏读，不可重复读，幻读（READ_UNCOMMITTED读未提交）

read_uncommitted(读未提交)隔离级别下出现脏读，不可重复读，幻读；

读未提交(Read_uncommitted)：允许脏读，也就是可能读到其他会话中未提交事务修改的数据，一个事务读到另一个事务未提交的数据；

只有设计成最低隔离级别READ UNCOMMITTED(读未提交)，脏读，不可重复读，幻读才会出现

1.第一个窗口把隔离级别设置成最低的隔离级别（读未提交）：

 set session transaction isolation level read uncommitted;

账户余额刚开始为500，1500；

2.第一个窗口将用户A的账户余额更改为100000，但是不提交

update bank_account set balance_money=100000 where username='A';

3.然后第二个窗口查看A的余额，发现是100000（注意第二个窗口的隔离级别还是原来的隔离级别，需要重新设置）

4.第一个窗口回滚

5.第二个窗口再次查看余额发现还是500；

刚才第二个窗口读到的10万就是脏数据；

对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。

这种现象也称为不可重复读和幻读，所以我们会发现在最低隔离级别read_uncommitted下就会出现脏读，不可重复读，幻读。

（2）演示不可重复读和幻读（READ_COMMITTED读已提交）

read_committed(读已提交)隔离级别下可以避免脏读，不可重复读和幻读不可避免；

1.第一个窗口把隔离级别设置为read_committed并查看隔离级别，命令如下：

set session transaction isolation level read committed;
select @@transaction_isolation;

2.第一个窗口开启事务，并将A账户余额改为20万，但是不提交；

begin;
update bank_account set balance_money=200000 where username='A';

3.在第二个窗口把隔离级别也设置为read_committed；

set session transaction isolation level read committed;
（更改）

4.然后查看数据，发现数据还是500，避免了脏读；但是不可重复读和幻读没有避免；

5.第一个窗口提交，再次查看数据，发现数据已经变成了20万；

6.但是第二个窗口再次读数据，发现数据是20万，也就是第二个窗口两次查看的数据竟然不一样，这个就是不可重复读，也就是read_committed(读已提交)隔离级别下不可重复读不能避免。

执行发现4和6重复读不一致；

所以，在read_committed(读已提交)隔离级别下可以避免脏读，但是不可以避免不可重复读和幻读。

（3）演示幻读（REPEATABLE_READ可重复读出现）

repeatable read(可重复读)隔离级别下可以避免脏读，不可重复读，幻读不能避免

1）演示可避免脏读

1、在第一个窗口下把隔离级别改为repeatable read;

set session transaction isolation level repeatable read;//设置
select @@transaction_isolation;//查看

2、第二个窗口同样也设置为repeatable read;

set session transaction isolation level repeatable read;//设置
select @@transaction_isolation;//查看

3、第一个窗口先看一下余额：

select *from bank_account;

A的余额是20万

4、在第一个窗口下开启事务，并修改余额

begin；
update bank_account set balance_money=1000 where username='A';

注意，并没有提交；

5、在第二个窗口下开启事务，并查看余额，这时发现并没有出现脏读，余额还是20万

2）演示可避免不可重复读

那么可重复读能避免吗？

继续刚才的演示；

1、第一个窗口先提交：commit；

2、第二个窗口再次查看数据，发现还是原来的数据，也就是两次查看的数据是一样的，都是20万；

执行发现

第二个窗口两次查看的数据是一样的，说明避免了不可重复读，即可以重复读

所以，对于同一事务，多次查询结果是一样的，即可重复读，那么就是说可重复读隔离级别也避免了不可重复读。

那么第一个窗口提交了一个寂寞？其实不是的；

那么第一个窗口的数据没有提交成功吗？提交成功了。

那么第二个窗口什么时候能看到更改后的数据呢？

当这个事务提交了，再新开一个事务（先commit，再查询），这个时候才是最新的数据。

综上，repeatable read(可重复读)隔离级别下可以避免脏读和不可重复读

那么幻读可以避免吗？

3）演示幻读不可避免

1、先把第二个窗口的提交了；

2、第一个窗口开启事务，查询数据，只有两行数据；

3、第二个窗口开启事务，先查询数据，然后插入一条数据；（此时表格有三条数据）；做完提交；

begin;

select *from bank_account;

insert into bank_account values(1003,'幻读演示',111111);

commit;

4、第一个窗口插入一行数据，如下的命令：

update bank_account set username='xxxxxxxx';(我们把所有的数据都改成xxxxxxxx，模拟两边同时进行)

这时出现了幻读，明明只有2行数据，怎么改的时候提示改了3条数据，这就是幻读（幻行）。

第一个窗口再查看就是3行，提交之后也是3行；如下：

4）serializable(串行化)隔离级别可以避免所有

即脏读，不可重复读，幻读都可以避免

1、第一个窗口设置串行，第二个窗口设置串行化

set session transaction isolation level serializable;//设置隔离级别
select @@transaction_isolation;//查看隔离级别

2、第一个窗口开启事务，查看数据；第二个窗口开启事务，查看数据

特别注意：是第一个窗口先开启事务begin；

begin;
select *from bank_account;

3、第二个窗口插入数据，发现第二个窗口阻塞了；

insert into bank_account values(1004,'串行化演示',100);

那是因为第一个窗口先开启的事务，第二个窗口要插入数据，必须等第一个窗口的数据提交了，这个就是串行化。但是由于第一个窗口长时间没有提交数据，所以第二个窗口提示锁超时了（大概1分钟）；

如果这个时候又要在第一个窗口插入数据，又会发生死锁（或者有时候又会出现锁超时）；

第一个窗口的数据提交之后，我们插入第二个窗口插入数据，就会提示成功。

串行化（Serializable）可以确保事务可以从一个表中读取相同的行，它通过强制事务串行执行，在这个事务持续期间，禁止其他事务对该表进行插入，更新和删除操作。简单来说，它会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题，所以，它所有的并发问题都可以避免，但性能十分低下。

实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

串形化（Serializable）使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

注意：最后一个隔离级别serialiable的隔离级别最高，什么都能避免，但是性能最低，为什么呢？因为它是一个事务在进行操作，其他事务只能进行等待，所以脏读，不可重复读，幻读都可以避免，但是性能最低。

串形化是最高的隔离级别，避免了前面说的所有问题。

10.存储引擎

数据库存储引擎是数据库底层软件组织的核心组件，它负责管理和存储数据，以及提供数据访问和操作的接口。不同的数据库管理系统（DBMS）通常支持多种不同的存储引擎，每种存储引擎都有其独特的特点和适应场景。

MySQL数据库支持多种存储引擎，每种存储引擎都有其独特的特点和适用场景。以下是MySQL中常见的9种存储引擎及其特点：

1.InnoDB

特点：InnoDB是MySQL的默认存储引擎，它支持事务处理（ACID特性），提供行级锁定和外键约束。InnoDB使用聚集索引，支持崩溃恢复机制和并发性能优化。

适用场景：适用于需要高可靠性和事务支持的应用，如电子商务网站、金融系统等。

2.MyISAM

特点：MyISAM不支持事务和行级锁，适合只读和小文件操作。它具有较高的插入和查询速度，支持全文索引。但由于其锁定机制，不适合高并发的写操作。

适用场景：适用于读多写少的应用，如新闻网站、博客等。

3.Memory（或Heap）

特点：Memory存储引擎将所有数据保存在内存中，因此读写速度非常快。但它不支持持久化存储，数据库重启后数据会丢失。

适用场景：适用于临时数据的存储、高速缓存和临时计算结果的存储，以及对读写性能要求非常高的应用。

4.Archive

特点：Archive存储引擎具有高压缩比和快速插入速度，但查询性能较低。它主要用于存储大量历史或归档数据，以节省磁盘空间。

适用场景：适用于日志记录、历史数据存档等场景。

5.Federated

特点：Federated存储引擎允许访问远程MySQL数据库表中的数据，而无需讲数据复制到本地。它实际上是一个代理，将查询转发到远程数据库并执行。

适用场景：适用于分布式数据库环境或需要访问远程数据的情况。

6.CSV

特点：CSV存储引擎允许你以CSV格式存储数据，可以直接使用文本编辑器查看和编辑。但CSV引擎不支持索引，因此查询性能可能较低。

使用场景：适用于需要与其他系统或应用进行数据交换的场景。

7.Blackhole（黑洞引擎）

特点：Blackhole引擎没有任何存储机制，它会丢弃所有的插入数据，不做任何保存。但它会记录日志信息，可以用于复制数据库到备库。

适用场景：不推荐使用，因为其丢弃数据的特性可能会导致数据丢失。

8.NDB Cluster

特点：NDB Cluster是一个分布式数据库引擎，支持高可用性和高性能的在线事务处理（OLTP）。它允许数据在多个节点之间自动复制和分区，提高了数据的可靠性和并发处理能力。

适用场景：适用于需要高可用性和高性能的分布式数据库应用。

9.Merge

特点：Merge引擎是MyISAM的一个变种，它允许你将多个MyISAM表作为一个表来查询。这对于需要将多个表的数据合并到一个查询中的场景很有用。

适用场景：适用于需要将多个MyISAM表作为一个逻辑表进行查询的场景。

需要注意的是，虽然MySQL支持多种存储引擎，但并非所有存储引擎都适用于所有应用场景。在选择存储引擎时，需要根据应用的需求、数据的访问模式、并发量以及可靠性要求等因素进行综合考虑。

MyISAM是MySQL中的一种存储引擎，它不支持行级锁，但支持表级锁。

1.表级锁：这是最简单的锁策略，对整个表加锁，用户并发访问时，当一个线程获得写锁后，会阻塞其他用户的读写，直到写锁被释放。MyISAM在执行查询（SELECT）操作前，会自动给涉及的所有表加读锁，在执行数据修改（UPDATE、DELETE、INSERT、REPLACE）操作时，会自动给涉及的表加写锁。

2.行级锁：行级锁对表中单独的行进行加锁，这是一种更细粒度的锁策略，能够允许多个事务并发执行，从而提高系统的并发性的性能。但是MyISAM不支持行级锁，只有InnoDB等其他的存储引擎才支持行级锁。

所以，如果你的应用需要高并发写入、事务支持、行级锁等特性，推荐使用InnoDB存储引擎。而如果你的应用主要是读取操作，且对数据一致性要求不高，MyISAM可能是一个更好的选择，因为它的查询性能在某些情况下可能会优于InnoDB。

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

mysql锁粒度就是我们通常所说的锁级别。数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。mysql数据库有三种锁的级别，分别是：页级锁、表级锁和行级锁。

MyISAM和InnoDB是MySQL中的两种存储引擎，它们之间存在显著的区别（经典面试题）

1、事务支持

InnoDB支持事务（Transaction），这保证了数据的完整性和一致性，使得在多个操作中，要么所有的操作都成功，要么所有的操作都失败（原子性）。

MyISAM不支持事务，它主要是用于简单的读取操作。

2、锁定级别

InnoDB支持行级锁（Row-level locking）以及表级锁，但默认使用行级锁，这提供了更高的并发性能，多个事务可以同时访问不同的数据行。

MyISAM仅支持表级锁（Table-level locking），这意味着在对数据进行修改时，整个表都会被锁定，可能导致较高的锁竞争，降低了并发性能。

3、外键支持