mysql中的事务与引擎

---

前言

MySQL事务

事务是什么

 1. 事务是一种机制、一个操作序列,包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
 2. 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
 3. 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
 4. 事务是通过事务的整体性以保证数据的一致性。

所谓事务，就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务的ACID特点

ACID，是指在可靠数据库管理系统（DBNS）中，事务(transaction)应该具有的四个特性:
原子性(Atomicity)、一致性（Consistency)、隔离性（Isolation)、持久性(Durability)。
这是可靠数据库所应具备的几个特性。

1. 原子性(Atomicity):

指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个完整的操作，事务的各元素是不可分的。
事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

2. 一致性（Consistency):

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

当事务完成时，数据必须处于一致状态。

在事务开始前，数据库中存储的数据处于一致状态。

在正在进行的事务中，数据可能处于不一致的状态。

当事务成功完成时，数据必须再次回到已知的一致状态。

3. 隔离性（Isolation):

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

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

4. 持久性(Durability):

在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

指不管系统是否发生故障，事务处理的结果都是永久的。

一单事务被提交，事务的效果会被永久的保留在数据库中。

事务见得相互影响

脏读

(读取未提交数据)

脏读是读到了别的事务回滚前的脏数据。
比如事务B执行过程中修改了数据x,在未提交前，事务A读取了x,而事务B却回滚了，这样事务A就形成了脏读。也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

不可重复读

(前后多次读取，数据内容不一致)
一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的,事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到row1，但列内

容发生了变化。

幻读

(前后多次读取，数据总量不一致)

一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也

修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表

中还有没有修改的数据行，就好象发生了幻觉一样。

MySQL及事务隔离级别

1. read uncommitted :读取尚未提交的数据:不解决脏读
2. read committed:读取已经提交的数据:可以解决脏读
3. repeatable read:重读读取:可以解决脏读和不可重复读–mysql默认的
4. serializable:串行化:可以解决脏读不可重复读和虚读–相当于锁表

MySQL默认的事务处理级别

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

查询全局事务隔离级别

show global variables like ' %isolation% ' ;

SELECT @@global.tx_isolation;

查询会话事务隔离级别

show session variables like '%isolation% ' ;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

设置全局事务隔离级别

set global transaction isolation level read committed;

设置会话事务隔离级别

set session transaction isolation level read committed;

事务控制语句

1. BEGIN或STARTTRANSACTION:手动开启一个事务。
2. COMMIT或COMMIT WORK:提交事务，并使已对数据库进行的所有修改变为永久性的。(ACID中的特性)
3. ROLLBACK或ROLLBACK WORK:回滚|会结束用户的事务，并撤销正在进行的所有未提交的修改。
4. SAVEPOINT S1:使用SAVEPOINT允许在事务中创建一个回滚点，一个事务中可以有多个
5. SAVBPOINT;"s1"代表回滚点名称。
6. ROLLBACK TO [SAVEPOINT] s1:把事务回滚到标记点。

测试回滚事务

原表

添加事务begin

添加 （s1）

添加s2

返回

最后提交事务commit

存储引擎

存储引擎概念介绍

 - MySQI中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功和能力（为了适应各种应用场景)，这些不同的技术以及配套的功能
在MysQL中称为存储引擎存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。
 - 常用的mysql数据存储引擎：MyISAM、Innodb
MysSQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

MyISAM特点

 - MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的
 - 访问速度快,对事务完整性没有要求
 - MyISAM适合查询、插入为主的应用场景
 - MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为:

	.frm   
	##文件存储表结构的定义

	MYD (MYData)
	##数据文件的扩展名

	.MYI(MYIndex)
	##索引文件的扩展名

 - 表级锁定形式,数据在更新时锁定整个表

 - 数据库在读写过程中相互阻塞:串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来

 - 会在数据写入的过程阻塞用户数据的读取

 - 也会在数据读取的过程中阻塞用户的数据写入

 - 特性:数据单独写入或读取，速度过程较快且占用资源相对少

 - MyIsam是表级锁定，i读或写无法同时进行

好处是分开执行时，速度快、资源占用相对较少（相对)

MyISAM表支持3种不同的存储格式

静态（固定长度)表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。
固定长度10
存储非常迅速，容器缓存，故障之后容易恢复

动态表

动态表包含可变字段（varchar），记录不是固定长度的，这样存储的有点是占用空间较少，，但是频繁的更新、删除记录会产生碎片，需要定期执行 optimize table 语句或myisamchk -r命令来改善，并且出现故障的时候回复相对比较困难。

压缩表

压缩表有myisamchk工具创建，占据非常小的空间，因此每条记录都是被单独压缩的，所以只有非常小的访问开支

MyISAM使用的生产场景

1. 公司业务不需要事务的支持
2. 单方面读取或写入数据比较多的业务
3. MyISAM存储引擎数据督学都比较频繁场景不适合
4. 使用读写并发放稳相对较低的业务
5. 数据修改相对较少的业务
6. 对数据业务一致性要求不是非常高的业务
7. 服务器硬件资源相对比较差

MyIsam:适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

INnnoDB特点

 1. 支持事务，支持4个事务隔离级别（数据不致问题）
 2. MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
 3. 5.5 之前是myisam(isam）默认

读写阻塞与事务隔离级别相关
能非常高效的缓存索引和数据
表与主键以簇的方式在悖心
支持分区、表空间，类似oracle数据库（5.5——》5.6和5.7第三代数据库8.o后版本)
支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

 对硬件资源要求还是比较高的场合

行级锁定，但是全表扫描仍然会是表级锁定（select ) ，

如
update table set a=1 where user like '%lic%' ;

 InnoDB中不保存表的行数
 
 如 
 select count () from table;
 
 InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。需要注意的是，当count()语句包含where条件时

MyISAM也需要扫描整个表

对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引

清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

死锁问题

MyISAM：表级锁定

INnnoDB：行级锁定

当两个请求分别访问/读取2行记录，同时又需要读取对方的记录数据，因为（行锁的限制）而造成了阻塞的现象

怎么解决死锁
第一种解决方案:查询线程信息，直接杀死该线程

查询线程信息sql是什么?
show processlist;  ##查看到产生死锁的线程

杀死线程:kill id;

第二种方案:
等待可以调整数据库的资源给与mysql更多的内存资源、线程资源，让产生死锁的线程能够冲破、
释放锁

预防死锁:

1. 给与相对充裕的mysql数据的资源
2. 调整更为精确的查找方案—》可以在前端页面的选择上给与更为精确的匹配栏
3. 定义更为合理的存储过程[解释]