实现ZhangSi(1)给LiSan(2)转账的过程：
(非事务：)
复制代码
public static void TransferNonTransaction() {
Connection conn = null;
PreparedStatement ptmt = null;
try {
conn = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);
String sql = "UPDATE User SET Account = ? WHERE userName = ? AND id = ?;";
// transfer 100 from ZhangSi(1) to LiSan(2)
ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, 0);
ptmt.setString(2, "ZhangSi");
ptmt.setInt(3, 1);
ptmt.execute();
ptmt.setInt(1, 100);
ptmt.setString(2, "LiSan");
ptmt.setInt(3, 2);
ptmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (ptmt != null) ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
复制代码
执行完第一个ptmt.execute()后，数据库中ZhangSi的Account=0, LiSan的Account=0；
出现了一个中间状态，对于整个业务逻辑的实现是不可接受的。如果此时程序崩溃了将不可挽回。
(事务：)
复制代码
public static void TransferByTransaction() {
Connection conn = null;
PreparedStatement ptmt = null;
try {
conn = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);
// Using Transaction mechanism
conn.setAutoCommit(false);
String sql = "UPDATE User SET Account = ? WHERE userName = ? AND id = ?;";
ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, 0);
ptmt.setString(2, "ZhangSi");
ptmt.setInt(3, 1);
ptmt.execute();
ptmt.setInt(1, 100);
ptmt.setString(2, "LiSan");
ptmt.setInt(3, 2);
ptmt.execute();
// Commit the transaction
conn.commit();
} catch (SQLException e) {
// if something wrong happens, rolling back
if(conn != null) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (ptmt != null) ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//代码效果参考：http://www.zidongmutanji.com/zsjx/147299.html
若在第一个ptmt.execute()时断点，并查询数据库，结果为事务执行之前的状态，并不是中间状态。
直到conn.commit()方法执行完毕，事务中的所有操作在数据库中才有效。
Connection类中的检查点功能：
.setSavePoint()：在执行过程中创建保存点，以便rollback()可以回滚到该保存点
.rollback(SavePoint savePoint)：回滚到某个检查点
i.e.
复制代码
public static void rollbackTest() {
Connection conn = null;
PreparedStatement ptmt = null;
// save point
Savepoint sp = null;
try {
conn = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);
conn.setAutoCommit(false);
String sql = "UPDATE User SET Account = ? WHERE userName = ? AND id = ?;";
ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, 0);
ptmt.setString(2, "ZhangSi");
ptmt.setInt(3, 1);
ptmt.execute();
// create a save point
sp = conn.setSavepoint();
ptmt.setInt(1, 100);
ptmt.setString(2, "LiSan");
ptmt.setInt(3, 2);
ptmt.execute();
// throw an exception manually for the purpose of testing
throw new SQLException();
} catch (SQLException e) {
// if something wrong happens, rolling back to the save point created before
// and then transfer the money to Guoyi(3)
if(conn != null) {
try {
conn.rollback(sp);
System.out.println("Transfer from ZhangSi(1) to LiSan(2) failed;\n"
+ "Transfer to GuoYi(3) instead");
// other operations
ptmt.setInt(1, 100);
ptmt.setString(2, "GuoYi");
ptmt.setInt(3, 3);
ptmt.executeQuery();
conn.commit();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (ptmt != null) ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//代码效果参考：http://www.zidongmutanji.com/zsjx/197281.html
事务的隔离级别：4个级别
读未提交（read uncommited)：可能导致脏读
读提交（read commited)：不可能脏读，但是会出现不可重复读
重复读（repeatable read)：不会出现不可重复读，但是会出现幻读
串行化（serializable）：最高隔离级别，不会出现幻读，但严格的并发控制、串行执行导致数据库性能差
N.B. 1. 事务隔离级别越高，数据库性能越差，但对于开发者而言编程难度越低。
- MySQL默认事务隔离级别为重复读 repeatable read
JDBC设置隔离级别：
Connection对象中，
.getTransactionIsolation();
.setTransactionIsolation();
死锁分析与解决
上节讲到数据库的隔离性，开发者一般会使用加锁来保证隔离性，但会遇到死锁的问题。
场景：
数据库：
ID UserName Account Corp
1 ZhangSan 100 Ali
2 Lisi 0 Ali
事务1：张三给李四转账100元钱
事务2：张三和李四的单位改为Netease
事务持锁：
MySQL是以行加锁的方式来避免不同事务对同一行数据的修改
事务1对张三这行记录的修改要使用到对这一行的行锁。
事务2同时并发执行，事务2先修改李四的行记录的Corp，使用了对李四的行锁。
事务1想要更新李四记录，需要持有李四的行锁，但是事务2占据了李四的行锁，于是事务1等待事务2执行完成后对李四行锁的释放。
事务2想要更新张三记录，需要持有张三的行锁，但是事务1占据了张三的行锁，于是事务2等待事务1执行完成后对张三行锁的释放。
事务1和事务2相互等待，两个事务都无法继续进行。
-->死锁
死锁：
两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
死锁产生的必要条件：
互斥：并发执行的事务为了进行必要的隔离保证执行正确，在事务结束前，需要对修改的数据库记录持锁，保证多个事务对相同数据库记录串行修改。对于大型并发系统而言是无法避免的。
请求和保持：一个事务需要申请多个资源，并且已经持有一个资源，在等待另一个资源锁。死锁仅发生在请求两个或者两个以上的锁对象时。由于业务需要修改多行数据库记录，难以避免。
不剥夺：已经获得锁资源的事务，在未执行完成前，不能被强制剥夺，只能使用完时由事务自己释放。一般用于已经出现死锁时，通过破坏该条件达到解除死锁的目的--数据库系统通常通过一定的死锁检测机制发现死锁，强制回滚持有锁的代价相对较小的事务，让另外一个事务执行完毕，就能解除死锁的问题。
环路等待：发生死锁时，必然存在一个事务-锁的环形链，如事务1因为锁1等待事务2，事务2因为锁2等待事务1、等等。产生原因：每个事务获取锁的顺序不一致导致。解决方法：按照同一顺序获取锁，可以破坏该条件。通过分析死锁事务之间的锁竞争关系，调整SQL的顺序，达到消除死锁的目的。i.e. 若事务1和事务2刚开始都想获取锁1，就不会形成环路，就不会出现环路等待，不会出现死锁了。----按序获取锁资源：预防死锁。
MySQL中的锁：
排它锁 X：与其他任何锁都是冲突的
共享锁 S：多个事务可以共享一把锁。若事务1获取了共享锁，事务二还想获取共享锁，则不需等待（是兼容的）
欲加锁
已有锁
X S
X 冲突 冲突
S 冲突 兼容
加锁方式：
外部加锁：由应用程序执行特定sql语句进行显式添加，锁依赖关系较容易分析
共享锁（S)：select * from table lock in share mode;
排它锁（X)：select * from table for update;
内部加锁：
为了实现ACID特性，由数据库系统内部自动添加。
加锁规则繁琐，与SQL执行计划、事务隔离级别、表索引结构有关。
哪些SQL需要持有锁？
不需要：快照读：Innodb实现了多版本控制（MVCC），支持不加锁快照读。所有select语句不加锁，可以保证同一个select的结果集是一致的。但是不能保证同一个事物内部，select语句和其他语句的数据一致性，如果业务需要，需通过外部显式加锁。
需要：当前读：
加了外部锁的select语句
Update from table set ......
Insert into ......
Delete from table ......
SQL加锁分析：
i.e.
ID UserName Account Corp
1 ZhangSan 100 Ali
2 LiSi 0 Ali
Update user set account = 0 where id = 1;
update语句直接在ID=1行数据处加排它锁，此时若为select操作 (是快照读)，则不会被阻塞。
Select UserName from user where id = 1 in share mode;
该语句对行记录加了共享锁，此时若其他事务也对该行记录加共享锁，是不会阻塞的
分析死锁的常用办法：
MySQL数据库会自动分析死锁并回滚代价最小的事务处理死锁。
但是开发人员需要在死锁处理以后避免死锁再次发生。
show engine innodb status;
其中有发生死锁时相关的sql语句，也会列出被系统强制回滚的事务
分析死锁产生的原因，可以通过改变sql顺序等操作有效避免死锁再次产生。
事务单元测试
本次得分为：70.00/70.00, 本次测试的提交时间为：2017-08-25
1单选(5分)
事务的隔离性是指？
A.一个事务一旦提交成功，则事务对数据的改变将永久生效。
B.事务包含的所有操作，要么全部完成，要么全部不完成。
C.事务执行前和事务执行后，数据必须处于一致的状态。
D.一个事务内部的操作及使用的数据对并发的其他事务是隔离的。5.00/5.00
2单选(5分)
设有两个事务T1、T2，其并发操作如图所示，下面描述正确的是：
A.该操作读取“脏”数据。5.00/5.00
B.该操作存在更新丢失。
C.该操作不可重复读。
D.该操作保证ACID特性。
3单选(5分)
JDBC 实现事务控制，开启事务使用哪个方法？
A..setSavePoint()
B..commit()
C..setAutoCommit(false)5.00/5.00
D..rollback()
4单选(5分)
以下哪个事务隔离级别不存在脏读，但是存在不可重复读？
A.read uncommitted
B.repeatable read
C.read committed5.00/5.00
D.serializable
5单选(5分)
以下哪项不是死锁产生的必要条件？
A.单个事务。5.00/5.00
B.互斥。
C.不剥夺。
D.环路等待。
6单选(5分)
关于死锁描述不正确的是？
A.MySQL数据库会自动解除死锁，随机回滚一个事务，解除事务持有的锁资源。5.00/5.00
B.单个事务是不会发生死锁的。
C.Show engine innodb status 可以查看发生死锁的SQL语句。
D.死锁产生的根本原因是由于两个事务之间的加锁顺序问题。
7多选(40分)
以下描述正确的是？
A.为了预防死锁，在完成应用程序时，必须做到按序加锁，这主要是破坏死锁必要条件的不剥夺条件。
B.MySQL 数据库实现了多版本控制，支持快照读，读不加锁。20.00/40.00
C.在MySQL中存在共享锁和排他锁两种加锁模式，一个事务对某行记录加了共享锁，则另外一个事务无论是添加共享锁还是排他锁，都可以添加。
D.MySQL数据库实现了事务死锁检测和解决机制，数据库系统一旦发现死锁，会自动强制回滚代价最小的事务，解除死锁。
事务作业
事务的单元作业，包括一道编程题目。
1（100分）
有一个在线交易电商平台，有两张表，分别是库存表和订单表，如下：
现在买家XiaoMing在该平台购买bag一个，需要同时在库存表中对bag库存记录减一，同时在订单表中生成该订单的相关记录。
请编写Java程序，实现XiaoMing购买bag逻辑。订单表ID字段为自增字段，无需赋值。
答：
创建数据库：
复制代码
mysql> CREATE TABLE Inventory (
-> ID int auto_increment primary key,
-> ProductName varchar(20) not null,
-> Inventory int not null);
mysql> INSERT INTO Inventory VALUES (null, "watch", 25);
mysql> INSERT INTO Inventory VALUES (null, "bag", 20);
mysql> CREATE TABLE Orders (
-> Id int auto_increment primary key,
-> Buyer varchar(20) not null,
-> ProductName varchar(20) not null);
复制代码
业务逻辑：
复制代码
public static void purchase() throws ClassNotFoundException {
Connection conn = null;
PreparedStatement ptmt = null;
ResultSet rs = null;
String sql = "";
int currNumberofBags = -1;
String buyer = "XiaoMing";
String productToBuy = "bag";
Class.forName(DRIVER_NAME);
try {
conn = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);
conn.setAutoCommit(false);
// the number of bags in the inventory
sql = "SELECT Inventory FROM Inventory WHERE ProductName = ?";
ptmt = conn.prepareStatement(sql);
ptmt.setString(1, productToBuy);
rs = ptmt.executeQuery();
if (rs.next()) {
currNumberofBags = rs.getInt("Inventory");
}
if (currNumberofBags > 0) {
// Buy one bag
sql = "UPDATE Inventory SET Inventory = ? WHERE ProductName = ?";
ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, currNumberofBags-1);
ptmt.setString(2, productToBuy);
ptmt.execute();
sql = "INSERT INTO Orders VALUES (null, ?, ?);";
ptmt = conn.prepareStatement(sql);
ptmt.setString(1, buyer);
ptmt.setString(2, productToBuy);
ptmt.execute();
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
if (conn != null) conn.close();
if (ptmt != null) ptmt.close();
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) throws ClassNotFoundException {
purchase();
}
//代码效果参考：http://www.zidongmutanji.com/zsjx/11269.html
第一次执行后：
复制代码
mysql> select * from inventory;
+----+-------------+-----------+
| Id | ProductName | Inventory |
+----+-------------+-----------+
| 1 | watch | 25 |
| 2 | bag | 19 |
+----+-------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from orders;
+----+----------+-------------+
| Id | Buyer | ProductName |
+----+----------+-------------+
| 3 | XiaoMing | bag |
+----+----------+-------------+
1 row in set (0.00 sec)
复制代码
第二次执行后：
复制代码
mysql> select * from inventory;
+----+-------------+-----------+
| Id | ProductName | Inventory |
+----+-------------+-----------+
| 1 | watch | 25 |
| 2 | bag | 18 |
+----+-------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from orders;
+----+----------+-------------+
| Id | Buyer | ProductName |
+----+----------+-------------+
| 3 | XiaoMing | bag |
| 4 | XiaoMing | bag |
+----+----------+-------------+
2 rows in set (0.00 sec)
