Properties of Transaction in Database

Let me start this article with the definition of transaction:

• Many businesses use databases to store information about their state.
  - e.g., balances of all depositors at a bank.
• When an event occurs in the real-world that changes the state of the business, a program is executed to change the database state in a corresponding way.
  - e.g., the bank balance must be updated when a deposit is made.
• That is called a transaction.

A Transaction does the following things:

• Returns information from the database.
  - Read client's balance in the database and output it, when the Request Balance Transaction is performed.
• Occurrence of a real-world event update database.
  - Deposit transaction: update customer's balance in the database.
• Cause the occurrence of a real-world event.
  - Withdraw transaction: dispense cash (and update the customer's balance in the database).
• The execution of each transaction must maintain the relationship between the database state and the business state.

Properties of transactions
 

Atomicity (A)

• Either all or none of the transactions of the real-world event are performed.
  - Student either registers or does not register
  Similarly, the system must ensure that either the corresponding transaction runs to completion or, if not, it has no effect at all.

Database consistency

• Business Rules limit the occurrence of certain real-world events.
  - Student cannot register for a course if the current number of registrants equals the maximum allowed.
• Correspondingly, allowable database states are restricted.
  cur_reg <= max_reg.
• These limitations are called (static) integrity constraints: assertions that must be satisfied by all database states.
• Other static consistency requirements are related to the fact that the database might store the same information in various ways.
• cur_reg = |list_of_registered_students|
• Such limitations are also expressed as integrity constraints.
  A database is consistent if all static integrity constraints are satisfied.

Transaction consistency

• A consistent database state does not necessarily model the actual state of the business.
  - A deposit transaction that increments the balance by the wrong amount maintains the integrity constraint balance ³ 0, but does not maintain the relation between the enterprise and database states.
• A consistent transaction maintains database consistency and the correspondence between the database state and the business state (implements its specification).
  Specification of deposit transaction includes.
  Balance = balance + amt_deposit (balance is the next value of balance).

 Dynamic integrity consistency

• Some constraints restrict allowable state transitions.
  - A transaction might transform the database from one consistent state to another, but the transition might not be permissible.
  - Example: A letter grade in a course (A, B, C, D, F) cannot be changed to an incomplete (I).
• Dynamic constraints cannot be checked by examining the database state.

Consistency (C)

• A transaction must preserve the consistency of a database after the execution.
• Consistent transaction: if the database is in a consistent state initially, then the transaction completes:
  - All static integrity constraints are satisfied (but constraints might be violated in intermediate states)
  1. Can be checked by examining a snapshot of the database
  - The new state satisfies the specifications of the transaction
  2. Cannot be checked from a database snapshot
  - No dynamic constraints have been violated
  3. Cannot be checked from a database snapshot
• Automatic: Embed constraint in the schema.
  - CHECK, ASSERTION for static constraints
  - TRIGGER for dynamic constraints
  - Increases confidence in correctness and decreases maintenance costs
  - Not always desirable since unnecessary checking (overhead) might result
• Deposit transaction modifies the balance but cannot violate constraint balance ³ 0
• Manual: Perform check in application code.
  - Only necessary checks are performed
  - Scatters references to constraint throughout application
  - Difficult to maintain as transactions are modified/added

Durability (D)

• Once a transaction commits, the system must guarantee that the result of its operations will never be lost, in spite of subsequent failures.

Implementing durability

• Databases are stored redundantly on mass storage devices to protect against media failure.
• Architecture of mass storage devices affects the type of media failures that can be tolerated.
• Related to availability: the extent to which a (possibly distributed) system can provide service despite failures.
  - Non-stop DBMS (mirrored disks).
  - Recovery based DBMS (log).

Isolation (I)

• The transaction must behave as if they are executed in isolation. It means that if several transactions are executed concurrently the result must be the same as if they were executed serially in the same order. The data used during the execution of the transaction cannot be used by the second transaction until the first one is completed.
• An interleaved schedule of transactions is isolated if its effect is the same as if the transactions had executed serially in the same order (serializable).
• It follows that serializable schedules are always correct (for any application).
• Serializable is better than serial from a performance point of view.
• DBMS uses locking to ensure that concurrent schedules are serializable.

Isolation in real-world

• SQL supports the SERIALIZABLE isolation level, which guarantees serializability and hence correctness for all applications.
• The performance of applications running as SERIALIZABLE is often not adequate.
• SQL also supports weaker levels of isolation with better performance characteristics.

Concurrent execution offers performance benefits:

• A computer system has multiple resources capable of executing independently (e.g., CPU's, I/O devices).
• A transaction typically uses only one resource at a time.
• Hence, only concurrently executing transactions can make effective use of the system.
• Concurrently executing transactions yield interleaved schedules.

FAQ: How to implement an ACID rule or model to the database e.g. Microsoft SQL or MySQL database?

It is not a model that you can implement. But you can use a commit and rollback to confirm that transactions are performed in a safe way.