ACID properties

Transaction Properties

Transactions in RDBMSs must possess four important properties, symbolized by the acronym,

which stands for atomicity, consistency, isolation, and durability of transactions. Transaction

management, in general, means supporting database transactions so the ACID pACID roperties are

maintained.

Let’s look at the transaction properties in more detail:

• Atomicity: Either a transaction should be performed entirely or none of it should be

performed. That is, you can’t have the database performing only a part of a transaction. For

example, if you issue a SQL statement that should delete 1,000 records, your entire transaction

should abort (roll back) if your database crashes after the transaction deletes 999 records.

• Consistency: The database is supposed to ensure that it’s always in a consistent state. For

example, in a banking transaction that involves debits from your savings account and credits

to your checking account, the database can’t just credit your checking account and stop. This

will lead to inconsistent data, and the consistency property of transactions ensures that the

database doesn’t leave data in an inconsistent state. All transactions must preserve the

consistency of the database. For example, if you wish to delete a department ID from the

Department table, the database shouldn’t permit your action if some employees in the

Employees table belong to the department you’re planning on eliminating.

• Isolation: Isolation means that although there’s concurrent access to the database by

multiple transactions, each transaction must appear to be executing in isolation. The isolation

property of transactions ensures that a transaction is kept from viewing changes made by

another transaction before the first transaction commits. This property is upheld by the database’s

concurrency control mechanisms, as you’ll see in the following sections. Although

concurrent access is a hallmark of the relational database, isolation techniques make it

appear as though users are executing transactions serially, one after another. This chapter

discusses how Oracle implements concurrency control—the assurance of atomicity and isolation

of individual transactions in a concurrently accessed database.

• Durability: The last ACID property, durability, ensures that the database saves commit transactions

permanently. Once a transaction completes, the database should ensure that the

transaction’s changes are not lost. This property is enforced by the database recovery mechanisms,

which make sure that all committed transactions are retrieved. As you saw in

Chapter 5, Oracle uses the write-ahead protocol, which ensures that all changes are first

written to the redo logs on disk before they’re transferred to the database files on disk.