mysql之事务简介、并发事务的四个问题、事务的四个隔离级别以及事务的四大特性

事务：transaction

1、控制事务
事务：transaction
1、什么是事务？
事务就是一堆的sql语句+逻辑判断
一个数据库事务由一条或者多条sql语句构成，它们形成一个逻辑的工作单元。这些sql语句要么全部执行成功，要么全部执行失败
事务是保证数据的完整性和一致性的重要手段
2、为什么要使用事务？
要将一部分的操作作为一个整体来执行，要么所有的语句都成功，要么所有的语句都失败
3、事务的类型
DML事务：由一条或者多条DML语句构成
如果关闭了autocommit参数，需要手工commit参数或者rollback。
如果没有关闭autocommit参数，mysql会自动执行commit操作。
如果显示的开启一个事务（start transaction），就需要手工的执行commit和rollback
DDL事务：由一条DDL语句构成（create table…/create index…）
执行完了DDL语句之后，mysql自动执行一个commit操作
DCL事务：由一条DCL语句构成（创建一个用户类似的操作）数据控制事务
执行完了DCL操作时，mysql自动执行一个commit。
保存点：事务的一个事件
**4、**在MySQL中，系统变量@@autocommit默认是打开的，这意味着任何1条SQL语句 都会开始一个事务，语句执行完后事务自动结束。实际使用中，应该使用SET语句 来关闭自动提交，否则一个事务不可能由多条SQL语句构成
@@autocommit作用：
如果将该变量打开（1打开，0关闭），MySQL会认为每一条DML语句都是一个事务，自动提交（Oracle数据库默认是关闭的，要手工提交）。
要将@@autocommit关闭，防止误操作，造成数据库数据的丢失损坏
SHOW VARIABLES LIKE ‘%autocommit%’;

SET @@autocommit=0;
SHOW VARIABLES LIKE '%autocommit%‘;

事务的开始和结束：
对于DDL（create、alter、drop等开头的语句）和DCL（grant、revoke语句）事务，在执行每条语句之前和之后，MySQL会自动执行一条COMMIT语句，因此事务是自动开始和结束的。自动提交打开或者关闭对这些事务没有影响

对于DML事务，在自动提交关闭的情况下，事务的开始分为隐式开始和显式开始：
隐式开始：程序的第一条DML语句执行时或者在COMMIT或ROLLBACK语句之后执行第一条DML语句时，自动开始一个新的事务

显式开始：发出STRAT TRANSACTION语句。该语句会自动关闭自动提交，当事务结束后，autocommit变量恢复到原来的值

DML事务的结束：
COMMIT语句：成功提交。事务所做的全部工作被永久地保存到磁盘上
ROLLBACK语句：失败回滚。事务所做的全部工作被撤销，表中的数据不受事务操作的影响
其它事务控制语句：
SAVEPOINT identifier ：保存点命令，用来在事务中做一个标记，专门提供给rollback to语句使用
ROLLBACK TO [SAVEPOINT] identifier：回滚到保存点。专门用来撤销事务所做的部分工作：保存点之后所做的工作全部撤销。该语句并不结束事务
事务示例：

CREATE TABLE trans_demo(
id INT,
NAME VARCHAR(20)
);

START TRANSACTION;

INSERT INTO trans_demo(id,NAME) VALUES(1,‘zs’);
INSERT INTO trans_demo(id,NAME) VALUES(2,‘ls’);

SAVEPOINT a;
UPDATE trans_demo SET NAME=‘ww’ WHERE id=1;
SAVEPOINT b;
DELETE FROM trans_demo WHERE id=2;

– 下面决定如何结束事务
ROLLBACK TO b;
ROLLBACK TO a;
ROLLBACK;
COMMIT 或 ROLLBACK 语句之前数据的状态 ：
1.数据的修改都是在内存中进行的
2.通过查询表，当前用户（事务）能够查看DML操作的结果
3.其它用户（事务）不能查看当前用户（事务）所做的DML操作的结果。这叫做不允许脏读（dirty read）。脏读：一个事务读到了另一个事务未提交的数据。已修改但未提交的数据叫做赃数据
表中受影响的行被锁定，其它用户（事务）不能在受影响的行上修改数据

不允许脏读示例：

以用户newroot分别打开两个会话
第一个会话中执行：
START TRANSACTION;
INSERT INTO trans_demo(id,NAME) VALUES(1, ‘张三’);
SELECT * FROM trans_demo;
事务未结束
再在第二个会话中执行：
SELECT * FROM trans_demo;
看不到张三
第一个会话中再执行：
COMMIT;
再在第二个会话中执行：看到了张三
SELECT * FROM trans_demo;
两个事务不允许同时修改表中的同一行示例：
第一个会话中执行：
START TRANSACTION;
UPDATE trans_demo SET NAME=‘李四’ WHERE id=1;
SELECT * FROM trans_demo;
事务未结束
再在第二个会话中执行：
UPDATE trans_demo SET NAME=‘李四’ WHERE id=1;
可以看到会话被阻塞，因为它得不到要修改的行上面的锁，锁被第一个会话持有
第一个会话中再执行：
COMMIT;
这时，第二个会话退出阻塞，成功进行修改

COMMIT或ROLLBACK语句之后数据的状态：
COMMIT之后：
1.数据改变被写到数据库中
2.所有用户（事务）可以查看事务的结果
3.表中受影响行上的锁被释放，这些行现在可以被其它用户（事务）修改
4.事务中所有的保存点被删除
ROLLBACK之后：
1.数据改变被撤销
2.数据先前的状态被恢复
3.表中受影响行上的锁被释放

5.commit/rollback
commit：将所有的操作永久的写入数据库中
Rollback：回滚之前的操作，不写入数据库

6.savepoint identifier
建立一个保存点，供rollback to语句使用

7.rollback to identifier
回滚到某一个固定的保存点，回滚的是整个事务部分的操作，回滚点之后的操作全部撤销，rollback to 操作不会结束整个事务。

8.commit与rollback操作前的数据情况
1.所有的数据都是在内存中操作，不写入磁盘（不持久化）
2.如果事务未提交或者回滚，别的会话不可以看到该事务修改的数据
3.本事务修改的数据行会被锁定，不允许其他事务进行操作

9.commit与rollback操作后的数据情况
1.所有的数据都持久化到磁盘
2.所有的会话都可以看到修改后的数据
3.提交之后，锁定的事务，其他事务可以对这些行进行操作

10.并发事务的4个问题：
当有多个事务同时运行时，可能存在以下4个问题：
1.脏读
事务t1可以读取到事务t2未提交的数据
一个事务读到了另一个事务未提交的数据
2.不可重复读
同一个事务，同样的条件，前后可能得到不同的数据
在同一个事务中，同样的条件，你读取过的数据再次读取出来时发现值不一样了。示例：
在事务1中，Mary 读取了自己的工资为1000，事务没结束
select salary from employee empId =‘Mary’;
在事务2中，财务人员修改了Mary的工资为2000，并提交了事务
update employee set salary = 2000;
commit;
在事务1中，Mary 再次读取自己的工资时，工资变为了2000
select salary from employee empId =‘Mary’;
3.幻读
同一个事务，同样的条件，前后可能得到不同的数据
在同一个事务中，同样的条件，第1次和第2次读出来的记录数不一样。示例：
在事务1中，读取所有工资为1000的员工（10人）
Select * from employee where salary =1000;
在事务2中，向employee表插入了一条员工记录，工资也为1000 ，并提交
Insert into employee(empId,salary) values(‘zs’,1000);
commit;
事务1再次读取所有工资为1000的员工（11人）
Select * from employee where salary =1000;
两者都表现为两次读取的结果不一致。区别是：不可重复读的重点是修改，幻读的重点在于新增或者删除
幻读的解决方式是对表加表锁解决。
不可重复读的解决方式可以对特定的行加锁解决。
4.丢失更新
一个事务的修改覆盖了另一个事务所做的修改
示例：以下操作按顺序执行
事务1读取4号球员的罚款额，为50
事务2读取4号球员的罚款额，也为50
事务1把罚款额增加25，并提交
Update penalties set amount=amount + 25;
Commit;
事务2把罚款额增加30，并提交
Update penalties set amount=amount + 30;
Commit;
此时，事务1认为最新的罚款额为75，但是实际上是80，事务1所做的更新操作被事务2覆盖掉了。事务1的更新丢失了

事务1，对ID列执行了update操作，修改为5，事务2，对ID列执行update操作，将ID列修改为10，这时事务1提交，然后事务2再提交。

11.以上所有的4个问题都可以通过不允许两个用户同时运行一个事务来很容易的解决掉：用户1的事务没有结束，用户2的事务就不能开始，这样就不会出错了。但是这样的话，数据库的并发性能极差，不能接受
每个事务都有一个隔离级别（isolation level），它规定了并发运行的两个事务之间是否允许发生上面的问题。MySQL有4种事务隔离级别：
REPEATABLE READ ：可重复读。默认级别
READ COMMITTED：读已提交
READ UNCOMMITTED：读未提交
SERIALIZABLE：串行化

事务的四个隔离级别：
未提交读
READ-UNCOMMITTED
1.发生了脏读
2.发生了不可重复读
3.发生了幻读
4.发生了丢失更新
已提交读
READ COMMITTED
1.避免了脏读
2.发生了不可重复读
3.发生了幻读
4.发生了丢失更新
可重复读
REPEATABLE READ
1.避免了脏读
2.避免了不可重复读
3.避免了幻读
4.发生了丢失更新
串行化
SERIALIZABLE
1.避免了脏读
2.避免了不可重复读
3.避免了幻读
4.避免了丢失更新
事务的隔离级别建议在创建数据库时，在配置文件中指定，最好不要后期修改。

查看数据库及当前会话的事务隔离级别
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
分别设置数据库及当前会话的事务隔离级别
SET GLOBAL tx_isolation=‘REPEATABLE-READ’;
SET SESSION tx_isolation=‘SERIALIZABLE’;
或者使用SET TRANSACTION语句来设置隔离级别
SET [GLOBAL | SESSION] TRANSACTION
ISOLATION LEVEL level
其中，level:
REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE
该语句对正在运行的事务没有影响，从下一个事务开始起作用
在配置文件中指定数据库的事务隔离级别
[mysqld]
transaction-isolation = REPEATABLE-READ

12.事务的四大特性（ACID）
原子性：整个事务作为一个整体，要么都成功，要么所有的都失败。
**一致性：**同一个事务，相同的条件，不同的时间要得到相同的结果。
**隔离性：**两个事务之间在未提交之间只能看到自己修改的数据，不能看到对方修改的数据。
**持久性：**持久化到磁盘

13.LOCK TABLES语句
MySQL允许客户端会话显式地获得一个表锁，以防止其它会话修改表
显式的获取表锁
语法：
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] …
锁类型lock_type：
READ：所有的会话只能读取，不能修改
对于innodb引擎， READ [LOCAL] = READ ， LOW_PRIORITY被废弃
表现：所有会话可以查询，当执行锁住的会话执行修改命令时会报错，其 他会话会被锁住，一直等待。
WRITE:当前会话可以对锁住的表进行读写，但是其他会话不能读写。
Lock tables和unlock tables的提交（注意：针对innodb存储引擎）：
Lock tables会提交锁表之前的操作，相当于执行了commit
Unlock tables不会提交锁表之前的操作。

UNLOCK TABLES：释放当前会话持有的所有表锁

Lock tables锁表之后只能查看被锁定的表，不能查看未被锁的表。
为什么需要显示表锁操作：
当需要更新一张表中大部分数据时，显示获取表锁，防止因为mysql本身或者其他事务对该表，提高执行效率。
正常业务如果一个事务更新一张表的10数据，但是当该事务1执行到第三行时，此时另一个事务2获取了第四行数据进行修改，此时事务1被锁住，发生等待，降低事务1的执行效率。

案例：
在会话1中执行：
LOCK TABLES players_copy1 READ;
SELECT * FROM players_copy1;
UPDATE players_copy1 SET NAME=‘zs’ WHERE playerno=6;
错误代码： 1099
Table ‘players_copy1’ was locked with a READ lock and can’t be updated
在会话2中执行：
SELECT * FROM players_copy1;
UPDATE players_copy1 SET NAME=‘zs’ WHERE playerno=6;
会话2被阻塞。会话1执行unlock tables语句后，会话2退出阻塞
注意：lock tables语句会自动提交当前活动的事务，而unlock tables语句不会（对于innodb表）
START TRANSACTION;
SELECT * FROM players_copy1;
UPDATE players_copy1 SET NAME=‘www1’ WHERE playerno=6;
LOCK TABLES players_copy1 READ;
ROLLBACK;
SELECT * FROM players_copy1; —看到www1

START TRANSACTION;
SELECT * FROM players_copy1;
UPDATE players_copy1 SET NAME=‘www2’ WHERE playerno=6;
UNLOCK TABLES;
ROLLBACK;
SELECT * FROM players_copy1; —看到www1
注意：当使用lock tables语句时，其后的操作只能访问被锁定的表，而不能访问未被锁定的表
LOCK TABLES players_copy1 READ;
SELECT count(*) FROM players_copy1;

SELECT count(*) FROM players;
错误代码： 1100
Table ‘players’ was not locked with LOCK TABLES
当需要更新表中的大量行时，可以加表锁，以减少数据库的内部管理操作，提高效率

14.加共享锁SELECT…LOCK IN SHARE MODE
SELECT…LOCK IN SHARE MODE
对查询得到行加共享锁。其它会话可以查询这些行，但是不能修改它们，除非你结束事务释放所加的锁。如果你要加锁的行正被其它事务修改，查询就被阻塞直到其它事务提交
该语句主要用在需要数据依赖关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。例如：你想向子表child中插入一行，首先要确保子行所依赖的父行在父表parent中存在。先使用该语句查询出父行
SELECT * FROM parent
WHERE NAME = ‘Jones’ LOCK IN SHARE MODE;
如果查询返回了‘Jones’ ，你就可以放心的插入子行并提交事务。如果在查询时不带LOCK IN SHARE MODE子句，那么在插入子行之前父行很可能就被其它事务删除了
注意：如果当前事务也需要对锁定的记录进行更新操作，则很有可能造成死锁。例如：
会话1执行：
select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode;
会话2执行：
select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode;
会话1执行：被阻塞
update actor set last_name = ‘MONROE T’ where actor_id = 178;
会话2执行，导致死锁退出
update actor set last_name = ‘MONROE T’ where actor_id = 178;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
SELECT…FOR UPDATE
对查询得到行及相关的索引加排他锁。其它事务试图修改这些行时会被阻塞
如果你想查询出某些行后，随后自己修改它们而阻止其它事务修改，就使用该语句
示例：
会话1执行：
START TRANSACTION;
SELECT * FROM players_copy1 FOR UPDATE;
会话2执行：
START TRANSACTION;
SELECT * FROM players_copy1;
被阻塞
UPDATE players_copy1 SET NAME='zs’ WHERE playerno=6;

语法：select … Lock in share mode；
执行锁住表的会话：可以读写
其余回话只能读，不能写。
场景：
假如有两张表，一张是学号是学生表，一张是成绩表，学生表和成绩表是相互关联的。此时我想修改姓名为张三的语文成绩，这时可以对学生表加锁（防止在此事务执行期间其他事务修改张三数据行），如果能查到张三学生，再执行update操作
update student set chengji=100 where id =(select id from student where name=‘zhangsan’);

死锁：事务1持有资源1，事务2持有资源2，此时事务1想获取资源2，事务2想获取资源1，此时就会发生死锁，发生死锁时，mysql会自主回滚一个事务（rollback）。
回滚依据：事务的权重（执行该事务的资源消耗），那个事务消耗资源少回滚那个。

15.加for update更新锁
当前会话可以进行修改和查询，其他会话只能查询，不能修改。
等到更新结束，其他回话才会获取被锁住的行执行操作。
常用于测试，测试模拟堵塞环境。

16.应用程序锁（很少用）
和其他锁的区别：
表锁锁住的是一个表，行锁锁住的是一行
应用锁锁住的是一个一部分操作。
很少使用，记住概念即可。

MySQ支持一种应用层级别的锁，称为应用程序锁或命名锁（named lock）。该锁和表及表中的行无关。通过这个锁，访问MySQL的应用程序可以实现互斥功能。这个锁通过一组MySQL 内置函数来实现
GET_LOCK(str,timeout)：
Str：锁的名字
Timeout：超时时间
创建一个名为str的应用程序锁。如果创建成功则返回1；如果名为str的锁已经存在，等待timeout秒的时间后还得不到锁则返回0；如果发生错误则返回null。
一个应用程序获取到锁后，可以通过RELEASE_LOCK(str)、执行新的GET_LOCK(str,timeout)、或者断开mysql连接（不管是正常释放还是异常断开）这三种方式释放锁
RELEASE_LOCK(str) ：
释放名为str的锁。如果锁被成功释放，返回1；如果这个进程没有占有该锁，则返回0；如果这个名为str的锁不存在，则返回NULL
IS_USED_LOCK(str)
检查名为str的锁是否在使用，如果被使用，则返回拥有该锁的连接标识符，否则返回NULL
IS_FREE_LOCK(str)
检查名为str的锁是否可用，如果可用则返回1；如果在使用则返回0；如果有错误则返回NULL
示例：
会话1执行：
创建一个名为lock1的应用程序锁。返回1
SELECT get_lock(‘lock1’,0);
会话2执行：
SELECT get_lock(‘lock1’,0); — 返回0
检查锁lock1是否可用
SELECT is_free_lock(‘lock1’); — 返回0，表示不可用
查询使用lock1锁的连接标示符
SELECT is_used_lock(‘lock1’); --返回3