Transaction Control
<< CSQL Data Types - TOC - Getting Started With CSQL >>
A transaction is a unit of work that may consist of one or more related SQL statements. A transaction is called atomic as the database modifications by the SQL statements that constitute the transactions can be: Either made permanent to the database or, undone from the database
The changes made to a table, using INSERT, UPDATE, or DELETE statements are not permanent till a transaction is not marked as complete.
During a session, a transaction begins when the first SQL command (DDL or DML) is encountered and ends one of the following occurs,
- A DDL command encountered
- COMMIT/ROLLBACK statement encountered
- System failure
Committing Transaction
Committing a transaction makes permanent the changes resulting from all successful SQL statements in a transaction. When a transaction is completed, the following events take place: The changes made by the current transaction are made permanent The locks acquired by the transaction are released The transaction is marked as “complete”
Syntax: COMMIT;
Automatic Transaction Commit
The AUTOCOMMIT environment variable can be set to execute COMMIT automatically whenever an INSERT, UPDATE, or DELETE statement is executed. By default this variable is set to ON in CSQL.
Syntax: SET AUTOCOMMIT ON;
If the user wants to commit or rollback a transaction at the end after executing set of SQL statements or bulk INSERT SQL statements, then auto commit option should be set to OFF.
Syntax: SET AUTOCOMMIT Off;
Rolling Back Transaction
Changes made to the database using SQL statements, may be abandoned using the ROLLBACK statement. When a transaction is roll backed, it is as if the transaction never occurred and following events take place, Any changes made to the database is undone The locks acquired by the transaction are released The transaction is ended
Syntax: ROLLBACK;
Rolling back is useful for two reasons; first if you make a mistake like deleting the wrong row from a table, rollback restores the original data. Second, if you start a transaction that you can not finish because an SQL statement has failed, the rollback let you return to the starting point to take corrective action and try again.
<< CSQL Data Types - TOC - Getting Started With CSQL >>