Before you access data stored in a database, it is important to understand several database concepts, including:
A database commit occurs when you make a permanent change to a database. For example, when you write a new row to a database, the write does not occur until the database commits the change.
Rollback is the process of undoing a change to a database. For example, if you write a new row to a table, you can rollback the write up to the point where you commit the write. After the commit, you can no longer rollback the write.
Most databases support transactions where a transaction consists of one or more SQL statements. Within a transaction, your SQL statements can read, modify, and write a database. You end a transaction by either committing all your changes within the transaction or rolling back all of them.
Transactions can be useful when you have multiple writes to a database and want to make sure all writes occurred without error before committing them. In this case, you wrap all writes within a single transaction and check for errors after each write. If any write causes an error, you rollback all of them. If all writes occur successfully, you commit the transaction.
A bank might use a transaction to encapsulate a transfer from one account to another. For example, if you transfer money from your savings account to your checking account, you do not want the bank to debit the balance of your savings account unless it also credits your checking account. If the update to the checking account fails, the bank can rollback the debit of the savings account as part of the transaction.
ColdFusion includes the cftransaction tag that lets you implement database transactions for controlling rollback and commit. For more information, see the CFML Reference.
From this basic description, the following database design rules emerge:
The best way to familiarize yourself with the capabilities of your database product or database management system (DBMS) is to review the product documentation.