MySQL Innodb トランザクションプログラミングの問題と処理-mysql チュートリアル-php.cn

MySQL Innodb トランザクションプログラミングの問題と処理

黄舟

リリース： 2017-02-06 11:02:44

オリジナル

1325 人が閲覧しました

1. ループ内での送信の問題

以下に示すように、多くの開発者はトランザクションをループ内で送信することを好みます。

DROP PROCEDURE IF EXISTS load1;CREATE PROCEDURE load1(count INT UNSIGNED)BEGIN
   DECLARE s INT UNSIGNED DEFAULT 1;
   DECLARE c CHAR(80) DEFAULT REPEAT(&#39;a&#39;,80);   WHILE s <= count DO
      INSERT INTO t1 select NULL,c;
      COMMIT;      SET s=s+1;   END WHILE;END;

ログイン後にコピー

上の例では、 commit コマンドは重要ではありません。 MySQL の innodb のストレージエンジンはデフォルトで自動送信になっているため、ストアドプロシージャ内のコミットを削除しても結果は同じになります。以下に示すように、開発者が見落としやすいもう 1 つの問題も次のとおりです。

DROP PROCEDURE IF EXISTS load2; CREATE PROCEDURE load2(count INT UNSIGNED)BEGIN
   DECLARE s INT UNSIGNED DEFAULT 1;
   DECLARE c CHAR(80) DEFAULT REPEAT(&#39;a&#39;,80);   WHILE s <= count DO
      INSERT INTO t1 select NULL,c;      SET s=s+1;   END WHILE;END;

ログイン後にコピー

上記のストアドプロシージャに関係なく、エラーが発生すると、データベースは不明な場所に留まります。たとえば、10,000 個のデータを挿入したいのですが、5,000 個のデータを挿入するときにエラーが発生しました。しかし、この 5,000 個のデータはすでにデータベースに格納されています。もう 1 つの問題はパフォーマンスです。挿入がトランザクション内に配置されるため、上記の 2 つのストアドプロシージャは、以下のストアドプロシージャよりも高速ではありません。以下に示すように、3 番目の方法の方がはるかに高速であることがわかります。これは、言及ごとに REDO ログを書き込む必要があるため、load1 とload2 は実際に 100 万回の REDO ログを書き込むためです。ストアドプロシージャload3 については、REDO ログを 1 回だけ書き込みました。

最初にテストテーブルを準備します

DROP PROCEDURE IF EXISTS load3; CREATE PROCEDURE load3(count INT UNSIGNED)BEGIN
   DECLARE s INT UNSIGNED DEFAULT 1;
   DECLARE c CHAR(80) DEFAULT REPEAT(&#39;a&#39;,80);
   START TRANSACTION;   WHILE s <= count DO
      INSERT INTO t1 select NULL,c;      SET s=s+1;   END WHILE;
   COMMIT;END;

ログイン後にコピー

テストを実行します

CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(500) NULL ,
PRIMARY KEY (`id`)
) ;

ログイン後にコピー

2番目のストアドプロシージャload2については、トランザクションを手動で開始することもでき、ストアドプロシージャload3の効果も実現できます。実行時間は次のとおりです。

09:50:44 test> call load1(1000000);
Query OK, 0 rows affected (1 min 4.90 sec)09:54:23 test> truncate table t1;
Query OK, 0 rows affected (0.05 sec)09:54:25 test> call load2(1000000);
Query OK, 1 row affected (1 min 3.38 sec)09:55:32 test> truncate table t1;
Query OK, 0 rows affected (0.20 sec)09:55:58 test> call load3(1000000);
Query OK, 0 rows affected (33.90 sec)

ログイン後にコピー

2. 自動送信の使用について

一部の特殊なシナリオでは、自動送信が必ずしも良いとは限らない場合があります。たとえば、前述の循環送信の問題では、MySQL データベースはデフォルトで自動送信 (自動コミット) になります。 MySQL の送信メソッドは次の方法で変更できます:

09:57:42 test> begin;
Query OK, 0 rows affected (0.00 sec)09:57:46 test> call load2(1000000);
Query OK, 1 row affected (34.08 sec)09:58:26 test> commit;
Query OK, 0 rows affected (0.76 sec)

ログイン後にコピー

START TRANSATION または BEGIN を使用してトランザクションを明示的に開始することもできます。 MySQL は自動的に

SET AUTOCOMMIT=0 を実行し、COMMIT または ROLLBACK がトランザクションを終了した後に SET AUTOCOMMIT=1 を実行します。

3. 自動ロールバックを使用して例外を処理するストアドプロセスで例外が発生した場合はどうすればよいですか? Innodb ストレージエンジンは、HANDLER を介したトランザクションの自動ロールバックをサポートしています。保存処理中にエラーが発生した場合、ロールバック操作が自動的に実行されます。次の例を見てみましょう。

10:35:34 test> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

ログイン後にコピー

テストテーブルは次のとおりです

CREATE PROCEDURE sp_auto_rollback_demo()BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;END;

ログイン後にコピー

上記のストアドプロシージャを実行すると、2番目のレコード1を挿入するときにエラーが発生しますが、自動ロールバック操作が有効になっているため、このストアドプロシージャの実行結果は手順は以下の通りです:

CREATE TABLE `b` (  `a` int(11) NOT NULL DEFAULT &#39;0&#39;,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ログイン後にコピー

問題なく正常に動作しているようですが、sp_auto_rollback_demo を実行した際に成功したのか失敗したのか？これは次のように処理できます。例は次のとおりです。

10:09:46 test> call sp_auto_rollback_demo;
Query OK, 0 rows affected (0.01 sec)10:10:04 test> select * from b;Empty set (0.00 sec)

ログイン後にコピー

エラーが発生した場合は、最初にロールバックしてから、操作中にエラーが発生したことを示す -1 を返します。 1 が返されると、通常の動作が示されます。実行結果は次のとおりです:

DROP PROCEDURE IF EXISTS sp_auto_rollback_demo;CREATE PROCEDURE sp_auto_rollback_demo()BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; END;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;
   SELECT 1;END;

ログイン後にコピー

上記は、MySQL Innodb トランザクションプログラミングの問題と処理の内容です。さらに関連する内容については、PHP 中国語 Web サイト (m.sbmmt.com) に注目してください。