MySQL transaction processing example explanation

mysql transaction processing

Not all engines support transaction processing As mentioned in Chapter 21, MySQL supports several basic database engines. As discussed in this chapter, not all engines support explicit transaction management. MyISAM and InnoDB are the two most commonly used engines. The former does not support explicit transaction management, while the latter does. This is why the sample tables used in this book were created to use InnoDB rather than the more commonly used MyISAM. If your application requires transaction processing capabilities, be sure to use the correct engine type.

Transaction processing can be used to maintain the integrity of the database. It ensures that batches of MySQL operations are either completely executed or not executed at all.

Relational database design stores data in multiple tables, making the data easier to manipulate, maintain, and reuse. Without getting into the how and why of relational database design, well-designed database schemas are all relational to some extent.

The orders table used before is a good example. Orders are stored in two tables, orders and orderitems: orders stores the actual order, while orderitems stores the items ordered. The two tables are related to each other using a unique ID called the primary key. These two tables are in turn related to other tables containing customer and product information.

The process of adding an order to the system is as follows:

(1) Check whether the corresponding customer exists in the database (query from the customers table), if not, add him /she.

(2) Retrieve the customer's ID.

(3) Add a row to the orders table and associate it with the customer ID.

(4) Retrieve the new order ID assigned in the orders table.

(5) Add a row to the orderitems table for each item ordered, and associate it with the orders table by retrieving the

ID (and associate it with the products table by the product ID).

Now, suppose that some kind of database failure (such as disk space exceeded, security restrictions, table locks, etc.) prevents the completion of this process. What happens to the data in the database?

If the failure occurs after the customer is added and before the orders table is added, there will be no problem. It is perfectly legal for some customers to not have orders. When the process is re-executed, the inserted customer record will be retrieved and used. You can effectively start the process from where it went wrong.

But what if the failure occurs after the orders row is added but before the orderitems row is added? Now, there is an empty order in the database.

Even worse, if the system fails in the middle of adding the orderitems line. The result is that there are incomplete orders in the database and you don't know about it.

How to solve this problem? Here you need to use transaction processing. Transaction processing is a mechanism used to manage MySQL operations that must be performed in batches to ensure that the database does not contain incomplete operation results. With transaction processing, you can guarantee that a set of operations will not be stopped midway, and that they will either be executed as a whole or not at all (unless explicitly instructed to do so). If no errors occur, the entire set of statements is committed (written) to the database table. If an error occurs, roll back (undo) to restore the database to a known and safe state. So, look at the same example, this time we illustrate how the process works.

(1) Check whether the corresponding customer exists in the database, if not, add him/her.

(2) Submit customer information.

(3) Retrieve the customer's ID.

(4) Add a row to the orders table.

(5) If a failure occurs while adding rows to the orders table, fall back.

(6) Retrieve the new order ID assigned in the orders table.

(7) For each item ordered, add a new row to the orderitems table.

(8) If a failure occurs when adding new rows to orderitems, roll back all added orderitems rows and orders rows.

(9) Submit order information.

When using transactions and transaction processing, there are several key words that appear repeatedly.

1. Transaction refers to a set of SQL statements;

2. Rollback refers to The process of undoing the specified SQL statement;

3. Submit (commit) refers to writing the unstored SQL statement results into the database table;

4. Savepoint (savepoint) refers to the setting during transaction processing A temporary place-holder to which you can issue a rollback (as opposed to rolling back the entire transaction).

【Related recommendations】

1.

2.

3.

4.

5.

The above is the detailed content of MySQL transaction processing example explanation. For more information, please follow other related articles on the PHP Chinese website!