MySQL的事务和存储引擎

事务

概述

• 事务是一种机制和操作序列，它包含了一组数据库操作命令，即把这些命令一起提交给系统或者撤销刚刚的操作请求，也就是这些命令要么被提交执行了，要么就被撤销掉不执行了。
• 是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务属于最小的控制单元。
• 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司和证卷交易系统等。
• 通过事务的整体性以保证数据的一致性。

事务的特点（ACID）

ACID是在数据库管理系统中，事务应该具有的四个特性，A代表原子性（Atomicity）；C代表一致性（Consistency）；I代表隔离性（Isolation）；D代表持久性（Durability）。

原子性

原子性促使事务成为一个不可分割的工作单位，一个事务中的所有操作要么都发生，要么都不发生

• 事务是一个完整的操作，不可能出现一部分发生，一部分不发生。
• 事务中所有的元素必须作为一个整体提交给系统或者回滚（rollback）
• 事务中只要有一个元素不成功，那么整个事务就失败

一致性

一致性指在事务开始前和结束后，数据库的完整性约束没有被破坏

• 事务开始前和完成后，数据库中的所有数据处于一致状态。
• 正在运行的事务中，数据的状态可以不一致

隔离性

隔离性指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

• 每个事务是独立的，对各自的数据修改都是隔离的，不会相互依赖和直接影响。
• 修改数据的事务则要另一个要使用相同数据的事务开始前访问数据，或者在其结束之后访问。

持久性

持久性指在事务完成后，该事务对数据库进行的操作会持久的保存在数据库中，并不会被回滚。

• 一旦事务完成后，不管系统有没有发生故障，事务的结果都是永久的
• 事务被提交后，事务的效果会被永久的保存在数据库中

事务之间的影响（间接的）

1. 脏读：一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。
2. 幻读：一个事务要对表中数据进行修改，这种修改涉及到了表中所有的数据行，同时另一个事务要向表中插入一行新的数据，那么前一个事务操作时不会看见新插入的数据行，就像产生幻觉一样。
3. 不可重复读：由于查询一个事务时系统中其他事务修改的提交，会引起该事务两次相同的查询返回的数据不同。
4. 丢失更新：两个事务同时读取同一条记录，A事务先修改这条记录，B后修改记录，B提交数据后会覆盖掉A的修改结果。

事务隔离级别

mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是 read committed 。

• read uncommitted（RU）：读取尚未提交的数据——不解决脏读
• read committed（RC）：读取已经提交的数据——可以解决脏读
• repeatable read（RR）：重复读取——可以解决脏读和不可重复读（MySQL默认的）
• serializable：串行化——可以解决脏读、不可重复读和虚读（相当于锁表）

查询全局事务隔离级别

查询会话事务隔离级别

设置全局事务隔离级别

设置会话事务隔离级别

事务控制语句

BEGIN 或 START TRANSACTION：显式地开启一个事务。
COMMIT 或 COMMIT WORK：提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点。

先创建个新的库和表，以便后面使用

测试提交事务

测试回滚事务

测试多点回滚

设置控制事务（set）

SET AUTOCOMMIT=0;						#禁止自动提交
SET AUTOCOMMIT=1;						#开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#查看Mysql中的AUTOCOMMIT值

如果开启了自动提交，MySQL会把每一条SQL语句当作一个事务，每执行一条就会自动的提交。
如果关闭了自动提交，那么直到你输入rollback/commit之前的所有操作都会被当成一个事务。当前事务结束前新的MySQL连接时无法读取到任何当前会话的操作结果。

存储引擎

概述

• 顾名思义，“存储”的意思的存储数据，“引擎”一词来源于发动机，它是发动机中的核心部分，那么“存储引擎”也是数据库的核心部分。
• 存储引擎是MySQL有别于其他数据库管理系统的最大特色，关系型数据库的数据是存在表里的，表是在存储数据的同时，还要组织数据的存储结构，而这些数据的组织结构就是由存储引擎决定的。
• MySQL常用的存储引擎一般是MyISAM和InnoDB

MyISAM

• MyISAM存储引擎是MySQL关系数据库系统5.5版本前默认的存储引擎，前身是ISAM。
• ISAM是一个定义明确且经历时间考验的数据表格管理方法。
• MyISAM使用一种表格锁定的机制，以优化多个并发的读写操作
• MyISAM提供高速存储和检索，以及全文检索能力

特点

• 不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的
• 不占用大量的内存和存储资源
• 访问速度快
• 对事务完整性没有要求
• 在磁盘上存储为三个文件
  .frm文件存储表定义
  数据文件的扩展名为.MYD(MYData)
  索引文件的扩展名为.MYI(MYIndex)

MyISAM支持的存储格式

1. 静态表
   静态表是默认的存储格式。静态表中的字段都是固定字段，每条记录都是固定长度的。这种存储方式的优点是存储迅速，易缓存，出现故障易恢复；缺点是占用的空间通常比动态表多。
2. 动态表
   动态表包含可变字段，记录不是固定长度的数据。这样存储的优点是占用空间较少；缺点是频繁的更新、删除记录容易产生碎片，需要定期的执行optimize table或者myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。
3. 压缩表
   压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

InnoDB

• 支持事务：支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• 读写阻塞与事务隔离级别相关
• 具有非常高效的缓存特性：能缓存索引和数据
• 表与主键以簇的方式存储
• 支持分区、表空间，类似于oracle数据库
• 支持外键约束，5.5版本以前不支持全文索引，5.5以后支持全文索引
• 对硬件资源要求还是比较高的场合
• 在磁盘上存储为三个文件
  db.opt（表属性文件）
  表名.frm（表结构文件）
  表名.ibd（表数据元数据）

查看存储引擎

查看系统支持的存储引擎

查看表使用的存储引擎

• 方法一
  
• 方法二
  

修改存储引擎

方法一（alter table）

方法二（/etc/my.cnf）

方法三（create table）

MyISAM和InnoDB的区别

1. InnoDB支持事务，MyISAM不支持
2. InnoDB支持外键，而MyISAM不支持
3. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描；MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
4. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高
5. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
6. InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
7. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
   Innodb：frm是表定义文件，ibd是数据文件
   Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
8. 清空整个表时，InnoDB是一行一行的删除效率很慢；MyISAM则会重建表

总结

事务

1. 事务是一个操作序列，只有两种状态，执行和撤销执行，它是一个不可分割的工作单位。
2. 事务具有原子性、一致性、隔离性和持久性。原子性使事务为一个不可分割的工作单元；一致性导致数据在事务开始前和完成后是处于一致状态；隔离性使每个事务之间不相互依赖和影响；持久性使事务的结果永久被保存到数据库中。
3. 事务之间的影响分为脏读、幻读、不可重复读和丢失更新。
4. 事务的隔离级别有RU、RC、RR和serializable，RC可以解决脏读，RR可以解决脏读和不可重复读，serializable可以解决脏读、不可重复读和虚读。
5. 关闭自动提交后，需要手动进行提交或者回滚，如果没有手动提交或回滚，那么修改的数据就不会被保留下来

存储引擎

存储引擎的作用就是规定了数据存储时的存储结构

InnoDB与MyISAM的区别

详细区别点击此处

死锁的现象与解决办法

一、现象
是两个或者两个以上线程在执行过程中，因争抢资源而产生相互等待的一种现象，在申请锁的时候发生了交叉闭环申请

二、死锁产生的条件

1. 互斥。共享资源同时只能被一个线程访问
2. 占有且等待。线程T1在取得共享资源A的时候，请求等待资源B的时候并不释放资源A
3. 不可抢占。其他线程不能强行抢占线程的资源
4. 循环等待条件。线程T1在持有资源A1，同时在请求等待获取资源B，线程T2在持有资源B,然后在请求等待线程T1的持有资源，形成了交叉闭环申请

三、解决办法

1. 资源倾斜，使其冲破死锁

2. 找到进程号，直接杀掉