Properties of Transaction in Database

What Is Transaction In Database?

In a database context, a transaction refers to a sequence of one or more database operations that are treated as a single unit of work. These operations can involve reading, writing, or modifying data within the database. Transactions are essential to maintain data integrity and consistency, ensuring that the database remains in a valid state even in the presence of failures or concurrent access by multiple users.

What Does A Transaction Do?

• Returns information from the database. Read the 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 the 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 A Transaction

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. Students 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 transactions 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 the 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 Scatter references to constraints throughout application and difficult to maintain as transactions are modified/added
• 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.
• The 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. Nonstop 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 been 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., CPUs, 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.