SQL must-Know notes Chapter 20th Management transaction Processing

20.1 Transaction Processing

Transaction processing (transaction processing) can be used to maintain the integrity of a database, which guarantees that a batch of SQL operations is either fully executed or not executed at all.
Relational database design stores data in multiple tables, making data easier to manipulate, maintain, and reuse. There is no need to delve into how and why relational database design, to some extent, well-designed database schema are related.
A transaction (transaction) refers to a set of SQL statements.
Fallback (rollback) refers to the process of revoking a specified SQL statement.
Commit (Commit) writes the result of an unsaved SQL statement to a database table.
A reserved point (savepoint) is a temporary placeholder (placeholder) that is set in a transaction, and you can publish a fallback to it (unlike fallback the whole thing).
Can I fallback those statements?
object handling is used to manage insert, UPDATE, and DELETE statements. The SELECT statement cannot be rolled back, nor can the create or drop operation be rolled back. These statements can be used in transactions, but they are not revoked when they are rolled back.

20.2 Control Transaction Processing

Differences in the implementation of things: different syntaxes are used to implement the processing of things differently.
The key to managing transactions is to break down the group of SQL statements into logical blocks and specify when the data should be rolled back and when it should not be rolled back.

BEGIN TRANSACTION……COMMIT TRANSACTION

20.2.1 using rollback

SQL ROLLBACK command is used to fallback SQL statements

DELETE FROM Order；ROLLBACK；

20.2.2 using Commit

The general SQL statements are executed and written directly against the database tables. This is called an implicit commit, that is, the commit operation is automatic. However, in a transaction block, commits are not suppressed.
Examples in SQL Server

BEGIN TRANSACTIONDELETE OrderItems WHERE order_num = 12345DELETE Orders Orders WHERE order_num = 12345COMMIT TRANSACTION;

Examples in Oracle

DELETE OrderItems WHERE order_num = 12345;DELETE Orders Orders WHERE order_num = 12345;COMMIT;

Remove the full order from the system 12345. Because it involves updating two database tables, the object handles the block to ensure that the order is not partially deleted. The last Commit statement writes the change only when there is no error. If the first delete works, but the second fails, delete does not commit.

20.2.3 using a retention point

Simple rollback and commit statements can write or undo the whole thing. However, just for simple transactions to do this, more complex transactions may require partial commit or fallback.
In order to support fallback partial transactions, placeholders must be placed in the appropriate location in the transaction module. This allows fallback to a placeholder if fallback is required.
To create placeholders in MySQL and Oracle, use the following savepoint statement:

SAVEPOINT delete1;ROLLBACK TO delete1；

The more you keep, the better: You can set as many reservations as you like in your SQL code, and the more the better, what's the point ? Because the more points you keep, the more flexible you will be to roll back as you wish.

SQL must-Know notes Chapter 20th Management transaction Processing