准备数据

创建库：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);123456789

异常分类 http://dxb.myzx.cn/audio/

我们将异常分为mysql内部异常和外部异常

mysql内部异常

当我们执行一些sql的时候，可能违反了mysql的一些约束，导致mysql内部报错，如插入数据违反唯一约束，更新数据超时等，此时异常是由mysql内部抛出的，我们将这些由mysql抛出的异常统称为内部异常。

外部异常 http://dxb.myzx.cn/shanxi/

当我们执行一个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 ;1234567891011121314

上面存储过程插入了两条数据，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)123456789101112

上面先删除了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 ;1234567891011121314151617181920212223242526

上面重点是这句： http://dxb.myzx.cn/nanning/

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE;1

当有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)12345678

上面插入了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)1234567891011121314

上面插入了2条不同的数据，最终插入成功。

外部异常

外部异常不是由mysql内部抛出的错误，而是由于sql的执行结果和我们期望的结果不一致的时候，我们需要对这种情况做一些处理，如回滚操作。

示例1 http://zzdxb.baikezh.com/puyang/

我们来模拟电商中下单操作，按照上面的步骤来更新账户余额。

电商中有个账户表和订单表，如下：

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);12345678910111213

下单操作涉及到操作上面的账户表，我们用存储过程来模拟实现：

/*删除存储过程*/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 ;123456789101112131415161718192021222324252627282930313233343536

上面过程主要分为3步骤：验证余额、修改余额变量、更新余额。

开启2个cmd窗口，连接mysql，同时执行下面操作：

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

然后执行： http://zzdxb.baikezh.com/

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)12345678910111213141516

上面出现了非常严重的错误：下单成功了2次，但是账户只扣了100。

上面过程是由于2个操作并发导致的，2个窗口同时执行第一步的时候看到了一样的数据（看到的余额都是1000），然后继续向下执行，最终导致结果出问题了。

上面操作我们可以使用乐观锁来优化。 http://ask.baikezh.com/hefei/

乐观锁的过程：用期望的值和目标值进行比较，如果相同，则更新目标值，否则什么也不做。

乐观锁类似于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);123456789101112131415

创建存储过程： http://dxb.myzx.cn/oldage/

/*删除存储过程*/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 ;12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849

ROW_COUNT()可以获取更新或插入后获取受影响行数。将受影响行数放在v_update_count中。

然后根据v_update_count是否等于1判断更新是否成功，如果成功则记录订单信息并提交事务，否则回滚事务。

验证结果：开启2个cmd窗口，连接mysql，执行下面操作： http://ask.baikezh.com/chengdou/

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

窗口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)1234567891011121314151617

窗口2结果： http://dxb.myzx.cn

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)1234567891011121314151617

可以看到第一个窗口下单成功了，窗口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)123456789101112131415