并发情况下数据校验-基于数据库实现

在开发过程中，我们会遇到校验数据的唯一性，数据更新之后是否超过设置的阈值等等。并发情况下数据校验常见方式有使用分布式锁，数据库行锁等。本章介绍并发情况下使用数据库进行数据校验，常见的场景有:

1. 金额扣减
2. 抽奖奖品数量扣减
3. 库存扣减

数据库行锁

原理

mysql数据库锁
悲观锁&乐观锁
Mysql 如何解决并发更新同一行数据
MySql MVCC 详解

实际操作

mysql 的默认引擎 InnoDB 支持行锁的，本节使用 Mysql 数据库来说明数据库行锁

数据准备

1. 创建金额表

create table t_amount (
id int primary key auto_increment,
total_amount decimal(10,2) not null default 0,
used_amount decimal(10,2) not null default 0
) charset = utf8mb4;

2. 插入测试数据

insert into t_amount(total_amount, used_amount) values(100, 0);

3. 查询数据表

mysql> select * from t_amount;
+----+--------------+-------------+
| id | total_amount | used_amount |
+----+--------------+-------------+
|  1 |       100.00 |        0.00 |
+----+--------------+-------------+
1 row in set (0.00 sec)

开启事务，更新数据

1. 打开2个终端，开启事务

start transaction;

start transaction 语句有啥作用？可以使用 help start transaction 命令查看

mysql> help start transaction
Name: 'START TRANSACTION'
Description:
Syntax:
START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {
  
  0 | 1}

These statements provide control over use of transactions:

o START TRANSACTION or BEGIN start a new transaction.

o COMMIT commits the current transaction, making its changes permanent.

o ROLLBACK rolls back the current transaction, canceling its changes.

o SET autocommit disables or enables the default autocommit mode for
  the current session.

By default, MySQL runs with autocommit mode enabled. This means that as
soon as you execute a statement that updates (modifies) a table, MySQL
stores the update on disk to make it permanent. The change cannot be
rolled back.

To disable autocommit mode implicitly for a single series of
statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the
transaction with COMMIT or ROLLBACK. The autocommit mode then reverts
to its previous state.

START TRANSACTION permits several modifiers that control transaction
characteristics. To specify multiple modifiers, separate them by
commas.

o The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for
  storage engines that are capable of it. This applies only to InnoDB.
  The effect is the same as issuing a START TRANSACTION followed by a
  SELECT from any InnoDB table. See
  http://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html.
  The WITH CONSISTENT SNAPSHOT modifier does not change the current
  transaction isolation level, so it provides a consistent snapshot
  only if the current isolation level is one that permits a consistent
  read. The only isolation level that permits a consistent read is
  REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT
  SNAPSHOT clause is ignored. A warning is generated when the WITH
  CONSISTENT SNAPSHOT clause is ignored.

o The READ WRITE and READ ONLY modifiers set the transaction access
  mode. They permit or prohibit changes to tables used in the
  transaction. The READ ONLY restriction prevents the transaction from
  modifying or locking both transactional and nontransactional tables
  that are visible to other transactions; the transaction can still
  modify or lock temporary tables.

  MySQL enables extra optimizations for queries on InnoDB tables when
  the transaction is known to be read-only. Specifying READ ONLY
  ensures these optimizations are applied in cases where the read-only
  status cannot be determined automatically. See
  http://dev.mysql.com/doc/refman/8.0/en/innodb-performance-ro-txn.html
  for more information.

  If no access mode is specified, the default mode applies. Unless the
  default has been changed, it is read/write. It is not permitted to
  specify both READ WRITE and READ ONLY in the same statement.

  In read-only mode, it remains possible to change tables created with
  the TEMPORARY keyword using DML statements. Changes made with DDL
  statements are not permitted, just as with permanent tables.

  For additional information about transaction access mode, including
  ways to change the default mode, see [HELP ISOLATION].

  If the read_only system variable is enabled, explicitly starting a
  transaction with START TRANSACTION READ WRITE requires the
  CONNECTION_ADMIN or SUPER privilege.

*Important*:

Many APIs used for writing MySQL client applications (such as JDBC)
provide their own methods for starting transactions that can (and
sometimes should) be used instead of sending a START TRANSACTION
statement from the client. See
http://dev.mysql.com/doc/refman/8.0/en/connectors-apis.html, or the
documentation for your API, for more information.

To disable autocommit mode explicitly, use the following statement:

SET autocommit=0;

After disabling autocommit mode by setting the autocommit variable to
zero, changes to transaction-safe tables (such as those for InnoDB or
NDB (http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html)) are
not made permanent immediately. You must use COMMIT to store your
changes to disk or ROLLBACK to ignore the changes.

autocommit is a session variable and must be set for each session. To
disable autocommit mode for each new connection, see the description of
the autocommit system variable at
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html.

BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for
initiating a transaction. START TRANSACTION is standard SQL syntax, is
the recommended way to start an ad-hoc transaction, and permits
modifiers that BEGIN does not.

The BEGIN statement differs from the use of the BEGIN keyword that
starts a BEGIN ... END compound statement. The latter does not begin a
transaction. See [HELP BEGIN END].

*Note*:

Within all stored programs (stored procedures and functions, triggers,
and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN