ホームページ >データベース >mysql チュートリアル >Mysqlトランザクションとデータ整合性処理の詳細説明

Mysqlトランザクションとデータ整合性処理の詳細説明

小云云オリジナル: 2017-12-18 15:39:251725ブラウズ

この記事では、Mysql トランザクションとデータ整合性処理について詳しく説明します。仕事では、利用可能なインベントリを更新する必要があるときに、他のユーザーがインベントリデータを変更した可能性があり、その結果、クエリしたインベントリデータに問題が発生します。以下のソリューションを見てみましょう。

MySQL の InnoDB では、デフォルトの Tansaction 分離レベルは REPEATABLE READ です。

SELECT の後に同じフォームを UPDATE する場合は、SELECT... UPDATE を使用するのが最善です。

例:

製品の数量を保存する製品フォームに数量があるとします。注文が確立される前に、製品の数量が十分であるかどうか (数量 > 0) を判断する必要があります。数量が 1 に更新されます。コードは次のとおりです:

SELECT quantity FROM products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;

なぜ安全ではないのですか?

少量の場合には問題がない可能性がありますが、大量のデータアクセスは「確実に」問題を引き起こします。数量 > 0 のときに在庫を差し引く必要がある場合、最初の SELECT 行でプログラムによって読み取られた数量が 2 であると仮定します。数値は正しいように見えますが、MySQL が UPDATE の準備をしているときに、誰かがすでに在庫を差し引いている可能性があります。 0になったのですが、プログラムはそれを知らずに間違ったUPDATEを続けてしまいました。したがって、読み取られて送信されたデータが正しいことを確認するには、トランザクションメカニズムを使用する必要があります。

それでは、MySQL でこのようにテストできます。コードは次のとおりです:

SET AUTOCOMMIT=0; BEGIN WORK; SELECT quantity FROM products WHERE id=3 FOR UPDATE;

このとき、商品データの id=3 のデータはロックされており (注 3)、他のトランザクションはこのトランザクションを待つ必要があります。実行する前に送信する必要があります

SELECT * FROM products WHERE id=3 FOR UPDATE

これで、他のトランザクションで数量ごとに読み取られた数値が正しいことを確認できます。

UPDATE products SET quantity = '1' WHERE id=3 ; COMMIT WORK;

コミットがデータベースに書き込まれ、製品のロックが解除されます。
注 1: BEGIN/COMMIT は、トランザクションの開始点と終了点です。ロックステータスを対話的に観察するには、3 つ以上の MySQL コマンドウィンドウを使用できます。
注 2: トランザクション中、同じデータを持つ SELECT ... FOR UPDATE または LOCK IN SHARE MODE のみが、他のトランザクションの終了を待ってから実行されます。通常、SELECT... はこれによる影響を受けません。
注 3: InnoDB はデフォルトで行レベルのロックに設定されているため、データ列のロックについてはこの記事を参照してください。
注 4: InnoDB フォームでは LOCK TABLES 命令を使用しないようにしてください。最終手段として使用する必要がある場合は、システムで頻繁に発生するデッドロックを避けるために、InnoDB での LOCK TABLES の使用に関する公式の手順を読んでください。

より高度な使用法

最初にクエリを実行してからデータを更新する必要がある場合は、次のようなステートメントを使用するのが最適です:

UPDATE products SET quantity = '1' WHERE id=3 AND quantity > 0;

この方法では、何も追加せずに処理できます。

MySQL は高い同時実行性を処理し、在庫の過剰販売を防ぎます

とても良い記事を見たので、勉強のために転送しました。

Wang 氏は今日、私たちに別の教訓を教えてくれました。実際、Wang 氏は、MySQL の高い同時実行性の処理と在庫の過剰販売の防止の問題についてすでに言及していましたが、当時は誰もがそれを理解していたにもかかわらず、実際には次のようなことが起こりました。開発の段階では、この点についてはまだ認識されていません。今日はこの問題について私の理解を整理し、将来このようなコースがもっと増えることを願っています。

まず、売れすぎた在庫の問題について説明します。一般に、電子商取引 Web サイトでは、共同購入、フラッシュセール、特別オファーなどのアクティビティが発生します。このようなアクティビティの共通の特徴は、訪問数が急増し、数千、場合によっては数十に達することです。何千人もの人々が製品を購入しようとします。しかし、アクティブな商品であるため、在庫は間違いなく非常に限られており、過剰購入や不必要な損失を防ぐために在庫をどのように制御するかは、多くの電子商取引 Web サイトプログラマーにとっての頭の痛い問題でもあります。

技術的な観点から言うと、必ずトランザクションを思い浮かべる人が多いと思いますが、トランザクションは売れ過ぎ在庫を抑制するための必要条件ではありますが、必要十分条件ではありません。

例:

総在庫: 4 商品

依頼者: a、1 商品 b、2 商品 c、3 商品

手順は次のとおりです:

beginTranse(开启事务)

try{

    $result = $dbca->query('select amount from s_store where postID = 12345');

    if(result->amount > 0){

        //quantity为请求减掉的库存数量

        $dbca->query('update s_store set amount = amount - quantity where postID = 12345');

    }

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

上記のコードは、在庫を管理するために通常記述するコードですはい、ほとんどの人はこのように書くでしょう。これは小さな問題のように見えるかもしれませんが、実際には大きな抜け穴が隠されています。データベースアクセスは実際にはディスクファイルへのアクセスであり、データベース内のテーブルは実際にはディスク上に保存されたファイルであり、1 つのファイルにも複数のテーブルが含まれています。たとえば、同時実行性が高いため、現在 3 人のユーザー a、b、c がこのトランザクションに参加しています。このとき、共有ロックが生成されるため、選択すると、これら 3 人のユーザーが検出した在庫数量はすべて 4 になります。また、mysql innodb によって見つかった結果はバージョン管理されていることに注意してください。他のユーザーが更新してコミットする前 (つまり、新しいバージョンが生成される前)、現在のユーザーによって見つかった結果は同じバージョンのままです。これら 3 人のユーザーが同時に update に到着した場合、update ステートメントはこの時点で同時実行をシリアル化します。つまり、同時に到着した 3 人のユーザーはソートされ、1 人ずつ実行され、排他ロックがかけられます。更新ステートメントがコミットされる前に、他のユーザーが実行を待機しているため、実行後に新しいバージョンが生成されます。しかし、上記の説明によれば、コードを修正すれば、買われすぎ現象は起こらなくなります。コードは次のとおりです:

beginTranse(开启事务)

try{

    //quantity为请求减掉的库存数量
    $dbca->query('update s_store set amount = amount - quantity where postID = 12345');

    $result = $dbca->query('select amount from s_store where postID = 12345');

    if(result->amount < 0){

       throw new Exception(&#39;库存不足&#39;);

    }

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

さらに、より簡潔な方法:

beginTranse(开启事务)

try{

    //quantity为请求减掉的库存数量
    $dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

=====================================================================================

1、在秒杀的情况下，肯定不能如此高频率的去读写数据库，会严重造成性能问题的
必须使用缓存，将需要秒杀的商品放入缓存中，并使用锁来处理其并发情况。当接到用户秒杀提交订单的情况下，先将商品数量递减（加锁/解锁）后再进行其他方面的处理，处理失败在将数据递增1（加锁/解锁），否则表示交易成功。
当商品数量递减到0时，表示商品秒杀完毕，拒绝其他用户的请求。

2、这个肯定不能直接操作数据库的，会挂的。直接读库写库对数据库压力太大，要用缓存。
把你要卖出的商品比如10个商品放到缓存中；然后在memcache里设置一个计数器来记录请求数，这个请求书你可以以你要秒杀卖出的商品数为基数，比如你想卖出10个商品，只允许100个请求进来。那当计数器达到100的时候，后面进来的就显示秒杀结束，这样可以减轻你的服务器的压力。然后根据这100个请求，先付款的先得后付款的提示商品以秒杀完。

3、首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。

这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。
乐观锁:，就是在数据库设计一个版本号的字段，每次修改都使其+1，这样在提交时比对提交前的版本号就知道是不是并发提交了，但是有个缺点就是只能是应用中控制，如果有跨应用修改同一条数据乐观锁就没办法了，这个时候可以考虑悲观锁。

悲观锁:，就是直接在数据库层面将数据锁死，类似于oralce中使用select xxxxx from xxxx where xx=xx for update，这样其他线程将无法提交数据。

除了加锁的方式也可以使用接收锁定的方式，思路是在数据库中设计一个状态标识位，用户在对数据进行修改前，将状态标识位标识为正在编辑的状态，这样其他用户要编辑此条记录时系统将发现有其他用户正在编辑，则拒绝其编辑的请求，类似于你在操作系统中某文件正在执行，然后你要修改该文件时，系统会提醒你该文件不可编辑或删除。

4、不建议在数据库层面加锁，建议通过服务端的内存锁（锁主键）。当某个用户要修改某个id的数据时，把要修改的id存入memcache，若其他用户触发修改此id的数据时，读到memcache有这个id的值时，就阻止那个用户修改。

5、实际应用中，并不是让mysql去直面大并发读写，会借助“外力”，比如缓存、利用主从库实现读写分离、分表、使用队列写入等方法来降低并发读写。

悲观锁和乐观锁

首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。

　　悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

　　乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

　　两种锁各有优缺点，不能单纯的定义哪个好于哪个。乐观锁比较适合数据修改比较少，读取比较频繁的场景，即使出现了少量的冲突，这样也省去了大量的锁的开销，故而提高了系统的吞吐量。但是如果经常发生冲突（写数据比较多的情况下），上层应用不不断的retry，这样反而降低了性能，对于这种情况使用悲观锁就更合适。

实战

Mysqlトランザクションとデータ整合性処理の詳細説明

对这个表的 amount 进行修改，开两个命令行窗口

第一个窗口A;

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;

第二个窗口B：

# 更新订单ID 124 的库存数量
UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;

我们可以看到窗口A加了事物，锁住了这条数据，窗口B执行时会出现这样的问题：

Mysqlトランザクションとデータ整合性処理の詳細説明

第一个窗口完整的提交事物：

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;
UPDATE `order_tbl` SET amount = 10 WHERE order_id = 124;
COMMIT WORK;

Mysqlトランザクションとデータ整合性処理の詳細説明

なぜ安全ではないのですか?

より高度な使用法

MySQL は高い同時実行性を処理し、在庫の過剰販売を防ぎます

悲观锁和乐观锁

实战

関連記事