Mysql比知必会系列教程(九) --------mysql事务

事务的概念：
事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

• 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
• 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
• 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。将数据库设计成单线程的数据库，可以防止所有线程安全问题，但是实际上是行不通的；
  多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。
  如果是两个线程并发修改一定会干扰，这时就需要用锁的机制防止并发修改；
  如果两个线程是查询，没有线程安全问题；

事务的使用：
事务是指逻辑上的一组操作，这组操作要么同时完成要么同时失败，数据库会管理事务，方式是一条语句独占一个事务
或者自己用以下的语句来操作：
Connection.setAutoCommit(false); //  相当于start transaction
Connection.rollback();  rollback
Connection.commit();  commit


要先把事务打开然后再进行事务的提交，只有提交后才能结束本次的操作；

mysql> select*from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   100 |
|  2 | b    |   100 |
+----+------+-------+
2 rows in set
mysql> start transaction;
Query OK, 0 rows affected
mysql> update account set money=money-100 where name='a';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select*from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |     0 |
|  2 | b    |   100 |
+----+------+-------+
2 rows in set


mysql> rollback;
Query OK, 0 rows affected


mysql> select *from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   100 |
|  2 | b    |   100 |
+----+------+-------+
2 rows in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> update account set money=money-100 where name='a';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> update account set money=money+100 where name='b';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select *from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |     0 |
|  2 | b    |   200 |
+----+------+-------+
2 rows in set

mysql> commit;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'comnit' at line 1
mysql> commit;
Query OK, 0 rows affected

mysql> 

一个jdbc操作事务例子：
//事务的管理例子：

public class JDBCTranDemo {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//设置回滚点；
Savepoint sp = null;
try{
conn = JDBCUtils.getConn();
//开启事务===start transaction
conn.setAutoCommit(false);

ps = conn.prepareStatement("update account set money=money-100 where name = ?");
ps.setString(1, "a");
ps.executeUpdate();


ps = conn.prepareStatement("update account set money=money+100 where name=?");
ps.setString(1, "b");
ps.executeUpdate();

//设置回滚点
sp = conn.setSavepoint();

ps = conn.prepareStatement("update account set money=money-100 where name = ?");
ps.setString(1, "a");
ps.executeUpdate();

String str = null;
str.toUpperCase();

ps = conn.prepareStatement("update account set money=money+100 where name=?");
ps.setString(1, "b");
//执行更新
ps.executeUpdate();
//事务提交
conn.commit();
}catch (Exception e) {
try {
if(sp == null){//如果回滚点为null说明没有执行到设置回滚点代码时就抛了异常,应该所有语句进行回滚
//事务回滚
conn.rollback();
}else{//说明sp不为null,可以会滚到回滚点,接着执行其他操作,但是要注意,如果希望之前的语句起作用,仍然需要做提交操作
//回滚后提交
conn.rollback(sp);
conn.commit();
}

} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally{
JDBCUtils.close(rs, ps, conn);
}
}
}

四大隔离级别：
安全性级别：

• Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
• Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）不可以避免虚读
• Read committed：可避免脏读情况发生（读已提交）
• Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

效率性：由后往前；
真正使用数据的时候，根据自己使用的数据库的需求，综合分析针对安全性和效率的要求来选择一个隔离级别；
mysql默认是Repeatable read：可避免脏读、不可重复读情况的发生。
oracle默认是read committed隔离级别；
其中如果不写括号里的是默认是session指的是当前客户端的；

• set [global/session]  transaction isolation level 设置事务隔离级别
• select @@tx_isolation 查询当前事务隔离级别

  问题一：脏读：一个数据读取到另一个未提交的数据；

a:1000;
b:1000;
a:
start transaction;
update account set money=money-100 where name=a;
update account set money=money+100 where name=b;
b:
start transaction;
select*from account;
a:900;
b=1100;
a:
rollback;
b:
start transaction;
select*from account;
a:1000;
b:1000;

问题二：不可重复读：
在一个事务内读取表中的某一行数据多次读取结果不同；和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。

a:1000 1000 1000;
b:银行职员：


------
b:先查询a的资产
b:start transaction;
select 活期 from account where name='a';--活期存款1000；
select 定期 from account where name='a';--定期存款1000；
select 国定 from account where name='a';--国定存款1000；
------
a同时做以下操作:
start transaction;
update account set 活期=money-1000 where name='a';---
commit;
------
b打印的报表单:
select 活期+定期+固定 from account where name='a';---2000;
commit;


    问题三：
虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
a: 1000
            b: 1000
            d: 银行业务人员
            
            -----------
            d:
                start transaction;
                select sum(money) from account; --- 2000 元
                select count(name) from account; --- 2 个
                
                ------
                c:
                    start transaction;
                        insert into account values(c,4000);
                     commit;
                ------
               
                select sum(money)/count(name) from account; --- 平均:2000元/个
                commit;
            |------------|
   |------------|

事务隔离级别的问题：
演示不同隔离级别下的并发问题

• set   transaction isolation level 设置事务隔离级别
• select @@tx_isolation 查询当前事务隔离级别

1.当把事务的隔离级别设置为read uncommitted时，会引发脏读、不可重复读和虚读

A窗口
set transaction isolation level  read uncommitted;
start transaction;
select * from account;
-----发现a帐户是1000元，转到b窗口

B窗口
start transaction;
update account set money=money+100 where name='aaa';
-----不要提交，转到a窗口查询

select * from account
-----发现a多了100元，这时候a读到了b未提交的数据（脏读）

2.当把事务的隔离级别设置为read committed时，会引发不可重复读和虚读，但避免了脏读
 

A窗口
set transaction isolation level  read committed;
start transaction;
select * from account;
-----发现a帐户是1000元，转到b窗口
B窗口
start transaction;
update account set money=money+100 where name='aaa';
commit;
-----转到a窗口


select * from account;
-----发现a帐户多了100,这时候，a读到了别的事务提交的数据，两次读取a帐户读到的是不同的结果（不可重复读）

3.当把事务的隔离级别设置为repeatable read(mysql默认级别)时，会引发虚读，但避免了脏读、不可重复读

A窗口
set transaction isolation level repeatable read;
start transaction;
select * from account;
----发现表有4个记录，转到b窗口

B窗口
start transaction;
insert into account(name,money) values('ggg',1000);
commit;
-----转到 a窗口

select * from account;
----可能发现表有5条记如，这时候发生了a读取到另外一个事务插入的数据（虚读）

4.当把事务的隔离级别设置为Serializable时，会避免所有问题

A窗口
set transaction isolation level Serializable;
start transaction;
select * from account;
-----转到b窗口

B窗口
start transaction;
insert into account(name,money) values('ggg',1000);
-----发现不能插入，只能等待a结束事务才能插入

 数据库中的锁的机制：

• 共享锁：在非serializable隔离级别做查询不加任何锁而在serializable隔离下做查询加共享锁；特点：共享锁和排他锁不能共存；
• 排它锁：在所有的隔离级别进行增删改的操作都会加排它锁特点：和任意的锁都不共存；
•  更新丢失问题:

   两个线程基于同一个查询结果进行修改,后修改的人会将先修改人的修改覆盖掉.
  两种解决方案：

•             悲观锁:悲观锁悲观的认为每一次操作都会造成更新丢失问题,在每次查询时就加上排他锁      select *from 表名 for update;这时加入排他锁后其他线程只有等待；查询很多的会降低效率
•             乐观锁:乐观锁会乐观的认为每次查询都不会造成更新丢失.利用一个版本字段进行控制 在表中加一个版本字段；update 表名 set stat=1 and version=version+1 where id=1 and version=0; 查询非常多,修改非常少,使用乐观锁  修改非常多,查询非常少,使用悲观锁

OK 就到这里把。