mysql day10 第十一章事务

-- 第十一章 事务
-- use sql_store;
-- start transaction;
-- 		insert into orders(customer_id,order_date,status)
-- 		values(1,'2019-01-01',1);

-- 		insert into order_items
-- 		values(last_insert_id(),1,1,1);
-- -- last_insert_id()会返回最新插入订单的id
-- commit; -- 事务结束
-- -- 错误检查 手动进行事务用rollback

-- show variables like 'autocommit' 当执行一条语句，这条语句就会存放到事务中，没有错误就提交

-- 并发和锁定
-- 两位用户同时更新积分
-- 用户1
-- use sql_store;
-- start transaction;
-- update customers
-- set points = points + 10
-- where customer_id = 2;
-- commit;
-- 用户2
-- use sql_store;
-- start transaction;
-- update customers
-- set points = points + 10
-- where customer_id = 2;
-- commit; 
-- 一共增加了20的积分
-- 单行执行com+回车，如果一个事务试图修改一行或多行，它给这一行上了锁，这个锁防止其他事务修改此行，直到第一个事务完成，被提交或者退回

-- 并发问题
-- 1丢失更新：两个事务尝试更新相同的数据，并且没有上锁，较晚提交的事务会覆盖之前提交的。
-- 2脏读：事务读取了未被提交的数据.设置隔离级别：读已提交
-- 3不可重复读：事务中读取了相同的数据两次，但得到了不同的结果。设置隔离级别：可重复读。其他事务更改数据，只按第一个读取的算
-- 4幻读：查询中缺失一行或多行，因为有另一个事务在修改数据，但我们没有意识到事务的修改。
-- 			设置隔离级别：序列化，提供确定性，但损坏性能和拓展性
-- 						能保证当有别的事务在更新数据时，第一个操作的事务能够知晓变动，必须等待别的事务先执行完再执行自己的。

-- 事务隔离级别
-- 最快的隔离级别：读未提交/不能解决问题，不设置任何锁，并忽略其他事务的锁，能遇到所有并发问题
-- 默认的隔离级别：可重复读，比可序列化快，并防止了除幻读之外的大多数并发问题
-- show variables like 'transaction_isolation';-- 查看当前系统变量REPEATABLE-READ
-- set transaction isolation level serializable; -- 为下一个事务设置隔离级别
-- set session transaction isolation level serializable;-- session会话连接，使所有未来的事务都会是这个隔离级别
-- set global 为所有会话中的所有新事物设置全局隔离

-- 读未提交事务隔离级别
-- 客户1
-- use sql_store;
-- set transaction isolation level read uncommitted;-- 使select读取未提交的数据，存在脏读
-- select points -- 读到的积分为20
-- from customers
-- where customer_id = 1; 
-- 客户2
-- use sql_store;
-- start transaction;
-- update customers
-- set points = 20
-- where customer_id = 1;
-- commit;
-- rollback; -- 使提交的语句没有执行

-- 读已提交隔离级别
-- 用户1
-- use sql_store;
-- set transaction isolation level read committed;-- 使select读取已提交的数据
-- start transaction;
-- select points from customers where customer_id = 1;  -- 20
-- select points from customers where customer_id = 1;  -- 30
-- commit; -- 提交后可回来更改下一个事务的隔离级别
-- 用户2
-- use sql_store;
-- start transaction;
-- update customers
-- set points = 30
-- where customer_id = 1;
-- commit; 

-- 可重复读隔离级别
-- 用户1
-- use sql_store;
-- set transaction isolation level repeatable read;-- 使select读取已提交的数据
-- start transaction;
-- select points from customers where customer_id = 1;  -- 30
-- select points from customers where customer_id = 1;  -- 30
-- commit;
-- 用户2
-- use sql_store;
-- start transaction;
-- update customers
-- set points = 40
-- where customer_id = 1;
-- commit;

-- 幻读
-- 1
-- use sql_store;
-- set transaction isolation level repeatable read;-- 使select读取已提交的数据
-- start transaction;
-- select * from customers where state = 'VA'; -- 能让顾客1被包括在这个客户1执行过的查询中
-- commit; -- 提交后才可看到两名在va中的客户
-- 2
-- use sql_store;
-- start transaction;
-- update customers
-- set state = 'VA' -- 更新数据
-- where customer_id = 1;
-- commit; -- 得到两个顾客都在va中，但会漏掉顾客1

-- 序列化隔离级别
-- 1
-- use sql_store;
-- set transaction isolation level serializable;-- 使select读取已提交的数据
-- start transaction;
-- select * from customers where state = 'VA'; -- 能让顾客1被包括在这个客户1执行过的查询中
-- commit; -- 提交后才可看到两名在va中的客户
-- 2
-- use sql_store;
-- start transaction;
-- update customers
-- set state = 'VA' -- 更新数据
-- where customer_id = 3;
-- commit; -- 顾客3也在va中，用户2先执行完用户1中才会看到三条结果

-- 死锁
-- 用户1
-- use sql_store;
-- start transaction;
-- update customers set state = 'VA' where customer_id = 1; -- 事务会锁定这条记录
-- update orders set status = 1 where order_id = 1; -- 提示deadlock
-- commit;
-- 用户2
-- use sql_store;
-- start transaction;
-- update orders set status = 1 where order_id = 1;-- 事务会锁定这条记录
-- update customers set state = 'VA' where customer_id = 1; -- 等待第一个用户
-- commit;
-- 经常发生死锁的解决办法：这些事务可能是存储过程中的一部分，如果这些事务以相反的顺序更新记录，就出现死锁，所以我们在更新多条记录的时候遵守相同的顺序
-- 简化事务，缩短运行时。如果事务在一个很大的表中运行，就会有冲突的风险，可以把事务安排在非高峰的时段运行，避开活跃用户