事务的基础知识

事务的状态

2. 使用事务

2.1 事务的完整过程

案例实战

USE atguigudb2

CREATE TABLE user3(name varchar(15) PRIMARY KEY)

SELECT * FROM user3

BEGIN;

INSERT INTO user3 VALUES('zs') # 此时不会自动提交事务

COMMIT


 BEGIN # 开启新事务
 
 INSERT INTO user3 VALUES('LS') # 此时不会自动提交事务
 INSERT INTO user3 VALUES('LS') # 受主键影响不能添加成功

ROLLBACK # 上一次添加的LS 不会存在，会回滚到之前commit之前的状态

# 情况2:
TRUNCATE TABLE user3 # 清空 DDL会自动提交数据，不会受到autocommit变量的影响

SELECT * FROM user3

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

INSERT INTO user3 VALUES('zs') # 默认情况下(autocommit = true),DML会自动提交数据

INSERT INTO user3 VALUES('zs') # 事务是一个失败的状态

ROLLBACK # 回滚到zs 的这个状态下

# 情况3
TRUNCATE TABLE user3
SELECT * FROM user3

SELECT @@COMPLETION_TYPE

SET  @@COMPLETION_TYPE = 1

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

SELECT * FROM user3


INSERT INTO user3 VALUES('zs') # 默认情况下(autocommit = true),DML会自动提交数据
INSERT INTO user3 VALUES('zs')

ROLLBACK

SELECT * FROM user3

# 举例2 体会innodb 和myisam

CREATE TABLE test1(i int) ENGINE = INNODB
CREATE TABLE test2(i int) ENGINE = MYISAM

# 针对INNODB
BEGIN
INSERT INTO test1 VALUES(1)

SELECT * FROM test1

ROLLBACK

# 针对MYISAM ：不支持事务
BEGIN
INSERT INTO test2 VALUES(1) 

SELECT * FROM test2

ROLLBACK

# 举例3 体会savepoint
CREATE TABLE user5(name varchar(15),balance DECIMAL(10,2))

BEGIN
INSERT INTO user5(name,balance) values('zs',1000)
COMMIT
SELECT * FROM user5

# 开启新事务
BEGIN 
UPDATE user5 SET balance = balance - 100 where name = 'zs'
UPDATE user5 SET balance = balance - 100 where name = 'zs'

SAVEPOINT s1 # 设置保存点
UPDATE user5 SET balance = balance + 1 where name = 'zs'

ROLLBACK TO s1 # 回滚到保存点

SELECT * FROM user5

ROLLBACK # 回滚到原始状态1000

SELECT * FROM user5