Database Transactions


A database transaction is a series of SQL queries that should be executed without error. If the queries execute without error, the related table changes are saved and the transaction is considered successful.
If an error occurs with any SQL query in the transaction, all changes are lost and the database will remain in the state it was before the transaction was executed.


Database transactions are known as atomic operations, where multiple queries are used to create one unit of work. With a database transaction, either all queries in the transaction are executed without error, or none of the queries are executed.

One main reason to have transactions in a relational database is based on having data in multiple tables.
For example, let's assume we have the following tables defined to hold the details of a Customer:

Customer: FirstName, LastName, Age, CustomerType, ContactId
Contact: ContactId, AddressOne, AddressTwo, City, State, ZIP
ContactAddress: ContactId, Phone, Fax, Email

If a new customer was being added to the database then all three tables need to be filled out or the customer record will not be correct. Using a transaction with multi-line SQL statements would look like this:

BEGIN TRANSACTION
;
INSERT INTO Contact (ContactId, AddressOne, AddressTwo, City, State, ZIP) VALUES(12314, '724 Evergreen Terrace','','Springfield','GG',11111)
;
INSERT INTO Customer (FirstName, LastName, Age, CustomerType, ContactId) VALUES ('Bob', 'Roberts', 54, 2, 12314)
;
INSERT INTO ContactAddress (ContactId, Phone, Fax, Email) VALUES (12314, '333-222-4444', '', 'foo@goo.com')
;
END TRANSACTION
;


If this SQL was used, and all the INSERT statements were executed without error, then the new customer would have been successfully added to the database.
If one or more of the INSERT statements generates an error then the changes are 'rolled back' to the state before the transaction was issued, and no changes are made to the database.

 
 

DBLX SQL Guide