Transactions are at the core of all database activity. When you use your credit card, or withdraw money at an ATM, or place an item into the cart on a web site, a transaction occurs. Database transactions have a clear starting and ending point. Once a transaction is completed it is guaranteed to remain in the database. This is known as data persistence. There are many facets to transaction management. If a transaction does not reach its end point, the DBMS will usually roll back the transaction to the starting point. Once a transaction completes a backup of the transaction is maintained on a storage medium other than the one where the database is stored. If the system hardware, software, or storage medium “crashes”, the backup can be used to recover all transactions.

One common way to maintain the integrity of data resulting from transactions, is strategic locking of affected data for each transaction. Optimistic concurrency control uses fewer locks, which allows more applications to run concurrently and potentially increases the throughput of the applications. This strategy attempts to obtain locks on the required resources only when the transaction completes.

Pessimistic concurrency control, on the other hand, implements a higher isolation level by placing locks on the database at the beginning of a transaction. In other words, pessimistic locking assumes there will be a collision in the database and takes precautions to prevent these collisions. A pessimistic locking strategy protects the integrity of the database by guaranteeing, from the beginning of the transaction, that no conflicting transaction can run concurrently.

For example, when booking a flight using an application that employs optimistic concurrency control, we run the risk of someone else grabbing the same seat before we can complete the transaction. This favors the airline because more seats stay available for a longer time to be selected by different potential customers who may be disappointed but will likely settle for a different seat if the one they wanted is no longer available by the time the complete the transaction of booking their flight. An example of a pessimistic concurrency control approach might be the sale of a home, which will be listed as “pending” while a contract is being negotiated and only become available again if that contract falls through.

One of the core principles in transaction management is concurrency control. Concurrency control refers to maintaining the integrity of a database that is being accessed by multiple users at the same time (concurrently). There are many issues that must be addressed to maintain database integrity during multi-user, concurrent access of a database.

In particular, all concurrent transactions must obey ACID properties. ACID is an acronym for atomicity, consistency, isolation, and durability.

answer the following:

• A description of a transaction and the ACID properties.
• Select one of the ACID properties of a transaction
  • Provide an example where that property is not satisfied.
  • Evaluate the impact on the integrity of the database of not satisfying the selected property.
  • Describe at least one way to guarantee that the selected property will be satisfied.