MySQL 中的事務詳解

東雎發表於2019-05-02
MySql

什麼是事務

  • 是以一種可靠、一致的方式，訪問和運算元據庫中資料的程式單元

原則

  • 原子性：一個事務要麼全部成功，要麼全部失敗
  • 一致性：事務完成以後，狀態改變是一致的，一致性一般是通過結果來呈現的
  • 隔離性：在不同事務試圖去操作同一份資料的時候，事務之間的隔離性
  • 永續性：資料提交以後，事務操作的結果才會永久儲存到資料庫當中

範例 ：a給b轉賬100元
一致性解讀：不會出現a賬戶－100而b賬戶沒有增加100的情況，整體的狀態是一致的，總的狀態不會平白無故地改變
隔離性解讀：在a給b轉賬的過程中，b進行查餘額的操作，那麼b得到到結果將由資料庫設定得資料庫隔離級別來決定。

MySQL 中的事務詳解

使用sql進行資料管理

事務1:

START TRANSACTION;
UPDATE user_transaction set amount = amount -100 WHERE username = 'user1';
UPDATE user_transaction set amount = amount + 100 WHERE `username` = 'user2';
COMMIT
複製程式碼

事務2

SELECT *  from user_transaction; 
複製程式碼

事務3

START TRANSACTION;
SELECT *  from user_transaction; 
SELECT * FROM user_transaction WHERE username = 'user1';
COMMIT
複製程式碼

結果展示

  1. 在不修改資料庫預設隔離級別的情況下，只有當執行完commit之後，其它事務才能查詢到資料庫的資料庫的更新。
  2. 可重複讀的展示（可重複讀:一個事務中讀取到的資料和事務開啟的時刻是一致的） 事務3開啟事務以後，開啟事務1並執行事務1的第一條更新語句執行第一條查詢語句,得到結果如下
    MySQL 中的事務詳解
    執行事務1的第二條更新語句並提交事務,然後執行事務3的第2條查詢語句
    MySQL 中的事務詳解
    我們可以看到在可重讀的隔離級別下，一個事務內多次讀取的資料結果和事務開始時的結果是一致的，哪怕其它事務已經對原有資料進行更新並提交。

查詢資料庫中的設定的隔離級別，可以發現mysql預設的資料庫隔離級別是可重讀

select @@GLOBAL.tx_isolation,@@tx_isolation;
複製程式碼

MySQL 中的事務詳解

修改資料庫當前事務隔離級別為髒讀

set session transaction isolation level read uncommited
複製程式碼

結果

  1. 事務1開啟事務並執行第一條更新語句，事務2可以看到事務1中執行的更新的資料，即使事務並沒有提交，即可以讀取到髒資料。

mysql資料庫的四種隔離級別

  • 讀未提交
  • 讀提交
  • 可重讀
  • 序列讀

jdbc操作事務

事務1:開啟事務->兩次更新->提交事務

public class JdbcTransaction {

    public static void main(String args[]) throws SQLException {

        Connection connection = getConn();

        //關閉自動提交，相當於開啟一個事務
        connection.setAutoCommit(false);

        //減少賬戶餘額
        String sql1 = "UPDATE user_transaction set amount = amount -100 WHERE username = ? ";
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.setString(1, "user1");
        ps1.executeUpdate();

        //若丟擲異常，事務會回滾
        //throwException();

        //增加賬號餘額
        String sql2 = "UPDATE user_transaction set amount = amount + 100 WHERE `username` = ?";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.setString(1, "user2");
        ps2.executeUpdate();

        // 提交事務
        connection.commit();
        ps1.close();
        ps2.close();
    }

    private static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "root";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,載入對應驅動
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    private static void throwException() throws SQLException {
        throw new SQLException();
    }
複製程式碼

事務2: 開啟事務->進行查詢操作->根據查詢的結果再進行更新->提交

public class JdbcTransaction2 {

    public  static  void main(String args[]) throws SQLException {

        Connection connection = getConn();

//        關閉自動提交，相當於開啟一個事務
        connection.setAutoCommit(false);

//        減少賬戶餘額,加悲觀鎖
        String query1 = "select * from user_transaction for update";
        PreparedStatement ps1 = connection.prepareStatement(query1);
        ResultSet resultSet = ps1.executeQuery();
        Integer myAmount = 0;
        while (resultSet.next()){
            String username = resultSet.getString(2);
            Integer amount = resultSet.getInt(3);
            System.out.println("username =" + username+ " amount = " + amount);
            if (username.equals("user1")){
                myAmount = amount;
            }
        }
        // 根據查詢出來的結果去更新會有什麼問題？
        /**
         * 1. 開啟JdbcTransaction中的事務，執行更新操作但不commit
         * 2. 本事務的更新操作將卡在更新資料之前，直到上一個事務提交，交出該資料鎖的許可權
         * 3. 此時更新的資料將是根據前面查得的資料進行更新的，那麼此時更新的依據將會是舊的資料
         *
         * 解決：開啟事務，鎖住查詢出來的資料，若其它事務正在對本事務需要查詢的資料進行操作，那麼本事務等待直至
         * 其它事務commit
         */

//        如果有其它事務正在操作這條資料，那麼此處將會等到其它事務提交以後才能繼續往下執行
//        根據mysql的內部機制，更新同一條資料的兩個事務不能同時執行，需要等待其中一個事務執行完
        String sql2 = "UPDATE user_transaction set amount = ? WHERE username = ? ";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.setString(1,String.valueOf((myAmount+100)));
        ps2.setString(2,"user1");
        ps2.executeUpdate();
        System.out.println("進行資料更新");

        // 提交事務
        connection.commit();
        ps1.close();
        ps2.close();
    }

    private static Connection getConn() {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test";
        String username = "root";
        String password = "root";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,載入對應驅動
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}
複製程式碼

資料庫：

MySQL 中的事務詳解
樣例示範:
執行事務1，但先不提交（斷點停留在commit( ) 處），開啟事務2，會發現事務2會停留在第二條sql（更新資料的sql）前，原因是事務1正在對同一份資料進行更新，事務2無法獲取到資料的鎖；這時提交事務1，事務2也隨之提交。這時會出現的問題是事務2中是根據舊資料為依據進行更新的，這種情況在生產中是不允許出現的。 解決方式：

  1. 對事務2中查詢出來的資料進行加鎖,這裡要注意的是加鎖的資料一定要是我們需要查詢的指定資料，所以這裡一定要加上where條件，否則有可能導致鎖全表，這樣將給系統效能帶來很大的影響。開啟事務1以後，事務2直到事務1提交以後才拿到查詢結果，因為要先獲取該行資料的鎖，這樣則不會出現讀舊資料去更新的情況。

相關文章