Mysql系列第十九講

準備資料
例外分類
Mysql內部例外
外部例外

準備資料

創建庫：javacode2018

創建表：test1，test1表中的a欄位為主鍵，

/*建庫javacode2018*/
drop database if exists javacode2018;
create database javacode2018;

/*切換到javacode2018庫*/
use javacode2018;

DROP TABLE IF EXISTS test1;
CREATE TABLE test1(a int PRIMARY KEY);

例外分類

我們將例外分為mysql內部例外和外部例外

mysql內部例外

當我們執行一些sql的時候，可能違反了mysql的一些約束，導致mysql內部報錯，如插入資料違反唯一約束，更新資料超時等，此時例外是由mysql內部拋出的，我們將這些由mysql拋出的例外統稱為內部例外，

外部例外

當我們執行一個update的時候，可能我們期望影響1行，但是實際上影響的不是1行資料，這種情況：sql的執行結果和期望的結果不一致，這種情況也我們也把他作為外部例外處理，我們將sql執行結果和期望結果不一致的情況統稱為外部例外，

Mysql內部例外

示例1

test1表中的a欄位為主鍵，我們向test1表同時插入2條資料，并且放在一個事務中執行，最終要么都插入成功，要么都失敗，

創建存盤程序：

/*洗掉存盤程序*/
DROP PROCEDURE IF EXISTS proc1;
/*宣告結束符為$*/
DELIMITER $
/*創建存盤程序*/
CREATE PROCEDURE proc1(a1 int,a2 int)
  BEGIN
    START TRANSACTION;
    INSERT INTO test1(a) VALUES (a1);
    INSERT INTO test1(a) VALUES (a2);
    COMMIT;
  END $
/*結束符置為;*/
DELIMITER ;

上面存盤程序插入了兩條資料，a的值都是1，

驗證結果：

mysql> DELETE FROM test1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc1(1,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> SELECT * from test1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

上面先洗掉了test1表中的資料，然后呼叫存盤程序proc1，由于test1表中的a欄位是主鍵，插入第二條資料時違反了a欄位的主鍵約束，mysql內部拋出了例外，導致第二條資料插入失敗，最終只有第一條資料插入成功了，

上面的結果和我們期望的不一致，我們希望要么都插入成功，要么失敗，

那我們怎么做呢？我們需要捕獲上面的主鍵約束例外，然后發現有例外的時候執行rollback回滾操作，改進上面的代碼，看下面示例2，

示例2

我們對上面示例進行改進，捕獲上面主鍵約束例外，然后進行回滾處理，如下：

創建存盤程序：

/*洗掉存盤程序*/
DROP PROCEDURE IF EXISTS proc2;
/*宣告結束符為$*/
DELIMITER $
/*創建存盤程序*/
CREATE PROCEDURE proc2(a1 int,a2 int)
  BEGIN
    /*宣告一個變數，標識是否有sql例外*/
    DECLARE hasSqlError int DEFAULT FALSE;
    /*在執行程序中出任何例外設定hasSqlError為TRUE*/
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

    /*開啟事務*/
    START TRANSACTION;
    INSERT INTO test1(a) VALUES (a1);
    INSERT INTO test1(a) VALUES (a2);

    /*根據hasSqlError判斷是否有例外，做回滾和提交操作*/
    IF hasSqlError THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
  END $
/*結束符置為;*/
DELIMITER ;

上面重點是這句：

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;

當有sql例外的時候，會將變數hasSqlError的值置為TRUE，

模擬例外情況：

mysql> DELETE FROM test1;
Query OK, 2 rows affected (0.00 sec)

mysql> CALL proc2(1,1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from test1;
Empty set (0.00 sec)

上面插入了2條一樣的資料，插入失敗，可以看到上面test1表無資料，和期望結果一致，插入被回滾了，

模擬正常情況：

mysql> DELETE FROM test1;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc2(1,2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from test1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

上面插入了2條不同的資料，最終插入成功，

外部例外

外部例外不是由mysql內部拋出的錯誤，而是由于sql的執行結果和我們期望的結果不一致的時候，我們需要對這種情況做一些處理，如回滾操作，

示例1

我們來模擬電商中下單操作，按照上面的步驟來更新賬戶余額，

電商中有個賬戶表和訂單表，如下：

DROP TABLE IF EXISTS t_funds;
CREATE TABLE t_funds(
  user_id INT PRIMARY KEY COMMENT '用戶id',
  available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '賬戶余額'
) COMMENT '用戶賬戶表';
DROP TABLE IF EXISTS t_order;
CREATE TABLE t_order(
  id int PRIMARY KEY AUTO_INCREMENT COMMENT '訂單id',
  price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '訂單金額'
) COMMENT '訂單表';
delete from t_funds;
/*插入一條資料，用戶id為1001，余額為1000*/
INSERT INTO t_funds (user_id,available) VALUES (1001,1000);

下單操作涉及到操作上面的賬戶表，我們用存盤程序來模擬實作：

/*洗掉存盤程序*/
DROP PROCEDURE IF EXISTS proc3;
/*宣告結束符為$*/
DELIMITER $
/*創建存盤程序*/
CREATE PROCEDURE proc3(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64))
  a:BEGIN
    DECLARE v_available DECIMAL(10,2);

    /*1.查詢余額，判斷余額是否夠*/
    select a.available into v_available from t_funds a where a.user_id = v_user_id;
    if v_available<=v_price THEN
      SET v_msg='賬戶余額不足!';
      /*退出*/
      LEAVE a;
    END IF;

    /*模擬耗時5秒*/
    SELECT sleep(5);

    /*2.余額減去price*/
    SET v_available = v_available - v_price;

    /*3.更新余額*/
    START TRANSACTION;
    UPDATE t_funds SET available = v_available WHERE user_id = v_user_id;

    /*插入訂單明細*/
    INSERT INTO t_order (price) VALUES (v_price);

    /*提交事務*/
    COMMIT;
    SET v_msg='下單成功!';
  END $
/*結束符置為;*/
DELIMITER ;

上面程序主要分為3步驟：驗證余額、修改余額變數、更新余額，

開啟2個cmd視窗，連接mysql，同時執行下面操作：

USE javacode2018;
CALL proc3(1001,100,@v_msg);
select @v_msg;

然后執行：

mysql> SELECT * FROM t_funds;
+---------+-----------+
| user_id | available |
+---------+-----------+
|    1001 |    900.00 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_order;
+----+--------+
| id | price  |
+----+--------+
|  1 | 100.00 |
|  2 | 100.00 |
+----+--------+
2 rows in set (0.00 sec)

上面出現了非常嚴重的錯誤：下單成功了2次，但是賬戶只扣了100，

上面程序是由于2個操作并發導致的，2個視窗同時執行第一步的時候看到了一樣的資料（看到的余額都是1000），然后繼續向下執行，最終導致結果出問題了，

上面操作我們可以使用樂觀鎖來優化，

樂觀鎖的程序：用期望的值和目標值進行比較，如果相同，則更新目標值，否則什么也不做，

樂觀鎖類似于java中的cas操作，這塊需要了解的可以點擊：詳解CAS

我們可以在資金表t_funds添加一個version欄位，表示版本號，每次更新資料的時候+1，更新資料的時候將version作為條件去執行update，根據update影響行數來判斷執行是否成功，優化上面的代碼，見示例2，

示例2

對示例1進行優化，

創建表：

DROP TABLE IF EXISTS t_funds;
CREATE TABLE t_funds(
  user_id INT PRIMARY KEY COMMENT '用戶id',
  available DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '賬戶余額',
  version INT DEFAULT 0 COMMENT '版本號，每次更新+1'
) COMMENT '用戶賬戶表';

DROP TABLE IF EXISTS t_order;
CREATE TABLE t_order(
  id int PRIMARY KEY AUTO_INCREMENT COMMENT '訂單id',
  price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '訂單金額'
)COMMENT '訂單表';
delete from t_funds;
/*插入一條資料，用戶id為1001，余額為1000*/
INSERT INTO t_funds (user_id,available) VALUES (1001,1000);

創建存盤程序：

/*洗掉存盤程序*/
DROP PROCEDURE IF EXISTS proc4;
/*宣告結束符為$*/
DELIMITER $
/*創建存盤程序*/
CREATE PROCEDURE proc4(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64))
    a:BEGIN
    /*保存當前余額*/
    DECLARE v_available DECIMAL(10,2);
    /*保存版本號*/
    DECLARE v_version INT DEFAULT 0;
    /*保存影響的行數*/
    DECLARE v_update_count INT DEFAULT 0;


    /*1.查詢余額，判斷余額是否夠*/
    select a.available,a.version into v_available,v_version from t_funds a where a.user_id = v_user_id;
    if v_available<=v_price THEN
      SET v_msg='賬戶余額不足!';
      /*退出*/
      LEAVE a;
    END IF;

    /*模擬耗時5秒*/
    SELECT sleep(5);

    /*2.余額減去price*/
    SET v_available = v_available - v_price;

    /*3.更新余額*/
    START TRANSACTION;
    UPDATE t_funds SET available = v_available WHERE user_id = v_user_id AND version = v_version;
    /*獲取上面update影響行數*/
    select ROW_COUNT() INTO v_update_count;

    IF v_update_count=1 THEN
      /*插入訂單明細*/
      INSERT INTO t_order (price) VALUES (v_price);
      SET v_msg='下單成功!';
      /*提交事務*/
      COMMIT;
    ELSE
      SET v_msg='下單失敗,請重試!';
      /*回滾事務*/
      ROLLBACK;
    END IF;
  END $
/*結束符置為;*/
DELIMITER ;

ROW_COUNT()可以獲取更新或插入后獲取受影響行數，將受影響行數放在v_update_count中，

然后根據v_update_count是否等于1判斷更新是否成功，如果成功則記錄訂單資訊并提交事務，否則回滾事務，

驗證結果：開啟2個cmd視窗，連接mysql，執行下面操作：

use javacode2018;
CALL proc4(1001,100,@v_msg);
select @v_msg;

視窗1結果：

mysql> CALL proc4(1001,100,@v_msg);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)

Query OK, 0 rows affected (5.00 sec)

mysql> select @v_msg;
+---------------+
| @v_msg        |
+---------------+
| 下單成功!     |
+---------------+
1 row in set (0.00 sec)

視窗2結果：

mysql> CALL proc4(1001,100,@v_msg);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)

Query OK, 0 rows affected (5.01 sec)

mysql> select @v_msg;
+-------------------------+
| @v_msg                  |
+-------------------------+
| 下單失敗,請重試!        |
+-------------------------+
1 row in set (0.00 sec)

可以看到第一個視窗下單成功了，視窗2下單失敗了，
再看一下2個表的資料：

mysql> SELECT * FROM t_funds;
+---------+-----------+---------+
| user_id | available | version |
+---------+-----------+---------+
|    1001 |    900.00 |       0 |
+---------+-----------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_order;
+----+--------+
| id | price  |
+----+--------+
|  1 | 100.00 |
+----+--------+
1 row in set (0.00 sec)

易兮科技

Java

18級大連海事大學在校生
網路工程 & 國際經濟與貿易雙學位
中山市易兮軟體開發有限公司創始人
易兮科技團隊負責人
Java語言愛好者

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/141050.html

標籤：java

上一篇：Java用timer做成的定時器同步資料任務，剛部署上去同步一次資料，到第二天就同步多次資料，造成資料重復

下一篇：postgresql安裝，環境配置，密碼設定，c++呼叫libpqxx鏈接資料庫，決議jsonb格式

Mysql系列第十九講 例外捕獲及處理詳解

Mysql系列第十九講

準備資料

例外分類

Mysql內部例外

外部例外

Mysql系列第十九講例外捕獲及處理詳解