SQL学习十七、事务处理

事务处理

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么 完全执行，要么完全不执行，来维护数据库的完整性。

事务处理是一种机制， 用来管理必须成批执行的 SQL操作，保证数据库不包含不完整的操作结果。
利用事务处理，可以保证一组操作不会中途停止，它们要么完全执 行，要么完全不执行（除非明确指示）。
如果没有错误发生，整组语句提 交给（写到）数据库表；
如果发生错误，则进行回退（撤销），将数据库 恢复到某个已知且安全的状态。

• 相关概念
  1、**事务（transaction）**指一组 SQL语句；
  2、**回退（rollback）**指撤销指定 SQL语句的过程；
  3、**提交（commit）**指将未存储的 SQL语句结果写入数据库表；
  4、**保留点（savepoint）**指事务处理中设置的临时占位符（placeholder）， 可以对它发布回退（与回退整个事务处理不同）。

5、**隐式提交（implicit commit）**一般的 SQL语句都是针对数据库表直接执行和编写的，即提交（写或保存）操作是自动进行的。

• 可以回退的语句
  事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。

---

实际操作

比如，我们在新的订单表（oderlist_new）中新增订单记录，过程如下：

1、检查用户表（user）中是否有对应的用户，如果不存在就添加
2、检查供应商表（supplier_new）中是否有对应的供应商，如果不存在就添加
3、在订单表（oderlist_new）中添加一条记录和用户id、用户名、供应商id关联

如果在上述存储的过程汇总，出现某种数据库故障（如超出磁盘空间、安全限制、表锁等）， 造成这个过程无法完成。那么数据库中的数据会出现什么情况？
如果在存储订单信息的时候出现的故障，就会出现不完整的订单信息，比如没有对应的用户或者供应商；
如果出现在2、3之间，就会有供应商没有供应商品（在某些业务中是合理的，某些业务中是不合理的）。

这时我们就要用到事务了。

---

管理事务

管理事务的关键在于将 SQL语句组分解为逻辑块，并明确规定数据何时 应该回退，何时不应该回退。

• 1、开启事务

SQL Server 中使用BEGIN TRANSACTION
Oracle中使用SET TRANSACTION
MariaDB和 MySQL中使用START TRANSACTION

• 2、撤销操作

ROLLBACK 命令用来回退（撤销）SQL语句，使用

DELETE FROM orderlist; 
ROLLBACK; 
1
2

• 3、事务提交

SQL Server 中使用 COMMIT TRANSACTION
Oracle 中使用COMMIT;

• 4、使用保留点

我们回滚的时候可以全部回滚也可以部分回滚，要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这 样，如果需要回退，可以回退到某个占位符。

在 SQL中，这些占位符称为保留点，保留点的名字可以随便取，但不能重复。

在 MariaDB、MySQL和 Oracle中 创建占位符，可使用 SAVEPOINT 语句设置保留点，如 SAVEPOINT delete1; ，可以通过ROLLBACK TO delete1;回滚到对应的保留点；

在 SQL Server中，需要使用SAVE TRANSACTION 语句设置保留点，如SAVE TRANSACTION delete1;，可以通过ROLLBACK TRANSACTION delete1;回滚到对应的保留点；

---

在DBMS中执行事务

比如，我们现在有这样一条订单信息需要入库，按照上面从操作步骤我们可以这样写：

"菠萝"	
"14"	
"10.0"	
"20181023001"	
"30"	
"王舍"	
"2018-10-23 10:12:49.000"	
"杭州水果批发总公司"	
"文一西路275号"	
"15102725297"	
"fruithangzhou@777.com"	
"郑凯"
1
2
3
4
5
6
7
8
9
10
11
12

BEGIN TRANSACTION;
insert into user (userId,userName,`password`,loginName) values (30,'王舍','ws1234','wangshe');

insert into supplier_new (supplier,supplierAddress,supplierTel,supplierEmail,supplierContact) values ('杭州水果批发总公司','文一西路275号','15102725297','fruithangzhou@777.com','郑凯');

insert into oderlist_new
(goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)
values ('菠萝',14,10.0,'20181023001',30,'王舍','2018-10-23 10:12:49.000',
(select id from supplier_new where `supplier` = '杭州水果批发总公司'));

END TRANSACTION;
1
2
3
4
5
6
7
8
9
10
11

---

在Android中执行事务

同样是上述数据，加入到订单库中，我们按照【实际操作】中提到的过程判断在代码中实现：

private void initData() {
        File test = new File(Environment.getExternalStorageDirectory(), "DBTest");
        if (!test.exists()) {
            test.mkdirs();
        }
        String dbTest = String.format("%s/%s", test.getPath(), "task.db");
        SQLiteOpenHelper helper = new MySQLiteOpenHelper(this, dbTest, null, 3);
        SQLiteDatabase db = helper.getWritableDatabase();
        //开启事务
        db.beginTransaction();
        try {
            String[] userId = {"30"};
            String sql0 = "select * from user where userId=?";
            Cursor cursor0 = db.rawQuery(sql0, userId);
            if (!cursor0.moveToFirst()) {
                String sql1 = "insert into user (userId,userName,`password`,loginName) values (?,?,?,?)";
                db.execSQL(sql1, new String[]{"39", "王舍例", "wsl234", "wangsheli"});
            }
            String[] supplier = {"杭州有机蔬菜专供经销商"};
            String sql2 = "select * from supplier_new where supplier=?";
            Cursor cursor2 = db.rawQuery(sql2, supplier);
            if (!cursor2.moveToFirst()) {
                String sql3 = "insert into supplier_new (supplier,supplierAddress,supplierTel,supplierEmail,supplierContact) values (?,?,?,?,?)";
                db.execSQL(sql3, new String[]{"杭州有机蔬菜专供经销商", "文一西路225号", "15568432549", "organicVegetableHangZhou@999.com", "李刚"});
            }
            String sql4 = "insert into oderlist_new\n" +
                    "(goodsName,quantity,item_price,orderNo,userId,userName,orderTime,supplierId)\n" +
                    "values (?,?,?,?,?,?,?,\n" +
                    "(select id from supplier_new where supplier = ?))";
            db.execSQL(sql4, new String[]{"菠萝", "14", "10.0", "20181023001", "30", "王舍", "2018-10-23 10:12:49", "杭州有机蔬菜专供经销商"});
            db.setTransactionSuccessful();//设置事务的标志为True
        } finally {
            //结束事务,有两种情况：commit（事务的标志为True）,rollback（事务的标志为False）
            db.endTransaction();
        }
    }

    /**
     * 1、实际项目中很少使用SQLiteDatabase的方法来打开数据库
     * 2、一般都是继承SQLiteOpenHelper类，来管理SQLiteDatabase
     * 3、通过SQLiteOpenHelper来获取SQLiteDatabase实例来进行相关数据库操作
     */
    private class MySQLiteOpenHelper extends SQLiteOpenHelper {

        public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            //初次生成数据库时的回调
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            //数据库版本发生改变时的回调

        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

• 用户表中没有【王舍例，userId = 39】，所以插入
  

• 供应商表中有【杭州有机蔬菜专供经销商】，所以不用插入
  

• 插入的订单信息
  

上述数据库操作，在出现异常的时候，会回滚到初始状态，即不改变数据库中的数据。我们可以在上传操作中手动加入一个异常，比如类型强转、角标越界等异常进行验证

我们可以看到在异常产生前的插入的用户数据也回滚了

---

笔记

1、在Android中使用事务

使用SQLiteDatabase的beginTransaction()方法可以开启一个事务，程序执行到endTransaction() 方法时会检查事务的标志是否为成功，如果为成功则提交事务，否则回滚事务。
当应用需要提交事务，必须在程序执行到endTransaction()方法之前使用setTransactionSuccessful() 方法设置事务的标志为成功，如果不调用setTransactionSuccessful() 方法，默认会回滚事务。