MySQL数据库MySQL事务与存储引擎

目录

一、MySQL事务

（一）定义

（二）ACID特性

1.原子性

2.一致性

3.隔离性

4.持久性

（三）隔离级别

1.可能出现的一致性问题

（1）脏读

（2）不可重复读

（3）幻读

（4）丢失更新

2.隔离级别

（1）未提交读

（2）提交读

（3）可重复读

（4）串行读

3.设置隔离级别

（1）全局级

?编辑

（2）会话级

?编辑

4.查看隔离级别

（1）全局级

?编辑

（2）会话级

?编辑

（四）事务管理操作

1.开启、操作事务

2.事务回滚

?编辑

3.结束事务

（1）提交事务

（2）回滚事务

4.自动提交事务

?编辑

二、MySQL存储引擎

（一）定义

（二）常用的存储引擎

1.MyISAM

2.InnoDB

（三）查看存储引擎

?编辑

?编辑

（四）修改存储引擎

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

?编辑

2.新建表时指定存储引擎

?编辑

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

?编辑

（五）死锁

1.现象

2.避免死锁

---

一、MySQL事务

（一）定义

? ? ? ?事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。

（二）ACID特性

? ? ? ?ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：

1.原子性

? ? ? 事务管理的基础。事务中所有操作看做一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

2.一致性

? ? ? 事务管理的目的。保证事务开始之前和事务结束以后，数据库的完整和一致。

3.隔离性

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

4.持久性

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

（三）隔离级别

1.可能出现的一致性问题

? ? 当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

（1）脏读

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

（2）不可重复读

? ? ? ?指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）

（3）幻读

? ? ? ?一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。

（4）丢失更新

? ? ? 两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

2.隔离级别

? ?MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务

（1）未提交读

? ? ?read uncommitted ? 允许 脏读 不可重复读 ?幻读

（2）提交读

? ? ?read committed ? ? 不允许 脏读，允许 不可重复读 ?幻读

（3）可重复读

? ? ?repeatable read ? ?不允许 脏读 不可重复读，有条件的允许 幻读（InnoDB存储引擎可以不允许）

（4）串行读

? ? ?Serializable ? ? ? 都不允许，相当于表级锁定，但是会影响数据库的读写效率和性能

3.设置隔离级别

（1）全局级

? ? 对所有的会话有效，需要重新登录才可生效。

set global transaction isolation level 隔离级别；

（2）会话级

? ? 只对当前的会话有效，在当前会话中立即生效。

set session transaction isolation level 隔离级别；

4.查看隔离级别

（1）全局级

show global variables like '%isolation%';

（2）会话级

show session variables like '%isolation%';

（四）事务管理操作

1.开启、操作事务

begin;             
#开启一个事务
  create database/table 
  insert into 
  update XXX set   
  delete from     
#事务性操作

2.事务回滚

savepoint XX;      
#在事务中创建回滚点
rollback to XX;    
#在事务中回滚操作到指定的回滚点位置

3.结束事务

（1）提交事务

? ? ?保存事务中命令操作的结果后，结束事务。

（2）回滚事务

? ? ?不保存事务中命令操作的结果，结束事务后，事务还原到开启前的数据和状态。

commit; 
#提交结束事务
rollback;   
#回滚结束事务

4.自动提交事务

? ? ? ? 如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的提交commit；

? ? ? ? 如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。

set autocommit=0;				
#禁止自动提交
set autocommit=1;				
#开启自动提交，Mysql默认为1
show [global/session] variables like 'autocommit';		
#查看Mysql中的AUTOCOMMIT值,[global代表全局，session代表当前会话，都不加则默认当前会话]

二、MySQL存储引擎

（一）定义

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

（二）常用的存储引擎

1.MyISAM

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

2.InnoDB

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

（三）查看存储引擎

show create table 表名;

show table status [from 库名] where name='表名'\G
#查看表使用的存储引擎

show engines;
#查看系统支持的存储引擎

（四）修改存储引擎

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

alter table 表名 engine=InnoDB/MyISAM;             
#针对已存在的表修改存储引擎

2.新建表时指定存储引擎

create table 表名 (....) engine=InnoDB/MyISAM;     
#新建表时指定存储引擎

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

vim /etc/my.cnf    
  [mysqld]
  default-storage-engine=InnoDB/MyISAM         
#设置新建表的默认存储引擎

（五）死锁

1.现象

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

2.避免死锁

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