Mysql（4事务及存储）

目录

一.事务

事务的 ACID 特性：

一致性问题：

隔离级别：

查看隔离级别：

?编辑

设置隔离级别：

事务管理操作：

看下未提交读隔离的现象：

?编辑

看下提交读隔离的现象：

看下可重复度隔离的现象：

设置回滚点：

?自动提交事务：

二.存储引擎

MyISAM 与 InnoDB 的区别？

查看表的存储引擎：

查看系统支持的存储引擎

针对已存在的表修改存储引擎

建表时指定存储引擎

设置新建表的默认存储引擎：

死锁：

如何避免死锁?

---

一.事务

事务就是一组数据库操作序列（包含一个或者多个SQL操作命令），事务会把所有操作看作是一个不可分割的整体向数据库系统提交或撤消操作，所有操作要么都执行，要么都不执行。

事务的 ACID 特性：

原子性、一致性、隔离性、持久性

原子性：事务管理的基础。把事务中的所有操作看作是一个不可分割的工作单元，要么都执行，要么都不执行。

一致性：事务管理的目的。保证事务开始前和事务结束后数据的完整和一致

隔离性：事务管理的手段。使多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空间，事务的执行不会受到其它事务的干扰。可通过设置隔离级别解决不同的一致性问题。

持久性：事务管理的结果。当事务被提交以后，事务中的命令操作修改的结果会被持久保存，且不会被回滚。

一致性问题：

脏读，不可重复读，幻读，丢失更新。

脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。

幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。

丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录，B提交数据后B的修改结果覆盖了A的修改结果

隔离级别：

未提交读 ?read uncommitted ? 允许 脏读 不可重复读 ?幻读
提交读 ? ?read committed ? ? 不允许 脏读，允许 不可重复读 ?幻读
可重复度 ?repeatable read ? ?不允许 脏读 不可重复读，有条件的允许 幻读（InnoDB存储引擎可以不允许）
串行读 ? ?Serializable ? ? ? 都不允许，相当于表级锁定，但是会影响数据库的读写效率和性能

查看隔离级别：

查看全局的隔离级别

show global variables like '%isolation%';

查看当前会话的隔离级别
show session variables like '%isolation%';

设置隔离级别：

全局级别的设置，可在所有会话有效，需要重新登录才可生效

set global transaction isolation level 隔离级别名称;?

会话级别的设置，在当前会话会话中立即生效

set session transaction isolation level 隔离级别名称; ?

事务管理操作：

begin; ? 开启一个事务

savepoint 自定义名字;? ?在事务中创建回滚点

rollback to 自定义名字; ? ?在事务中回滚操作到指定的回滚点位置

commit; 或 rollback; ? ?提交或回滚结束事务

看下未提交读隔离的现象：

给表添加内容：

看会话1：

看会话2：

会话2能看到会话1修改未保存的数据。

给会话1增加结束操作：

看下提交读隔离的现象：

将隔离级别设为提交读

看会话1：

看会话2：

会话2看不到会话1修改未保存的数据。

当会话1保存结束修改后，查看会话2：

会话2在没有结束可以直接查看结果：

看下可重复度隔离的现象：

看下会话1：

看下会话2：

看不到会话1的修改为保存的数据。

结束会话1并在会话2查看表：

设置回滚点：

我想回滚到s1的数据：

回滚到s2，就不行了：

如果回滚到第一个回滚点，后面设置的回滚点就回不到了。

?自动提交事务：

查看提交事务是否开启：

show ?[global/session] ?variables like 'autocommit';

设置提交事务：set [global/session] ?autocommit = 0/1; ? ? ? ?0关闭自动提交，1开启自动提交

修改aaa表中的内容：

退出数据库重新进入：

二.存储引擎

存储引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作。工作在文件系统之上，数据库的数据会先传到存储引擎，再按照存储引擎的存储格式保存到文件系统。

常用的存储引擎：InnoDB ? MyISAM

MyISAM 与 InnoDB 的区别？

MyISAM：不支持事务、外键约束，只支持表级锁定，适合单独的查询和插入的操作，读写会相互阻塞，支持全文索引，硬件资源占用较小，数据文件和索引文件是分开存储的（存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI）
使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景

InnoDB：支持事务、外键约束，支持行级锁定（在全表扫描时仍然会表级锁定），读写并发能力较好，支持全文索引（5.5版本之后），缓存能力较好可以减少磁盘IO的压力，数据文件也是索引文件（存储成两个文件：表结构文件.frm、数据文件.ibd）
使用场景：适用于需要事务的支持，一致性要求较高，数据会频繁更新，读写并发高的业务场景

查看表的存储引擎：

show create table 表名;

show table status [from 库名] where name='表名'\G

查看系统支持的存储引擎

show engines;

针对已存在的表修改存储引擎

alter table 表名 engine=InnoDB/MyISAM;?

查看下aaa表的存储引擎：

建表时指定存储引擎

create table 表名 (....) engine=InnoDB/MyISAM;?

设置新建表的默认存储引擎：

修改配置文件:

死锁：

死锁是指两个或多个事务在同一个资源上相互占用，并请求对方的锁定资源，从而导致恶心循环的现象。

锁定记录：delete from 表名?where （条件）；
进行测试一下：

会话2，连接超时：

如何避免死锁?

设置事务超时等待时间 innodb_lock_wait_timeout
设置开启死锁检测 innodb_deadlock_detect
为表添加合理的索引，减少表锁发生的概率
如果业务允许，可以降低隔离级别，比如采用 提交读 隔离级别
建议开发人员尽量使用更合理的业务逻辑，多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
建议开发人员在读多写少的场景下适用乐观锁机制
?