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
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.
ROLLBACK
A rollback will set the database back to the exact way it was before the transaction took place.