mysql事务四要素即事务的隔离级别分析

最新推荐文章于 2021-02-17 17:01:34 发布

湘梓521

最新推荐文章于 2021-02-17 17:01:34 发布

阅读量468

点赞数 2

分类专栏：数据库 mysql 文章标签： mysql ACID 隔离级别

数据库同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

在jdbc的使用中以最简单的jdbc的使用为例，说明了jdbc的具体用法。然而在通常项目中，需要考虑更多内容，例如事务。
事务，在单个数据处理单元中，存在若干个数据处理，要么整体成功，要么整体失败。事务需要满足ACID属性（原子性、一致性、隔离性和持久性）。

原子性：所谓原子性是指本次数据处理要么都提交、要么都不提交，即不能先提交一部分，然后处理其他的程序，然后接着提交未完成提交的剩余部分。概念类似于编程语言的原子操作。
一致性：所谓一致性是指数据库数据由一个一致的状态在提交事务后变为另外一个一致的状态。例如，用户确认到货操作：确认前，订单状态为待签收、客户积分为原始积分，此状态为一致的状态；在客户确认到后后，订单状态为已完成、客户积分增加本次消费的积分，这两个状态为一致状态。不能出现，订单状态为待签收，客户积分增加或者订单状态为已完成，客户积分未增加的状态，这两种均为不一致的情况。一致性与原子性息息相关。
隔离性：所谓隔离性是指事物与事务之间的隔离，即在事务提交完成前，其他事务与未完成事务的数据中间状态访问权限，具体可通过设置隔离级别来控制。
持久性：所谓持久性是指本次事务提交完成或者回滚完成均为持久的修改，除非其他事务进行操作否则数据库数据不能发生改变。

本文重点描述事物隔离性及使用方法。
要详细说明数据库隔离级别，需要先对数据库并发事务可能出现的几种状态进行说明：

读脏：一个事务读取另外一个事务尚未提交的数据。如下图，线程thread1在事务中在time1时刻向库表中新增一条数据‘test’并在time3时刻回滚数据；线程thread2在time2时刻读取，若thread2读取到‘test’，则为读脏。

不可重新读：其他事务的操作导致某个事务两次读取数据不一致。如下图，线程thread1在事务中time1时刻将数据库中‘test’更新为‘00’，并在time3时刻提交；thread2在一个事务中分别在time2和time4两个时刻读取这条记录，若两次读取结果不同则为不可重读。（注意：1.不可重读针对已经提交的数据。2.两次或多次读取同一条数据。）

幻读：其他事务的数据操作导致某个事务两次读取数据数量不一致。如下图，线程thread1在事务中time1时刻向数据库中新增‘00’，并在time3时刻提交；thread2在一个事务中分别在time2和time4两个时刻扫描库表，若两次读取结果不同则为幻读。（注意：1.幻读针对已经提交的数据。2.两次或多次读取不同行数据，数量上新增或减少。）

针对上诉3中事务并发情况，jdbc定义了5中事务隔离级别：
- TRANSACTION_NONE 无事务
- TRANSACTION_READ_UNCOMMITTED 允许读脏，不可重读，幻读。
- TRANSACTION_READ_COMMITTED 直译为仅允许读取已提交的数据，即不能读脏，但是可能发生不可重读和幻读。
- TRANSACTION_REPEATABLE_READ 不可读脏，保证同一事务重复读取相同数据，但是可能发生幻读。
- TRANSACTION_SERIALIZABLE 直译为串行事务，保证不读脏，可重复读，不可幻读，事务隔离级别最高。

**> 注意：

隔离级别对当前事务有效，例如若当前事务设置为TRANSACTION_READ_UNCOMMITTED，则允许当前事务对其他事务未提交的数据进行读脏，而非其他事务可对当前事务未提交的数据读脏。
部分数据库不支持TRANSACTION_NONE，例如mysql。
在TRANSACTION_SERIALIZABLE 隔离级别下，为先执行DML更新，再执行查询，此处为实验的结论。
若未显示设置隔离级别，jdbc将采用数据库默认隔离级别。文中实验数据库的默认隔离级别为：**

以下将分别在各种事务隔离级别下，通过设置事务内访问间隔时间，模拟读脏、不可重读、幻读。
建立库表脚本如下：

CREATE TABLE `t_dict` (
  `dict_type` varchar(255) DEFAULT NULL,
  `dict_code` varchar(255) DEFAULT NULL,
  `dict_name` varchar(255) DEFAULT NULL,
  `dict_remark` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 
 1
2
3
4
5
6
7

主线程，用于建立数据库连接、设置隔离级别、打印输出等

package DBTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBTest {
    private String url ;
    private String user;
    private String password;

    /**
     * 创建数据连接
     * @return
     */
    private Connection getCon(){
        Connection con = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            url = "jdbc:mysql://localhost:3306/twork";
            user = "root";
            password = "root";
            con = DriverManager.getConnection(url, user, password);
        }catch (Exception e){
            e.printStackTrace();
            try {
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return con;
    }

    /**
     * 通过链接获取声明
     * @param con
     * @return
     */
    private Statement getStat(Connection con){
        Statement state = null;
        try{
            state = con.createStatement();
        }catch(Exception e){
            e.printStackTrace();
        }
        return state;
    }

    /**
     *  打印数据库所有数据
     */
    public void selectAll(int transactionType){
        Connection con = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            con = getCon();
            if(transactionType >= 0 ){
                 con.setTransactionIsolation(transactionType);
            }
            System.out.println("-------------当前事务隔离级别为："+con.getTransactionIsolation()+"-------------");
            state = getStat(con);
            rs = state.executeQuery("select * from t_dict");
            ResultSetMetaData rsmd = rs.getMetaData();
            for(int i = 1;i<= rsmd.getColumnCount() ;i++){
                System.out.print(rsmd.getColumnName(i)+"| ");
            }
            System.out.println();
            System.out.println("-------------------------------------------");
            //打印所有行
            while(rs.next()){
                for(int i = 1;i<= rsmd.getColumnCount() ;i++){
                    System.out.print(rs.getString(i)+"|     ");
                }
                System.out.println();
            }
        }
        catch (Exception e){
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        finally {
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(state != null){
                    state.close();
                }
            } catch (Exception e){

            }
            try {
                if(con != null){
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 新增一行
     * @param needExcepition
     * @param sleepTimes
     * @param values
     * @return
     */
    public int insertOne(int needExcepition,int sleepTimes, List<String> values){
        Connection con = getCon();
        PreparedStatement pre = null;
        String sql = "INSERT INTO t_dict (dict_type, dict_code, dict_name, dict_remark) VALUES (?, ?, ?, ?)";
        int res = 0;
        try {
            con.setAutoCommit(false);
            pre = con.prepareStatement(sql);
            for(int i = 0; i < values.size() ;i++){
                pre.setString(i+1, values.get(i));
            }
            Thread.sleep(sleepTimes);
            System.out.println("before execute");
            res = pre.executeUpdate();
            System.out.println("after execute");
            Thread.sleep(sleepTimes);
            int i = 1/needExcepition;
            System.out.println("before commit");
            con.commit();
            System.out.println("after commit");
        } catch (Exception e) {
            try {
                System.out.println("before roll back");
                con.rollback();
                System.out.println("after roll back");
                res = 0;
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if(pre != null){
                    pre.close();
                }
            } catch (Exception e){

            }
            try {
                if(con != null){
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

    /**
     * 间隔一定时间读取多次
     * @param dictType 要去读取的数据类型
     * @param sleepTimes 每次读取之间的间隔时间
     * @param printTimes 打印次数
     * @param transactionType 事务隔离级别
     */
    private void printMultiple(String dictType,int sleepTimes,int printTimes, int transactionType){
        Connection con = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            con = getCon();
            con.setAutoCommit(false);
            if(transactionType >= 0){
                con.setTransactionIsolation(transactionType);
            }
            System.out.println("-------------当前事务隔离级别为："+con.getTransactionIsolation()+"-------------");
            state = getStat(con);
            for (int j = 0; j < printTimes; j++) {
                Thread.sleep(sleepTimes);
                rs = state.executeQuery("select * from t_dict where dict_type = '"+dictType+"' ");
                ResultSetMetaData rsmd = rs.getMetaData();
                System.out.println("第"+(j+1)+"次读取");
                for(int i = 1;i<= rsmd.getColumnCount() ;i++){
                    System.out.print(rsmd.getColumnName(i)+"| ");
                }
                System.out.println();
                System.out.println("-------------------------------------------");
                while(rs.next()){
                    for(int i = 1;i<= rsmd.getColumnCount() ;i++){
                        System.out.print(rs.getString(i)+"|     ");
                    }
                    System.out.println();
                }   
            }
            con.commit();
        }
        catch (Exception e){
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        finally {
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(state != null){
                    state.close();
                }
            } catch (Exception e){

            }
            try {
                if(con != null){
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 更改一条数据的内容
     * @param dict_type
     * @param sleepTimes
     * @param values
     * @return
     */
    public int updateOne(String dict_type,int sleepTimes, List<String> values){
        Connection con = null;
        PreparedStatement pre = null;
        String sql = "UPDATE t_dict  SET dict_code = ?, dict_name = ?, dict_remark = ? WHERE dict_type ='"+dict_type+"'";
        int res = 0;
        try {
            con = getCon();
            con.setAutoCommit(false);
            con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            pre = con.prepareStatement(sql);
            for(int i = 0; i < values.size() ;i++){
                pre.setString(i+1, values.get(i));
            }
            Thread.sleep(sleepTimes);
            System.out.println("before execute ");
            res = pre.executeUpdate();
            System.out.println("after execute ");
            Thread.sleep(sleepTimes);
            System.out.println("before commit");
            con.commit();
            System.out.println("after commit");
        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if(pre != null){
                    pre.close();
                }
            } catch (Exception e){

            }
            try {
                if(con != null){
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

    /**
     * @param transType
     */
    public void testTransaction(int transType){
        intDate();
        System.out.println("-------------------读脏模拟---------------------");
        testDirty(transType);
        try {
            Thread.sleep(2000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("-------------------不可重读模拟------------------");
        testRepeat(transType);
        try {
            Thread.sleep(3000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("-------------------幻读模拟----------------------");
        testTrick(transType);
    }

    /**
     * 初始化数据
     */
    private void intDate(){
        System.out.println("------------初始化数据 start-------------");
        Connection con = getCon();
        Statement pre = null;
        String sqlDelete = "delete from t_dict";
        String sqlInsert = "INSERT INTO `twork`.`t_dict` (`dict_type`, `dict_code`, `dict_name`, `dict_remark`) VALUES ('type0', '00', 'type00', 'type00')";
        try {
            con.setAutoCommit(false);
            pre = con.createStatement();
            pre.execute(sqlDelete);
            pre.execute(sqlInsert);
            con.commit();
        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if(pre != null){
                    pre.close();
                }
            } catch (Exception e){
                e.printStackTrace();
            }
            try {
                if(con != null){
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("------------初始化数据 end-------------");
    }
    /**
     * 模拟读脏，抛出未捕获异常，插入数据不提交
     */
    public void testDirty(int transactionType){
        List<String> list = new ArrayList<String>();
        list.add("type1");
        list.add("11");
        list.add("type11");
        list.add("type11");
        TestThread testThread = new TestThread("insert",0,300,list);
        Thread thread = new Thread(testThread);
        thread.start();
        try {
            Thread.sleep(500);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        selectAll(transactionType);
    }

    /**
     * 模拟幻读，第N次读取多出数据
     */
    public void testTrick(int transactionType){
        List<String> list = new ArrayList<String>();
        list.add("type0");
        list.add("11");
        list.add("type11");
        list.add("type11");
        //执行插入，不产生异常
        TestThread testThread = new TestThread("insert",1,400,list);
        Thread thread = new Thread(testThread);
        thread.start();
        //打印两次
        printMultiple("type0", 300,4,transactionType);
    }

    /**
     * 模拟不可重读，多次读取同一条记录，记录被更改
     */
    public void testRepeat(int transactionType){
        List<String> list = new ArrayList<String>();
        list.add("type0");
        list.add("11");
        list.add("type11");
        list.add("type11");
        //执行插入，不产生异常
        TestThread testThread = new TestThread("update",1,400,list);
        Thread thread = new Thread(testThread);
        thread.start();
        //打印4次
        printMultiple("type0", 300,4,transactionType);
    }

    public static void main(String[] args){
        DBTest dbTest = new DBTest();
        /*分别执行下面的方法，即可模拟各个隔离级别下，线程并发事务间的访问结果*/
        System.out.println(" -----------------------TRANSACTION_READ_UNCOMMITTED test start------------------------");
        dbTest.testTransaction(Connection.TRANSACTION_READ_UNCOMMITTED);

//      System.out.println(" -----------------------TRANSACTION_READ_COMMITTED test start------------------------");
//      dbTest.testTransaction(Connection.TRANSACTION_READ_COMMITTED);
//      
//      System.out.println(" -----------------------TRANSACTION_REPEATABLE_READ test start------------------------");
//      dbTest.testTransaction(Connection.TRANSACTION_REPEATABLE_READ);
//      
//      System.out.println(" -----------------------TRANSACTION_SERIALIZABLE test start------------------------");
//      dbTest.testTransaction(Connection.TRANSACTION_SERIALIZABLE);
//      
//      System.out.println(" -----------------------default test start------------------------");
//      dbTest.testTransaction(-1);
    }

}    

 
 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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436

并发线程，调用读取方法

package DBTest;

import java.util.ArrayList;
import java.util.List;

/** 
* Created by ygl on 2016/5/1. 
*/ 
public class TestThread implements Runnable {
    int needException = 1;

    int sleepTimes = 0;

    List<String> list = new ArrayList<String>();

    String method = "";

    DBTest dbTest = new DBTest();
    /**
     * @param method insert 或 update
     * @param needException 是否需要抛出异常，0抛出异常，1不抛出异常
     * @param sleepTimes 线程睡眠时间（毫秒）
     * @param list 更新数据库的数据，当method为update时，list的第一个元素为条件，其他为更新内容
     */
    public TestThread(String method,int needException, int sleepTimes , List<String> list){
        this.needException = needException;
        this.sleepTimes = sleepTimes;
        this.list = list;
        this.method = method;
    }

    public void run(){
        if("insert".equals(method)){
            insert();
        } else if("update".equals(method)){
            update();
        }
    }

    private void insert(){
        int res = dbTest.insertOne(needException, sleepTimes, list);
        if(res == 1){
            System.out.println("insert success");
        }else{
            System.out.println("insert fail");
        }
    }

    private void update(){
        String updateKey = list.get(0);
        list.remove(0);
        int res = dbTest.updateOne(updateKey, sleepTimes, list);
        if(res == 1){
            System.out.println("update success");
        }else{
            System.out.println("update fail");
        }
    }
}

 
 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
60

读者可以使用上述程序分别测试，这里仅以TRANSACTION_READ_UNCOMMITTED为例，输出结果为：

 -----------------------TRANSACTION_READ_UNCOMMITTED test start------------------------
------------初始化数据 start-------------

------------初始化数据 end-------------
-------------------读脏模拟---------------------

before execute
after execute

-------------当前事务隔离级别为：1-------------
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     00|     type00|     type00|     
type1|     11|     type11|     type11|     
before roll back
after roll back
java.lang.ArithmeticException: / by zero
    at DBTest.DBTest.insertOne(DBTest.java:141)
    at DBTest.TestThread.insert(TestThread.java:41)
    at DBTest.TestThread.run(TestThread.java:34)
    at java.lang.Thread.run(Unknown Source)
insert fail
-------------------不可重读模拟------------------

-------------当前事务隔离级别为：1-------------
第1次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     00|     type00|     type00|     
before execute 
after execute 
第2次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
before commit
after commit
update success
第3次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
第4次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
-------------------幻读模拟----------------------

-------------当前事务隔离级别为：1-------------
第1次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
before execute
after execute
第2次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
type0|     11|     type11|     type11|     
before commit
第3次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
type0|     11|     type11|     type11|     
after commit
insert success
第4次读取
dict_type| dict_code| dict_name| dict_remark| 
-------------------------------------------
type0|     11|     type11|     type11|     
type0|     11|     type11|     type11|