Mysql Programming CS 155 Pノート(六)Transactions

Mysql Programming CS 155 Pノート(六)Transactions
Transactions
A transaction is a set of statements all of which need to complete or none of which should complete. 
In many complex cases, it's impossible to do everything you need in a single SQL statement.
Lets look at a couple examples:
SHOPPING CART Checkout transaction:In our shopping cart, when a customer wants to checkout and buy all the items in their cart, a series of things need to happen effectively all at the same time.  For simplicity sake, lets say that we aren't allowed to sell more inventory than we have (no backorders or online download [unlimited inventory]):
We've collected all the billing and shipping and payment info.  We now need to do the following:

store the payment confirmation

reduce the inventory of each item

add the purchase to the users transaction history

identify customers to the vendors for warranty information or customer notifications

clear the users shopping cart

This is likely done via a series of SQL statements.. at least one per task, but some tasks may have multiple queries.  In any case, if any of these steps doesn't work (we ran out of inventory, for example), we don't want any of these queries to actually change database info (we certainly shouldn't clear the cart--although we may want to remove the out of inventory item or mark it as unavailable in the users cart), we don't want to add this to the transaction history, we shouldn't reduce inventory of other items since we aren't completing this transaction, and we should reverse the payment transaction (ideally this is done via a hold which is lifted rather than a purchase that is reversed, but that's out of scope for this class).
Without transactions, out software would have to keep track of all the queries executed and when it ran into a problem, we'd have to run queries to undo all the work we just did.
In the transactions world, we can simply roll back the transaction and all pending DB changes would be undone.    Similarly, if we're happy and everything is fine, we can commit the changes and all tables will be updated effectively simultaneously.
Transaction commit and rollback can be included in procedures/functions as well.
Here's an example:
 

DELIMITER $$
 
CREATE PROCEDURE `sp_fail`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION; //    
    INSERT INTO `tablea` (`date`) VALUES (NOW());
    INSERT INTO `tableb` (`date`) VALUES (NOW());
    INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL
    IF `_rollback` THEN
        ROLLBACK; //  
    ELSE
        COMMIT;
    END IF;
END$$
 
DELIMITER

 
START TRANSACTION、COMMIT、ROLLBACK構文

START TRANSACTION | BEGIN [WORK]

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

SET AUTOCOMMIT = {0 | 1}

デフォルトでは、MySQLはautocommiteモードで動作します.これは、テーブルの文を更新または変更すると、MySQLはすぐにハードディスクに格納されます.InnoDBまたはNDBエンジンを使用すると、この自動保存オプションをオフにすることができます.SET AUTOMMIT=0です.もう1つの方法はstart transactionの使用を宣言することです.このstatement.
In this example, for whatever reason, the insert into table c should fail (perhaps it doesn't have a date column).   our continue handler sets the variable _rollback to true, so then we rollback the transaction.  Otherwise, we commit the transaction.
Since the first 2 queries were successful, rollback will do the work for us of removing the rows that were added.
In the "resources"for this weeks section is a 7 minute youtube clip on MySQL Transactions, please watch it.
There are also 2 tutorials from different sources.  I suggest you review at least one of them.
Why Transactions?
The fundamental desire for a database is ACID compliance:
ACID −

Atomicity − This ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

Consistency − This ensures that the database properly changes states upon a successfully committed transaction.

Isolation − This enables transactions to operate independently on and transparent to each other.

Durability − This ensures that the result or effect of a committed transaction persists in case of a system failure.

A single query will lock the database so that all it's additions/changes/deletions are completed before another query can update or read data from the same table.
一般に、トランザクションは4つの条件(ACID):原子性(Atomicity、または分割不可能性)、一貫性(Consistency)、隔離性(Isolation、独立性)、持続性(Durability)を満たさなければならない.

原子性:トランザクション内のすべての操作は、すべて完了するか、すべて完了しないか、中間の一環で終了しません.トランザクションは実行中にエラーが発生し、このトランザクションが実行されたことがないように、トランザクションが開始される前の状態にロールバックされます.

一貫性:トランザクションが開始される前とトランザクションが終了した後、データベースの整合性は破壊されません.これは、書き込まれた資料がすべてのプリセット規則に完全に合致しなければならないことを示しています.これは、資料の精度、直列性、および後続のデータベースが自発的に所定の作業を完了できることを含む.ネットショッピングについて言えば、商品を出庫させ、商品を顧客のショッピングバスケットに入れることで事務を構成することができます.

独立性:データベースは、複数の同時実行トランザクションが同時にデータの読み書きと変更を行うことを可能にし、独立性は、複数のトランザクションが同時実行されるときに交差実行によってデータの不一致を防止します.トランザクション・アイソレーションは、リード・コミット(Read uncommitted)、リード・コミット(read committed)、リピート可能リード(repeatable read)、シリアル化(Serializable)など、異なるレベルに分けられます.

持続性:トランザクションが終了すると、データの変更は永続的であり、システム障害が発生しても失われません.正常に実行されたトランザクションは、データベースに永続的な役割を果たします.データベースに障害が発生した場合でも、

をリカバリできるはずです.
It would be bad if one query was changing 1 million rows and another query were reading those rows at the same time.  The one reading may not get the correct state of all the rows if some change before it read their data and after it's read other rows data.  With transactions it's important that changes to all tables relative to the are executed simultaneously so they won't affect other queries or transactions.
For this reason, it's important to be aware of the impact your transactions will have on the DB when they're executed.   If you have a transaction that will take a very long time to commit, many tables may be locked for a long time preventing other transactions or queries from executing. 
You may have noticed certain sites you visit might operate more slowly late at night than they do during the day.  In many cases, like your bank, for example, they run large batch process which can sometimes consist of bulky transactions and you're query to view your account statement may have to wait until they finish processing the transactions relative to your account from the past day.
Gotcha's
When using transactions it's very important to be aware of things that might perform actions when you didn't intend them.
Some statements cannot be rolled back, while others force an implicit commit. 
Any transaction with statements which create, drop, or alter a DB cannot be rolled back.   These really shouldn't be part of transactions but there are some use cases where this is done. (create,drop,alterというstatmentsはrolled backができることに注意してください)
Since this statement can't be rolled back, any statements issued up to that point also can't be rolled back.  Those same statements force an implicit commit to all the transactions that happened before the statement.  
Similarly, drop, alter and create functions statements force an implicit commit and cannot be rolled back.
Be sure to understand the statements which cause an implicit commit.  Read the MySQL Documentation on implicit commit (Links to an external site.)Links to an external site..
A good rule of thumb is, if the query relates to "data"then it can be rolled back, if it relates to structures or code or metadata then it likely can't be rolled back and would also force an implicit commit.トランザクションテスト:

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  #      
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  #     
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; #     
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    #     
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   #   
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   #             
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;
 
 
https://blog.csdn.net/u010412719/article/details/51147292
posted on
2018-04-25 07:42 ecwork読書(
...) コメント(
...) コレクションの編集
転載先:https://www.cnblogs.com/ecwork/p/8939304.html