Controlling SQL Transactions

Starting a Transaction

BEGIN TRANSACTION; 

BEGIN TRANSACTION transaction_name; 

begin transaction new_account
2> insert CUSTOMERS values ("Frans Mukoya", "1285 Mbambi village", "Rundu, Namibia", 6);
3> if exists(select * from CUSTOMERS where Name = "Frans Mukoya")
4> begin
5> begin transaction
6> insert BALANCES values(1650.76, 1461.26, 8);
7> end;
8> else
9> rollback transaction;
10> if exists(select * from BALANCES where Account_ID = 8);
11> begin
12> begin transaction
13> insert ACCOUNTS values(8, 6);
14> end;
15> else
16> rollback transaction;
17> if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6)
18> commit transaction; 
19> else
20> rollback transaction;

• Start transaction with BEGIN TRANSACTION
• If something goes wrong roll back the changes made up to this point with ROLLBACK TRANSACTION
• If everything runs smoothly to the end, complete the transaction with COMMIT TRANSACTION , which saves the data to permanent storage.



COMMITING Transactions with server settings


One way to ensure that all transactions are committed / saved is to turn ON the server AUTOCOMMIT setting. That is done with the following statement:

SET AUTOCOMMIT {ON| OFF}; 

Note that you do not use the braces, you simply set AUTOCOMMIT to either ON or OFF as in

SET AUTOCOMMIT ON; 

to turn auto commit ON.


Transaction Savepoints


Save points serve as mini commits within a transaction. Say Now that you are in the transaction, but you have data that need to be saved if the transaction reaches that point so that if something goes wrong not all changes should be rolled back. This is where Save points comes into play. Take a look at the following example:


 1> SET TRANSACTION;
2> UPDATE BALANCES SET CURR_BAL = 2600 WHERE ACCOUNT_ID = 6;
3> SAVEPOINT save_point1;
4> DELETE FROM BALANCES WHERE ACCOUNT_ID = 6;
5> ROLLBACK TO SAVEPOINT save_point1;
6> ROLLBACK;
7> SELECT * FROM BALANCES;



Note what happens on line 3, we are establish a save point after the update (Data is saved here so that the next roll back stops here and does not go all the way to the beginning of the transaction. Notice that after deleting the balance for account number 6, we decided that we just deleted from a wrong account. We use line 5 to roll back that deletion, but only up to the last update. Line 6 rolls back the whole transaction, thus the values in that record goes back to what it was before the transaction started.