Transaction Processing

Transaction Processing

It is normal to group one or more database operations into a transaction, which is a unit of work that must be carried out atomically and in obvious isolation from other transactions. In addition, a DBMS offers the guarantee of durability: that the work of a completed transaction will never be lost. The transaction manager therefore accepts transaction commands from an application, which tell the transaction manager when transactions begin and end, as well as information about the expectations of the application (some may not wish to require atomicity, for example). The transaction processor performs the following tasks:

1. Logging: In order to assure durability, every change in the database is logged separately on disk. The log manager follows one of several policies designed to assure that no matter when a system failure or "crash" occurs, a recovery manager will be able to examine the log of changes and restore the database to some consistent state. The log manager at first writes the log in buffers and negotiates with the buffer manager to make sure that buffers are written to disk (where data can survive a crash) at proper times.

2. Concurrency control: Transactions must appear to carry out in isolation. But in most systems, there will in truth be many transactions executing at once. Thus, the scheduler (concurrency-control manager) must assure that the individual actions of various transactions are completed in such an order that the net effect is the same as if the transactions had in fact executed in their entirety, one-at-a-time. A typical scheduler does its work by keeping locks on certain pieces of the database. These locks prevent two transactions from accessing the same piece of data in ways that interact badly. Locks are generally stored in a main-memory lock table, as suggested by following figure. The scheduler affects the execution of queries and other database operations by stopping the execution engine from accessing locked parts of the database.

3. Deadlock resolution: As transactions compete for resources through the locks that the scheduler grants, they can get into a situation where none can continue because each needs something another transaction has. The transaction manager has the responsibility to interfere and cancel ("roll- back" or "abort") one or more transactions to let the others proceed.

The ACID Properties of Transactions

Properly executed transactions are commonly said to meet the "ACID test", where:

● "A" stands for "atomicity", the all-or-nothing execution of transactions.

● "I" stands for "isolation", the fact that each transaction must appear to be executed as if no other transaction is executing at the same time.

● "D" stands for "durability", the condition that the effect on the database of a transaction must never be lost, once the transaction has completed.

The remaining letter, "C", stands for "consistency". That is, all databases have consistency constraints or expectations about relationships among data elements (e.g., account balances may not be negative). Transactions are expected to preserve the consistency of the  database. We discuss the expression of consistency constraints in a database schema in “Constraints and Triggers”, while "Serial and Serializable Schedules" begins a discussion of how consistency is maintained by the DBMS

Transaction Processing