SQL transaction Processing

Database updates are usually caused by events that occur in the objective world. In order to ensure the consistency of the database content, a set of operations of the database should be carried out as a whole, either complete successfully or all fail out. If there is some completion in a set of operations due to a failure or other reason, and some unfinished, the data in the database is bound to be inconsistent, causing the integrity of the database to be compromised. Therefore, the update action sequence must appear as a whole when the DBMS executes, that is, "either all or none." SQL provides a mechanism for transaction processing to help the DBMS achieve these functions.

Transaction processing (TRANSACTION) is a logical processing unit formed by the combination of one or more SQL statement sequences. Each statement in a transaction is part of the entire task, and all the statements are organized together to accomplish a particular task. When a DBMS processes a statement in a transaction, it does so according to the following conventions, and that is, "all statements in a transaction are treated as an atomic unit of work, and all statements can be executed successfully or without any statement being executed." The DBMS is responsible for such a convention, even if the application exits unexpectedly in a transaction, or in various unexpected situations, such as a hardware failure. In any unexpected case, the DBMS is responsible for ensuring that the database contents never appear "executed in part of the transaction" after the system is restored to normal.

The SQL language provides two important statements for transactional processing, which are commit and rollback statements. They are used in the following formats:

COMMIT WORK

ROLLBACK WORK

A commit statement is used to tell Dmbs that the statement in the transaction was successfully executed. The database content will be complete when the execution is completed successfully. The rollback statement is used to tell the DBMS that the statement in the transaction cannot be executed successfully. At this point, the DBMS will revert to the changes made to the database during this transaction to revert to the state before the transaction process.

References: SQL language tutorials SQL language basics

Back to the database basics ABC Directory