Transaction;

• A sequence of database requests that accesses the database
• A is a logical unit of work; i.e., it must be entirely completed or aborted- no intermediate ending states are accepted
• All transactions must have the properties of atomicity, consistency, isolation, and durability

Concurrent transactions

• Many transactions taking place at the same time

Concurrency control

• Managing the execution of concurrent transactions

Consistent database state

• A database state in which all data integrity constraints are satisfied

Database request

• The equivalent of a single SQL statement in an application program

Atomicity

• requires that all operations (SQL requests) of a transaction be completed; if not, the transaction is aborted i.e., a transaction is treated as a single, indivisible, logical unit of work

Consistency

• indicates the permanence of the database's consistent state
• A transaction takes a database from one consistent state to another. Once a transaction is completed, the database must be in a consistent state

Isolation

• means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed
• This property is particularly useful in multiuser database environments because several users can access and update the database at the same time

Durability

• ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure

Serializability

• A property in which the selected order of concurrent transaction operations creates the same final database state that would have been produced if the transactions had been executed in a serial fashion
• i.e., ensures that the schedule for the concurrent execution of the transactions yields consistent results
• This property is important in multiuser and distributed databases in which multiple transactions are likely to be executed concurrently

A COMMIT statement is reached, in which case all changes are permanently recorded within the database

• The COMMIT statement automatically ends the SQL transaction

A ROLLBACK statement is reached, in which case all changes are aborted and the database is rolled back to its previous consistent state

The end of a program is successfully reached, in which case all changes are permanently recorded within the database

• This action is equivalent to COMMIT

The program is abnormally terminated, in which case the database changes are aborted and the database is rolled back to its previous consistent state

• This action is equivalent to ROLLBACK

Transaction log- A feature used by the DBMS to keep track of all transaction operations that update the database. The information stored in this log is used by the DBMS for recovery purposes

The transaction log stores the following

• A record for the beginning of the transaction
• Each transaction component (SQL statement):
• The type of operation being performed (INSERT, UPDATE, DELETE)
• The names of the objects affected by the transaction (the name of the table)
• The before and after values for the fields being updated
• Pointers to the previous and next transaction log entries for the same transaction
• The ending (COMMIT) of the transaction

Concurrency control- refers to coordinating the simultaneous execution of transactions in a multiuser database system

The objective of concurrency control is

• to ensure the serializability of transactions in a multiuser database environment

To achieve this goal;

• most concurrency control techniques are oriented toward preserving the isolation property of concurrently executing transactions
• Concurrency control is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems

The 3 main problems;

1. lost updates
2. uncommitted data
3. inconsistent retrievals

The lost update problem occurs when two concurrent transactions, T1 and T2, are updating the same data element and one of the updates is lost (overwritten by the other transaction)

The phenomenon of uncommitted data occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data - thus violating the isolation property of transactions

Inconsistent retrievals occur when a transaction accesses data before and after one or more other transactions finish working with such data

For example, an inconsistent retrieval would occur if transaction T1 calculated some summary (aggregate) function over a set of data while another transaction (T2) was updating the same data

The problem is that the transaction might read some data before it is changed and other data after it is changed, thereby yielding inconsistent results

Scheduler

• The DBMS component that establishes the order in which concurrent transaction operations are executed
• The scheduler interleaves the execution of database operations in a specific sequence to ensure serializability

Serializable schedule

• In transaction management, a schedule of operations in which the interleaved execution of the transactions yields the same result as if they were executed in serial order

Locking Methods

• Locks; Binary & Shared/exclusive locks
• Two-Phase Locking
• Deadlocks

Time Stamping Methods

• Wait/Die schemes
• Wound/Wait schemes

Optimistic Methods

• Using an optimistic approach, each transaction moves through two or three phases, referred to as read, validation, & write

Read on

Database recovery management e.g. Transaction Recovery

Queries about this Lesson, please send them to: jmwaura@jkuat.ac.ke

*References*

• Database Systems: Design, Implementation, and Management, 12th ed. Carlos Coronel & Steven Morris
• Database Modeling and Design; Logical Design, 5th ed. Taby Teorey et.al
• Fundamentals of database systems, 6th ed. Ramez Elmasri & Shamkant B. Navathe