To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the use of functional cookies. For more information read this page.

SQL transactions

Most database management systems offer some form of transaction. Transactions are very useful when multiple commands need to be executed.

What a transaction is

Transactions are a huge part of ecommerce. A transaction follows four simple properties known as ACID:

Atomicity
If a transaction is to be processed, it is processed as one. If one small part of a transaction fails, the whole transaction fails. The database is guaranteed to go back to it's state before the transaction happened.
Consistency
Any transactions made will bring the database from one state to another valid state. Data inserted must follow the defined rules of the program.
Isolation
Every transaction is isolated from the next. If a transaction is completed concurrently then the database must act as if they were being performed sequentially. This means that the result data includes both of the results of the transactions.
Durability
Durability ensures that all transactions have been commited and stored once they are finished. In the event of a power loss, the database is not lost and the transactions are still stored.

SQL provides the concept of transactions and follows these rules.

Transactions in SQL

SQL
START TRANSACTION;
DELETE FROM `Attendees` WHERE forename = "Jane";
		

If the transaction fails it is easy to reset the database to it's former state. This is done with a rollback. SQL provides an easy way to do this using the ROLLBACK keyword.

SQL
ROLLBACK
		

A rollback will set the database back to the exact way it was before the transaction took place.

Provide feedback on this page
Comments are sent via email to me.