Mysql学习之数据库事务

事务

数据库事务概述
事务是数据库区别于文件系统的重要特性之一，当有了事务就可以让数据库始终保持一致性。同时还可以通过事务的机制，恢复到某个时间点，这样就可以保证以提交到数据库的修改不会因为系统崩溃而丢失

只有Innodb支持事务的

事务：一切逻辑的操作单元，使数据从一种状态转化到另外一种状态

事务处理的原则

事务的ACID特性

原子性（atomicity）
原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间状态的。如果无法保证，就会出现数据不一致的情形。A账户减去100元，B账户增加100元失败，系统将无故丢失100元。
一致性（Consistency）
一致性是指事务执行前后，数据从一个合法状态变换到另外一个合法性状态。这种状态语义的，而不是语法上的，跟具体的业务无关。

隔离性（Isolation）
事务的隔离性是指从一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来其他操作和数据库故障不应该对其有任何影响。
持久性是通过事务日志来保证的，日志包含了重做日志和回滚日志。当通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。
总结
ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。
事务的状态



如何开启事务
使用事务有两种方式，分别为显式事务和隐式事务
显示事务
START TRANSACTION 或者BEGIN ，作用是显式开启一个事务。

BEGIN;

END

START TRANSACTION;

START TRANSACTION 相比较于BEGIN特别之处在于，后面能跟随几个修饰符。


隐式事务
mysql中有一个系统变量autocommit;

SHOW VARIABLES LIKE '%autocommit%'

Oracle默认不自动提交，需要手写COMMIT命令，而Mysql默认自动提交

举例1：commit和rollback的使用

#情况1
CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三');  #此时不会自动提交事务·
COMMIT;

BEGIN; #开启一个新的事务
INSERT INTO user3 VALUES('王五'); 
INSERT INTO user3 VALUES('王五'); #受主键影响不能添加成功 
ROLLBACK;

SELECT * FROM user3;




#情况2
TRUNCATE TABLE user3; ##DDL操作会自动提交事务，不受autocommit变量的影响

SELECT * FROM user3;
 
 BEGIN;
INSERT INTO user3 VALUES('张三');  #此时不会自动提交事务·
COMMIT;

INSERT INTO user3 VALUES('王五'); #默认情况下(即autocommit=true)，DML操作也是会自动提交事务
INSERT INTO user3 VALUES('王五'); #事务的失败状态

ROLLBACK;

SELECT * FROM user3;


#情况3

TRUNCATE TABLE user3; ##DDL操作会自动提交事务，不受autocommit变量的影响

SELECT * FROM user3;

SELECT @@completion_type;

SET @@completion_type=1;
 
BEGIN;
INSERT INTO user3 VALUES('张三');  ·
COMMIT;

INSERT INTO user3 VALUES('王五'); 
INSERT INTO user3 VALUES('王五'); 
ROLLBACK;

SELECT * FROM user3;

mysql中completion_type参数的作用

举例2：体会INNODB和MYISAM的区别

CREATE TABLE test1(i INT) ENGINE=INNODB;

CREATE TABLE test2(i INT) ENGINE=MYISAM;

#针对innodb表
BEGIN
INSERT INTO test1 VALUES(1);
ROLLBACK;

SELECT * FROM test1;


#针对myisam表:不支持事务
BEGIN
INSERT INTO test2 VALUES(1);
ROLLBACK;

SELECT * FROM test2;

举例3： 体会savepoint

CREATE  TABLE user4(NAME VARCHAR(15),balance DECIMAL(10,2));

BEGIN
INSERT INTO user4(NAME,balance)VALUES('张三',1000);
COMMIT;

SELECT * FROM user4;


BEGIN;
UPDATE user4 SET balance=balance-100 WHERE NAME='张三';

UPDATE user4 SET balance=balance-100 WHERE NAME='张三';

SAVEPOINT s1;#设置保存点

UPDATE user4 SET balance=balance-100 WHERE NAME='张三';
UPDATE user4 SET balance=balance+1 WHERE NAME='张三';

ROLLBACK TO s1; #回滚到·S1保存点

SELECT * FROM user4;


ROLLBACK;

SELECT * FROM user4;

事务隔离级别

数据并发问题
针对事务的隔离性和并发性，应该如何取舍？先看一下访问相同数据的事务在不保证串行执行的情况下，可能出现的问题：
1.脏写
对于两个事务SessionA、SessionB,如果事务SeesionA修改了另一个未提交事务SessionB修改过的数据，那就意味着发生了脏写。

2.脏读
对于两个事务SessionA、SessionB,SessionA读取了已经被SessionB更新但还没有被提交的字段，之后若SessionB回滚，SessionA读取的内容是临时且无效的。
3. 不可重复读
对于两个事务SessionA、SessionB,Session A读取了一个字段，然后SessionB更新了该字段，之后SessionA再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

4. 幻读
对于两个事务SessionA、SessionB,SessionA从一个表中读取了一个字段，然后SessionB在该表中插入了一些新的行，之后，如果SessionA再次读取同一个表，就会多出几行，那就意味着发生了幻读。


幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

SQL中的四种隔离级别

问题的严重性排序
脏写>脏读>不可重复读>幻读
舍弃一部分隔离性来换取一部分性能：设立一些隔离级别，隔离级别越低，并发问题发生的就越多。SQL标准中设立了四个隔离级别：
**读未提交：**在该隔离级别下，所有事务都可以看到其他未提交事务的执行结果，不能避免脏读、不可重复读、幻读。
读已提交: 它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这个是大多数数据库系统默认的隔离级别（但不是Mysql默认的）可以避免脏读、但不可重复读、幻读问题依然存在。
可重复读： 事务A在读到一条记录后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读、但幻读问题仍然存在。这是MySQL默认的隔离级别。
可串行化： 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表的插入、更新和删除操作，所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读、幻读。


Mysql支持的四种隔离级别
oracle只支持读已提交和串行化的隔离级别

SHOW VARIABLES LIKE 'tx_isolation'

SHOW VARIABLES LIKE 'transaction_isolation'

设置隔离级别


脏读演示：
事务1

SHOW VARIABLES LIKE 'transaction_isolation'


CREATE TABLE account(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(15),
  balance DECIMAL(10,2)
  
);

INSERT INTO account VALUES(1,'张三','100'),(2,'李四','0');


SELECT * FROM account;

SET SESSION transaction_isolation='read-uncommitted';


BEGIN;

UPDATE account SET balance=balance+100 WHERE id=1

ROLLBACK;

事务2

SET SESSION transaction_isolation='read-uncommitted';

SELECT * FROM account;

读已提交
事务1

TRUNCATE TABLE ACCOUNT;

SET SESSION transaction_isolation='read-committed';


BEGIN;

UPDATE account SET balance=balance-50 WHERE id=1


COMMIT

事务2

SHOW VARIABLES LIKE 'transaction_isolation'

SET SESSION transaction_isolation='read-committed';


BEGIN;

SELECT * FROM account;

SELECT * FROM account;

可重复读
事务1

INSERT INTO account VALUES(1,'张三','100'),(2,'李四','0');


SELECT * FROM account;

TRUNCATE TABLE ACCOUNT;

SET SESSION transaction_isolation='repeatable-read';


BEGIN;

SELECT * FROM account;


UPDATE account SET balance=balance-10 WHERE id=1


COMMIT

事务2

SHOW VARIABLES LIKE 'transaction_isolation'

SET SESSION transaction_isolation='repeatable-read';


BEGIN;

SELECT * FROM account;

SELECT * FROM account;

什么是幻读更深层次的解释