MySQLトランザクション機能

18520 ワード

mysql マリアード事務 MySQLデータベース

MySQLトランザクション機能
==============================================================================
取引:
1.紹介する
★事務:

原子間SQLクエリーのセット、または1つ以上のSQL文からなる独立した作業ユニット.

★ACIDテスト:
⊙A:AUTOMICITY、原子性;

トランザクション全体のすべての操作は、すべて正常に実行されたか、すべて失敗した後にロールバックされます.

⊙C:CONSISSTENCY、一致性;

データベースは、常に1つのコンシステンシ状態から別のコンシステンシ状態に移行する必要があります.

⊙I:ISO LATION、隔離性;

ある事務所の操作が提出される前に、他の事務に表示できるかどうか.同時操作を保証する目的で、隔離には複数のレベルがある.

⊙D:DURABILITY、持続性;

トランザクションがコミットされると、その変更は永続的に保存されます.

2.トランザクション・ログ:
mysqlはキャッシュメカニズムを最大限に使用してデータベースのアクセス効率を向上させますが、万が一データベースが停電した場合、キャッシュされたデータがディスクに書き込まれていないため、メモリにキャッシュされたデータが失われ、データが一致しない場合はどうしますか?
★Innodbは主にトランザクションログを通じてACID特性を実現する.
★取引ログは以下のとおりです.
⊙REDOログredo

Redoは、既に完了したトランザクションを記録するものであり、commit(コミット)を実行したトランザクションであり、記録ファイルはib_logfile 0 ib_logfile 1である.

⊙ロールバックログundo

Undoは、部分的に完了してハードディスク(HDD)に書き込まれた未完了のトランザクションを記録し、デフォルトではロールバックログは表領域(共有表領域または共有表領域)に記録されます.

★実行プロセス:

一般的にmysqlはクラッシュ後、サービスを再開し、innodbはロールバックログundoを通じてディスクに完了して書き込まれたすべての未完了トランザクションをrollbackします.その後、redoのトランザクションをすべて再実行(バックグラウンドで関連スレッドを起動して自動的に実行)するとデータを復元できますが、redoの量が増えるにつれて、redoの最初のエントリから復元するたびに時間がかかるため、checkpoint(チェックポイント)メカニズムが導入されました.

★トランザクションログ関連構成パラメータ:

innodb_log_files_in_group//グループにはいくつかのファイルがあります.デフォルトは2つの

です.

innodb_log_group_home_dir//ログディレクトリの格納場所、デフォルトはデータディレクトリ

innodb_log_file_size//ログファイルサイズ

innodb_mirrored_log_groups//ミラーロググループ

プレゼンテーション:
1.物事ログ関連パラメータの表示

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%innodb%log%';
+-------------------------------------------+---------+
| Variable_name                             | Value   |
+-------------------------------------------+---------+
| innodb_flush_log_at_trx_commit            | 1       |                          ，1  ON  
| innodb_locks_unsafe_for_binlog            | OFF     |
| innodb_log_block_size                     | 512     |
| innodb_log_buffer_size                    | 8388608 |
| innodb_log_file_size                      | 5242880 |       ，   5M
| innodb_log_files_in_group                 | 2       |         ，   2 
| innodb_log_group_home_dir                 | ./      |          ，        
| innodb_mirrored_log_groups                | 1       |      （  ）
| innodb_recovery_update_relay_log          | OFF     |
| innodb_use_global_flush_log_at_trx_commit | ON      |
+-------------------------------------------+---------+
10 rows in set (0.00 sec)

3.≪自動コミット｜Automatic Submit｜ldap≫:単一文トランザクション
★自動提出:単文取引

デフォルトでmysqlがオンになっているのは、単一の文の物事機能(つまり、実行が完了するたびに自動的に物事ログにコミットされ、ログからデータファイルに同期され、パフォーマンスに非常に影響します)です.手動で物事を制御するには、自動的に物事を提出する機能をオフにします.

★オートコミットコマンドの表示:

mysql> SELECT @@autocommit;

mysql> SHOW GLOBAL|SESSION VARIABLES LIKE 'autocommit'

★現在のセッション文を閉じる機能:

mysql> SET @@session.autocommit=0;

プレゼンテーション:
1.プレゼンテーションの効果のために、物事の機能をサポートするために、私はここで前のhellodbを修正しました.sqlのストレージエンジンはInnoDBで、mysqlを再インポートします.次に、手動で物事を制御するには、単一の文の物事機能をオフにする必要があります.

MariaDB [hellodb]> show table status like 'students'\G
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB    #      InnoDB
        Version: 10
     Row_format: Compact
           Rows: 25
 Avg_row_length: 655
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 26
    Create_time: 2016-11-28 11:42:19
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.02 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE 'autocommit';  #                ON
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> SET @@session.autocommit=0;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE 'autocommit'; #       OFF
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

--------------------------------------------------------------------------------
4.トランザクションの手動制御:
★起動:START TRANSACTION
★提出:COMMIT
★ロールバック:ROLLBACK
★事務サポートsavepoints:

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

プレゼンテーション:
1.単一文トランザクションを閉じた上で、手動で物事機能を起動し、ロールバック操作を実証します.

MariaDB [hellodb]> START TRANSACTION;  #       
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DESC courses;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| CourseID | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Course   | varchar(100)         | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.01 sec)

MariaDB [hellodb]> INSERT INTO courses (Course) VALUES ('Zabbix'),('Puppet'); #       
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | Zabbix         |
|        9 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> DELETE FROM courses WHERE CourseID=3;  #       
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | Zabbix         |
|        9 | Puppet         |
+----------+----------------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> ROLLBACK;  #          ，        
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses;  #                  
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)

2.操作にsavepoints(ストレージポイント)を追加し、次のようにストレージポイントにロールバックする場所を指定できます.

MariaDB [hellodb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> INSERT INTO courses (Course) VALUES ('Zabbix'),('Puppet');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> SAVEPOINT first;  #         
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DELETE FROM courses WHERE CourseID=3;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SAVEPOINT second; #         
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> UPDATE courses SET Course='nginx' WHERE CourseID=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hellodb]> SELECT * FROM courses;  #          
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        4 | nginx          |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       10 | Zabbix         |
|       11 | Puppet         |
+----------+----------------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> ROLLBACK TO second;  #          
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       10 | Zabbix         |
|       11 | Puppet         |
+----------+----------------+
8 rows in set (0.00 sec)

MariaDB [hellodb]> ROLLBACK TO first;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses;  #          
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       10 | Zabbix         |
|       11 | Puppet         |
+----------+----------------+
9 rows in set (0.01 sec)

MariaDB [hellodb]> ROLLBACK;  #        
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)

3.物事を起動して、操作を実行した後に提出して(COMMIT)、発見して更にロールバックして無効になって、この時すでにデータの中に同期したためです;

MariaDB [hellodb]> START TRANSACTION;   #     
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> INSERT INTO courses (Course) VALUES ('Zabbix'),('Puppet');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> COMMIT;   #   
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> SELECT * FROM courses;  #        
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses;  #            
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

5.トランザクション独立性レベル:
★取引隔離レベル:

READ-UNCOMMITTED:未読-->汚読;

READ-COMMITTED:読み上げ-->繰り返し不可

REPEATABLE-READ:繰り返し読み-->幻読み;(デフォルトレベル)

SERIALIZABLE:シリアル化;

★物事の隔離レベルの表示と設定:

mysql> SELECT @@session.tx_isolation;

mysql> SET @@session.tx_isolation='...'

プレゼンテーション:
1.READ-UNCOMMITTED:未読-->汚い読み;すなわち、node 1とnode 2の2つのセッションが同じテーブルの変更に同時にアクセスすると、node 1ユーザーの物事がまだ提出されていないため、node 2ユーザーは見ることができ、結果は汚い読み取り(他の人が提出できなかったデータを見た)であり、以下のようになる.
1)node 1セッションで、独立性レベルを読み取り未送信に変更し、物事を開始し、1行のデータを変更し、ロールバックする

MariaDB [hellodb]> SELECT @@tx_isolation;  #       
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> SET @@session.tx_isolation='READ-UNCOMMITTED'; #            
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> START TRANSACTION; #     
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> UPDATE courses SET Course='nginx' WHERE CourseID=4; #     
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | nginx          |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> rollback;  
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from courses; #        
+----------+----------------+ 
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

2)node 2セッションでは、何もしておらず、前後2回のレビューでデータが変化していることがわかりますので、読み取り未送信で他の人がコミットできなかったデータを見ることができます

MariaDB [hellodb]> set @@session.tx_isolation='READ-UNCOMMITTED'; #              
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT @@tx_isolation；
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> start transaction; #       
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from courses; # node1         ，         nginx
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | nginx          |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.01 sec)

MariaDB [hellodb]> select * from courses;  # node1         ，           
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

2.READ-COMMITTED:読み出し-->繰り返し読み不可;すなわち、node 1とnode 2の2つのセッションが同じテーブルの変更に同時にアクセスすると、node 1ユーザの物事はコミットされ、node 2ユーザのみが見ることができるが、node 2ユーザはデータを変更していない.したがって、node 2セッションを繰り返し読むと、毎回の結果が異なる可能性があります.以下のようにします.
1)node 1セッションでは、独立性レベルを読み取りコミットに変更し、物事を開始し、1行のデータを変更し、コミットします.

MariaDB [hellodb]> set @@session.tx_isolation='READ-COMMITTED'; #          
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT @@tx_isolation； 
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> start transaction;  #     
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> UPDATE courses SET Course='Nagios' WHERE CourseID=12; #     
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Nagios         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> COMMIT;  #     
Query OK, 0 rows affected (0.01 sec)

2)node 2セッションでは、node 1セッションがデータをコミットしないまで、node 2が表示したデータは変更されず、node 1セッションがデータをコミットした後、node 2はデータが変更されたことを発見したが、node 2は何もしなかった

MariaDB [hellodb]> set @@session.tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses; #  node1         ，     
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Zabbix         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> SELECT * FROM courses; # node1       ，          
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|       12 | Nagios         |
|       13 | Puppet         |
+----------+----------------+
9 rows in set (0.00 sec)

MariaDB [hellodb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

3.REPEATABLE-READ:繰り返し可能-->幻読みであり、mysqlとmariadbのデフォルトの実行レベルでもあります.つまり、2つのセッションはそれぞれ独立したトランザクションです.Node 1とnode 2セッションはトランザクションを確立するだけで、初めてデータを表示した結果は物事全体の過程を貫き、期間の下位データの変化は見えず、物事が終わった後にしかデータの変化を見ることができず、このように直接招いた結果は幻読であり、つまり各セッションから見たデータは真実のデータではなく、下位データはすでに変化した可能性がある.

Twelve Factor APPについてのまとめ（後半）

go:gin優雅にサービスを終了