MariaDB SQL Management thing handling

form

1. Customers table: Customer.
2. Orders table: Orders for customers.
3. OrderItems: Stores the various items ordered by the order.

Scenario ( No transactions used )

1. Check that the customer is in the database (that is, in the Customers table). If not, add the customer.
2. Retrieves the customer ID.
3. Add a row to the Orders table. Associate it to the customer ID.
4. Retrieves the ID of the new order in the Orders table.
5. For each ordered item, add a line to the OrderItems table and associate it to the Orders table by the retrieved ID.

problem

Assume that there are some database failures or other causes of database failure, so that this complete step can not be completed.

The failure occurs in:

1. After the customer is added, and before the form is added. Such a situation is permissible because the customer has no order to be legal.
2. After the orders line is added, and before the OrderItems line is added. The database will have an empty order.
3. When adding orderitems lines. The database will produce an incomplete order.

What is a database transaction?

1. Database transactions: A limited sequence of database operations . (Wikipedia)
2. Transaction (Transaction)--A set of SQL statements. ("MariaDB SQL")
3. Transaction processing (Transaction processing): a mechanism for managing a collection of database operations that guarantees that a batch is completed to ensure that the database never contains the results of a partial operation, that is, either all executes or is not executed at all. ("MariaDB SQL")

how to deal with? ( using Transactions )

1. Check whether the customer is in the database. If not, add the customer.
2. Submit customer information.
3. Retrieves the customer ID.
4. Add a row to the Orders table.
5. If a failure occurs while adding a row to the Orders table, the rollback is rolled back.
6. Retrieves the ID of the new order in the Orders table.
7. For each item ordered, add a line to the OrderItems table.
8. If an error occurs while adding rows to OrderItems, all added ordertimes rows and orders rows are rolled back.
9. Submit Order Information

 Other Concepts

• Rollback (Rollback): revokes the processing of the specified SQL statement.
• Commit: Writes a database statement that has not yet been saved to the database table.
• Save Point (savepoint): A temporary placeholder in the transaction set that can be rolled back.

Part of the code:

SELECT *  from  TRANSACTION; DELETE  from Ordertotales; SELECT *  from Ordertotales; ROLLBACK ; SELECT *  from OrderTotals;

savepoint delete1; ROLLBACK  to Delete1;

Setting MARIADB cannot be submitted automatically:

SET autocommit=0;

more major themes to be continued

1. What is the most important core of the database?

2. Database integrity

3. Database transactions have four features that are customarily called acid Properties

4. What is a deadlock? Optimistic lock?

MariaDB SQL Management thing handling