Introduction to transaction management

Department of Information Technology 1Data base Technologies (ITB4201)
Introduction to Transaction concepts
Dr. C.V. Suresh Babu
Professor
Department of IT
Hindustan Institute of Science & Technology

Action Plan
• Transaction processing systems
• Introduction to TRANSACTION
• Need for TRANSACTION
• Operations
• Transaction Execution and Problems
• Transaction States
• Transaction Execution with SQL
• Transaction Properties
• Transaction Log
• Quiz

Transaction processing systems
• Transaction is a logical unit of work that represents real-world events of
any organisation.
• Transaction processing systems execute database transactions with large
databases and hundreds of concurrent users,
for example,
• railway and air reservations systems,
• banking system,
• credit card processing,
• stock market monitoring,
• super market inventory and
• checkouts and so on.

Introduction to TRANSACTION
• A transaction is a logical unit of work of database processing that includes one or more database
access operations.
• A transaction can be defined as an action or series of actions that is carried out by a single user or
application program to perform operations for accessing the contents of the database. The
operations can include
– retrieval (Read)
– insertion (Write)
– deletion and
– modification.
• A transaction must be either completed or aborted.
It can either be embedded within an application program or can be specified interactively via a high-
level query language such as SQL.
• Its execution preserves the consistency of the database.
Each transaction should access shared data without interfering with the other transactions and
whenever a transaction successfully completes its execution; its effect should be permanent.

Need for TRANSACTION
This basic abstraction frees the database application programmer
from the following concerns :
• Inconsistencies caused by conflicting updates from concurrent
users.
• Partially completed transactions in the event of systems failure.
• User-directed undoing of transactions.
• A transaction is a sequence of READ and WRITE actions that are
grouped together to from a database access. A transaction may
consist of a simple SELECT operation to generate a list of table
contents, or it may consist of a series of related UPDATE command
sequences.

Operations
• A transaction can include the following basic database access operations:
Operations Descriptions
Retrieve To retrieve data stored ina database.
Insert To store new data in database.
Delete To delete existing data from database.
Update To modify existing data in database.
Commit To save the work done permanently.
Rollback To undo the work done.
• Transaction that changes the contents of the database must alter the database from one consistent state to
another.
• A consistent database state is one in which all data integrity constraints are satisfied.
To ensure database consistency, every transaction must begin with the database in a known consistent state.

Transaction Execution and Problems
• A transaction which successfully completes its execution is said
to have been committed. Otherwise, the transaction is
aborted.
• Thus, if a committed transaction performs any update
operation on the database, its effect must be reflected on the
database even if there is a failure.

Transaction States
• A transaction can be in one of the following states:

State Description
Active state
• A transaction goes into an active state immediately after it starts execution, where it
can issue READ and WRITE operations.
• A transaction may be aborted when the transaction itself detects an error during
execution which it cannot recover from, for example, a transaction trying to debit loan
amount of an employee from his insufficient gross salary.
• A transaction may also be aborted before it has been committed due to system failure
or any other circumstances beyond its control.
Partially
committed
• When the transaction ends, it moves to the partially committed state. When the last
state is reached.
• To this point, some recovery protocols need to ensure that a system failure will not
result in an inability to record the changes of the transaction permanently.
• Once this check is successful, the transaction is said to have reached its commit point
and enters the committed state.
Aborted
• When the normal execution can no longer be performed.
Failed or aborted transactions may be restarted later, either automatically or after
being resubmitted by the user as new transactions.
Committed
• After successful completion of transaction.
• A transaction is said to be in a committed state if it has partially committed and it can
be ensured that it will never be aborted.

Transaction Execution with SQL
• The American National Standards Institute (ANSI) has defined standards that govern SQL database
transactions. Transaction support is provided by two SQL statements namely COMMIT and
ROLLBACK.
The ANSI standards require that, when a transaction sequence is initiated by a user or an application
program, it must continue through all succeeding SQL statements until one of the following four
events occur :
• A COMMIT statement is reached, in which case all changes are permanently recorded within the
database. The COMMIT statement automatically ends the SQL transaction. The COMMIT operations
indicates successful end-of-transaction.
• A ROLLBACK statement is reached, in which case all the changes are aborted and the database is
rolled back to its previous consistent state. The ROLLBACK operation indicates unsuccessful end-of-
transaction.
• 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 changes made in the database are aborted
and the database is rolled back to its previous consistent state. This action is equivalent to
ROLLBACK.

Transaction Properties
A transaction must have the following four properties, called
ACID properties (also called ACIDITY of a transaction), to ensure
that a database remains stable state after the transaction is
executed:
• Atomicity.
• Consistency.
• Isolation.
• Durability.

Atomicity
• The atomicity property of a transaction requires that all operations of a
transaction be completed, if not, the transaction is aborted. In other
words, a transaction is treated as single, individual logical unit of work.
Therefore, a transaction must execute and complete each operation in its
logic before it commits its changes.
• As stated earlier, the transaction is considered as one operation even
though there are multiple read and writes. Thus, transaction completes or
fails as one unit.
• The atomicity property of transaction is ensured by the transaction
recovery subsystem of a DBMS.
• In the event of a system crash in the midst of transaction execution, the
recovery techniques undo any effects of the transaction on the database.

Consistency
• Database consistency is the property that every transaction sees a consistent database
instance. In other words, execution of a transaction must leave a database in either its prior
stable state or a new stable state that reflects the new modifications (updates) made by the
transaction.
• If the transaction fails, the database must be returned to the state it was in prior to the
execution of the failed transaction.
• If the transaction commits, the database must reflect the new changes. Thus, all resources
are always in a consistent state.
• The preservation of consistency is generally the responsibility of the programmers who
write the database programs or of the DBMS module that enforces integrity constraints.
• A database program should be written in a way that guarantees that, if the database is in a
consistent state before executing the transaction, it will be in a consistent state after the
complete execution of the transaction, assuming that no interference with other
transactions occur. In other words, a transaction must transform the database from one
consistent state to another consistent state.

Isolation
• Isolation property of a transaction 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 isolates transactions from one another. In other
words, if a transaction T1 is being executed and is using the
data item X, that data item cannot be accessed by any other
transaction (T2………..Tn) until T1 ends.
• The isolation property is enforced by the concurrency control
subsystem of the DBMS.

Durability
• The durability property of transaction indicates the performance of
the database's consistent state. It states that the changes made by
a transaction are permanent.
• They cannot be lost by either a system failure or by the erroneous
operation of a faulty transaction.
• When a transaction is completed, the database reaches a
consistent state and that state cannot be lost, even in the event of
system's failure.
• Durability property is the responsibility of the recovery subsystem
of the DBMS.

Transaction Log (or Journal)
To support transaction processing, DBMSs maintain a transaction record of every change made to the
database into a log (also called journal). For each transaction, the following data is recorded on the log:
A start-of-transaction marker.
• The transaction identifier which could include who and where information.
• The record identifiers which include the identifiers for the record occurrences.
• The operation(s) performed on the records (for example, insert, delete, modify).
• The previous value(s) of the modified data. This information is required for undoing the changes
made by a partially completed transaction. It is called the undo log. Where the modification made by
the transaction is the insertion of a new record, the previous values can be assumed to be null.
• The updated value(s) of the modified record(s). This information is required for making sure that the
changes made by a committed transaction are in fact reflected in the database and can be used to
redo these modifications. This information is called the redo part of the log. In case the modification
made by the transaction is the deletion of a record, the updated values can be assumed to be null.
• A commit transaction marker if the transaction is committed, otherwise an abort or rollback
transaction marker.

Test Yourself
1. With regards to transaction processing, any DBMS should be capable of:
a. Ensuring that transactions are free from interference from other users.
b. Parts of a transaction are not lost due to a failure.
c. Transactions do not make the database inconsistent.
d. All of the above.
2. What is ACID properties of Transactions?
a. Atomicity, Consistency, Isolation, Database
b. Atomicity, Consistency, Inconsistent, Durability
c. Automatically, Concurrency, Isolation, Durability
d. Atomicity, Consistency, Isolation, Durability
3. A transaction completes its execution is said to be
a. Saved b. Loaded c. Rolled d. Committed
4. The deadlock state can be changed back to stable state by using _____________ statement.
a. Commit b. Rollback c. Savepoint d. Deadlock
5. A transaction for which all committed changes are permanent is called:
a. Atomic b. Consistent c. Isolated d. durable

Answers
1. With regards to transaction processing, any DBMS should be capable of:
a. Ensuring that transactions are free from interference from other users.
b. Parts of a transaction are not lost due to a failure.
c. Transactions do not make the database inconsistent.
d. All of the above.
2. What is ACID properties of Transactions?
a. Atomicity, Consistency, Isolation, Database
b. Atomicity, Consistency, Inconsistent, Durability
c. Automatically, Concurrency, Isolation, Durability
d. Atomicity, Consistency, Isolation, Durability
3. A transaction completes its execution is said to be
a. Saved b. Loaded c. Rolled d. Committed
4. The deadlock state can be changed back to stable state by using _____________ statement.
a. Commit b. Rollback c. Savepoint d. Deadlock
5. A transaction for which all committed changes are permanent is called:
a. Atomic b. Consistent c. Isolated d. durable

Introduction to transaction management

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Introduction to transaction management

Similar to Introduction to transaction management (20)

More from Dr. C.V. Suresh Babu

More from Dr. C.V. Suresh Babu (20)

Recently uploaded

Recently uploaded (20)

Introduction to transaction management