数据库事务、隔离级别及其应用

                                                               数据库事务、隔离级别及其应用

     一、数据库事务

           事务(Transaction):是并发控制的单元，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，sql server 能将逻辑相关的一组操作绑定在一起，以便服务器 保持数据的完整性。事务通常是以begin transaction开始，以commit或rollback结束。Commint表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据的更新写回到磁盘上的物理数据库中去，事务正常结束。Rollback表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有已完成的操作全部撤消，滚回到事务开始的状态。

        1.事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
            1)原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
            2)一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
            3)隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
            4)持久性（durability）。指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

       2.事务并发处理可能引起的问题
           1)脏读(dirty read) 一个事务读取了另一个事务尚未提交的数据。事务A、B并发执行时，当A事务update后，B事务select读取到A尚未提交的数据，此时A事务rollback，则B读到的数据是无效的"脏"数据。
           2)不可重复读(non-repeatable read) 一个事务的操作导致另一个事务前后两次读取到不同的数据。事务A、B并发执行时，当B事务select读取数据后，A事务update操作更改B事务select到的数据，此时B事务再次读去该数据，发现前后两次的数据不一样。
           3)幻读(phantom read) 一个事务的操作导致另一个事务前后两次查询的结果数据量不同。事务A、B并发执行时，当B事务select读取数据后，A事务insert或delete了一条满足A事务的select条件的记录，此时B事务再次select，发现查询到前次不存在的记录("幻影")，或者前次的某个记录不见了。

二、事务隔离级别

      JDBC定义了五种事务隔离级别：分别是:

        1.Connection.TRANSACTION_NONE,JDBC驱动不支持事务       

   /**     * A constant indicating that transactions are not supported.     */    int TRANSACTION_NONE             = 0;

       2.Connection.TRANSACTION_READ_UNCOMMITTED,允许脏读、不可重复读和幻读。

  /**     * A constant indicating that     * dirty reads, non-repeatable reads and phantom reads can occur.     * This level allows a row changed by one transaction to be read     * by another transaction before any changes in that row have been     * committed (a "dirty read").  If any of the changes are rolled back,     * the second transaction will have retrieved an invalid row.     */    int TRANSACTION_READ_UNCOMMITTED = 1;

       读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

      事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

     分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

     那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

     3.Connection.TRANSACTION_READ_COMMITTED,禁止脏读，但允许不可重复读和幻读。

  /**     * A constant indicating that     * dirty reads are prevented; non-repeatable reads and phantom     * reads can occur.  This level only prohibits a transaction     * from reading a row with uncommitted changes in it.     */    int TRANSACTION_READ_COMMITTED   = 2;

      读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

    事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

    分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

    那怎么解决可能的不可重复读问题？Repeatable read ！

    4.Connection.TRANSACTION_REPEATABLE_READ,禁止脏读和不可重复读，但允许幻读。

   /**     * A constant indicating that     * dirty reads and non-repeatable reads are prevented; phantom     * reads can occur.  This level prohibits a transaction from     * reading a row with uncommitted changes in it, and it also     * prohibits the situation where one transaction reads a row,     * a second transaction alters the row, and the first transaction     * rereads the row, getting different values the second time     * (a "non-repeatable read").     */    int TRANSACTION_REPEATABLE_READ  = 4;

       重复读，就是在开始读取数据（事务开启）时，不再允许修改操作      

      事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

     分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作或DELETE操作，而不是UPDATE操作。

      什么是幻读?事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

    5.Connection.TRANSACTION_SERIALIZABLE,禁止脏读、不可重复读和幻读。       

   /**     * A constant indicating that     * dirty reads, non-repeatable reads and phantom reads are prevented.     * This level includes the prohibitions in     * <code>TRANSACTION_REPEATABLE_READ</code> and further prohibits the     * situation where one transaction reads all rows that satisfy     * a <code>WHERE</code> condition, a second transaction inserts a row that     * satisfies that <code>WHERE</code> condition, and the first transaction     * rereads for the same condition, retrieving the additional     * "phantom" row in the second read.     */    int TRANSACTION_SERIALIZABLE     = 8;

  Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

     三、应用

       现在有一个需求:并发条件下注册员工信息

       其中，员工信息包括：员工id、员工姓名name、员工工资salary(前提是：员工的姓名name是唯一的，即员工不重名)

      要求：并发情况下注册员工时要避免重复insert

      思路： 首先想到的是员工姓名name字段加unique唯一性索引，但是由于员工有离职操作，如果员工离职后再入职，要作为一个新员工来处理，而且有可能员工入职、离职重复多次，这样就不能以员工姓名name+员工状态（在职或离职）两个字段作为唯一性索引

      最终解决方案:采用数据库事务的TRANSACTION_SERIALIZABLE串行化隔离级别来实现

     1.数据库表结构

      

  2.代码（多线程模拟并发操作，注册员工接口时采用事务）

           

import com.alibaba.fastjson.JSON;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Connection;import java.sql.Statement;import java.util.concurrent.ExecutorService;import java.util.concurrent.Executors;/** * Created on 2017/11/8. */public class TestController {    public static void main(String args[]){        ExecutorService pool = Executors.newCachedThreadPool();        TestThread thread1 = new TestThread();        thread1.setName("张三");        thread1.setSalary(1000l);        pool.execute(thread1);        TestThread thread2 = new TestThread();        thread2.setName("张三");        thread2.setSalary(1000l);        pool.execute(thread2);        TestThread thread3 = new TestThread();        thread3.setName("李四");        thread3.setSalary(2000l);        pool.execute(thread3);        TestThread thread5 = new TestThread();        thread5.setName("李四");        thread5.setSalary(2000l);        pool.execute(thread5);        TestThread thread4 = new TestThread();        thread4.setName("王五");        thread4.setSalary(3000l);        pool.execute(thread4);    }    public static void insertEmployee(String nameParam,long salaryParam){        Connection conn = null;        String sql;        // MySQL的JDBC URL编写方式：jdbc:mysql://主机名称：连接端口/数据库的名称?参数=值        String url = "jdbc:mysql://localhost:3306/employee?"                + "user=root&password=love&useUnicode=true&characterEncoding=UTF8";        for (int i = 0; i < 3; i++) {//重试三次            try {                // 之所以要使用下面这条语句，是因为要使用MySQL的驱动，所以我们要把它驱动起来，                // 可以通过Class.forName把它加载进去，也可以通过初始化来驱动起来，下面三种形式都可以                Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动                // or:                // com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();                // or：                // new com.mysql.jdbc.Driver();                // 一个Connection代表一个数据库连接                conn = DriverManager.getConnection(url);                conn.setAutoCommit(false);//设置自动提交为false                conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);//设置串行化隔离级别                // Statement里面带有很多方法，比如executeUpdate可以实现插入，更新和删除等                Statement stmt = conn.createStatement();                sql = "select * from employee where name = '"+nameParam+"'";                ResultSet rs = stmt.executeQuery(sql);// executeQuery会返回结果的集合，否则返回空值                System.out.println("查询是否有name="+nameParam+"员工;结果是rs.next()="+ JSON.toJSONString(rs.next()+";rs.first="+rs.first()));                if(rs != null && rs.first()){                    System.out.println("已经有name="+nameParam+"员工");                }else{                    sql = "insert into employee(name,salary)  values('"+nameParam+"',"+salaryParam+")";                    int  result = stmt.executeUpdate(sql);                    System.out.println("新增员工结果result="+result+";nameParam="+nameParam);                }                rs.close();                conn.commit();//提交事务                conn.setAutoCommit(true);//设置自动提交为true                conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);//设置隔离级别为读提交                break;            } catch (Exception e) {                if(i == 2){                    System.out.println("MySQL操作错误,e=" + e.getMessage());                }                try {                    conn.rollback(); //回滚事务                    conn.setAutoCommit(true);//设置自动提交为true                    conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);//设置隔离级别为读提交                    System.out.println("回滚结束");                } catch (Exception e1) {                    System.out.println("MySQL回滚操作出现异常,e1="+e1.getMessage());                }            } finally {                try {                    conn.close();                } catch (Exception e2) {                    System.out.println("MySQL关闭数据库连接出现异常,e2="+e2.getMessage());                }            }            try {                Thread.sleep(20);            } catch (InterruptedException e2) {                Thread.currentThread().interrupt();            }        }    }}class TestThread implements Runnable{    public String name;    public long salary;    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public long getSalary() {        return salary;    }    public void setSalary(long salary) {        this.salary = salary;    }    public void run() {        TestController.insertEmployee(getName(),getSalary());    }}

 代码解释:由于开始了串行化事务，所以注册时有可能由于死锁而失败，这样就需要重试，重试的机制是:每隔20ms，重试2次

运行结果：