2 cissp ® Official Study Guide Eighth Edition

Database Transactions
Relational databases support the explicit and implicit use of transactions to ensure data 
integrity. Each transaction is a discrete set of SQL instructions that will either succeed or 
fail as a group. It’s not possible for one part of a transaction to succeed while another part 
fails. Consider the example of a transfer between two accounts at a bank. You might use 
the following SQL code to first add $250 to account 1001 and then subtract $250 from 
account 2002:
BEGIN TRANSACTION
UPDATE accounts
SET balance = balance + 250
WHERE account_number = 1001;

---

900
Chapter 20 
■
Software Development Security
UPDATE accounts
SET balance = balance – 250
WHERE account_number = 2002
END TRANSACTION
Imagine a case where these two statements were not executed as part of a transaction 
but were instead executed separately. If the database failed during the moment between 
completion of the first transaction and completion of the second transaction, $250 would 
have been added to account 1001, but there would be no corresponding deduction from 
account 2002. The $250 would have appeared out of thin air! Flipping the order of the two 
statements wouldn’t help—this would cause $250 to disappear into thin air if interrupted! 
This simple example underscores the importance of transaction-oriented processing.
When a transaction successfully finishes, it is said to be committed to the database and 
cannot be undone. Transaction committing may be explicit, using SQL’s 
COMMIT
command, 
or it can be implicit if the end of the transaction is successfully reached. If a transaction 
must be aborted, it can be rolled back explicitly using the 
ROLLBACK
command or implicitly 
if there is a hardware or software failure. When a transaction is rolled back, the database 
restores itself to the condition it was in before the transaction began.
All database transactions have four required characteristics: atomicity, consistency, iso-
lation, and durability. Together, these attributes are known as the 
ACID model
, which is a 
critical concept in the development of database management systems. Let’s take a brief look 
at each of these requirements: