mysql优化 -- 事务

什么存储引擎支持事务

1.查看数据库下面是否支持事务（InnoDb支持）？

SHOW ENGINES

2.查看mysql当前默认的存储引擎？

show variables like '%storage_engine%';

3.查看某张表的存储引擎？

show create table 表名;

CREATE TABLE `user` (
  `userid` varchar(64) NOT NULL,
  `username` varchar(64) DEFAULT NULL,
  `fullname` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4.关于表的存储机构的修改

建立Innodb表：create table..type=InnoDB;

alter table table_name type=InnoDB;

事务的特性

事务具有4个特性：

原子性，一致性，隔离型，持久性。

原子性：一个事务是一个不可分割的工作单位，事务中包括的操作要么做，要么不做。

一致性: 事务必须要使数据库从一个一致性状态变到另一个一致性状态。

隔离性：一个事务的执行不能被其他事务干扰。

持久性：指事务一旦提交，他对数据库中数据的改变就应该是永久性的。

事务的持久性不能通过数据库的绝对完全能解决

 

事务的隔离性

分为四种：

未提交读（Read Uncommit）脏读

已提交读（Read commit）不可重复读

可重复读（Repeatable read）

可串行化（Serializable）

mysql默认的事务隔离级别为Repeatable-read 

SHOW VARIABLES LIKE '%tx_isolation%';

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据。

不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更改并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：事务A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是事务B就在这个时候插入了一条具体分数的记录，当事务A改完之后，发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增和删除。

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁住表。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

未提交读，脏读

set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
一个session中
start TRANSACTION
update account set balance = balance -50 where id = 1
另外一个session中查询
select * from account
回到第一个session中 回滚事务
ROLLBACK
在第二个session种
update account set balance = balance -50 where id = 1
查询结果还是 400

已提交读，不可重复读

show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL read committed;
一个session中
start TRANSACTION
update account set balance = balance -50 where id = 1
另外一个session中查询 (数据并没改变)
select * from account
回到第一个session中 回滚事务
commit
在第二个session种
select * from account (数据已经改变)

可重复读

set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
一个session中
start TRANSACTION
update account set balance = balance -50 where id = 1
另外一个session中查询 (数据并没改变)
select * from account
回到第一个session中 回滚事务
commit
在第二个session种
select * from account (数据并未改变)

可串行化

 

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表

 

事务隔离级别为串行化时，读写数据都会锁住整张表

 

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

 

事务语法

开启事务

1、begin

2、START TRANSACTION（推荐）

3、begin work

事务回滚  

rollback

​​​​​​​事务提交

  commit

​​​​​​​还原点

savepoint

show variables like '%autocommit%';  自动提交事务是开启的

set autocommit=0;

insert into testdemo values(5,5,5);

savepoint s1;

insert into testdemo values(6,6,6);

savepoint s2;

insert into testdemo values(7,7,7);

savepoint s3;

select * from testdemo

rollback to savepoint s2

rollback