Transactions
A transaction is an executing program that forms a logical unit of database processing. A
transaction includes one or more database access operations these can include insertion,
deletion, modification (update), or retrieval operations. If the database operations in a
transaction do not update the database but only retrieve data, the transaction is called a read-
only transaction; otherwise it is known as a read-write transaction.
Q. Draw a state diagram and discuss the typical states that a transaction goes through
during execution.
• BEGIN_TRANSACTION. This marks the beginning of transaction execution.
• READ or WRITE. These specify read or write operations on the database items that are
executed as part of a transaction. A transaction goes into an active state immediately after
it starts execution, where it can execute its READ and WRITE operation
• END_TRANSACTION. This specifies that READ and WRITE transaction operations
have ended and marks the end of transaction execution and moves to the partially
committed state. However, at this point it may be necessary to check whether the changes
introduced by the transaction can be permanently applied to the database (committed) or
whether the transaction has to be aborted because it violates serializability or for some
other reason.
• COMMIT_TRANSACTION. This signals a successful end of the transaction so that any
changes (updates) executed by the transaction can be safely committed to the database
and will not be undone.
• ROLLBACK (or ABORT). This signals that the transaction has ended unsuccessfully, so
that any changes or effects that the transaction may have applied to the database must be
undone.
• The terminated state corresponds to the transaction leaving the system .Transaction
information that is maintained in system tables while the transaction has been running is
removed when the transaction terminates.
Q. Discuss What is Concurrency Control? Why concurrency control is needed, and give
informal examples.
Concurrency control in a (DBMS) refers to the coordination of simultaneous execution of
transactions in a multiuser environment. The main objective of concurrency control is to
ensure that database transactions are executed in a safe manner and that the integrity of the
data is maintained, even when multiple transactions are executed concurrently. Techniques
like locking mechanisms, two-phase locking, and different isolation levels help manage the
challenges of concurrency, preventing anomalies such as lost updates, dirty reads, non-
repeatable reads, and phantom reads.
,Concurrency control is essential for several reasons:
1. Maintain Data Consistency: Ensure that concurrent transactions produce a
consistent and correct state of the database, preserving the integrity of data.
2. Prevent Anomalies: Avoid problems such as lost updates, dirty reads, non-repeatable
reads, and phantom reads that can arise when transactions interfere with each other.
3. Ensure Isolation: Each transaction should be executed in isolation from others,
meaning the intermediate states of a transaction should not be visible to other
transactions.
4. Maximize Throughput: Allow as many transactions as possible to proceed
concurrently without compromising the integrity of the database.
Informal Examples of Concurrency Problems and Control Mechanisms
1. Lost Updates
Problem: Two transactions update the same data item concurrently, and one update
overwrites the other, leading to lost updates and makes the value of some database items
incorrect.
Example:
• Transaction T1 reads the balance of an account (balance = 2500).
• Transaction T2 also reads the balance of the same account (balance = 2500).
• T1 updates the balance by subtracting 500 (new balance = 2000) and writes it back.
• T2 updates the balance by adding 700 (new balance = 3200) and writes it back.
• The final balance should be 2700, but due to lost updates, it is incorrectly set to 3200.
Solution: Locking Mechanism
• Before reading or writing the balance, a transaction acquires a lock. T1 or T2 must wait if
the lock is already held, ensuring one completes before the other begins.
2. Temporary Update or Dirty Reads
Problem: A transaction reads data that has been modified by another transaction that has not
yet committed, leading to inconsistent data if the modifying transaction is rolled back.
Example:
, • Transaction T1 updates the balance of an account 600 to 400 but has not committed.
• Transaction T2 reads the balance and sees 400. And then updates it to 1000 by adding
600.
• T1 rolls back, and the balance returns to its original value of 600 due to some failure
issues.
• T2 has read a "dirty" value that was never actually committed.
Solution: Strict Two-Phase Locking (2PL)
• T1 must hold an exclusive lock on the data until it commits or rolls back, preventing
T2 from reading the uncommitted data.
3. Non-Repeatable Reads
Problem: A transaction reads the same data multiple times and gets different values because
another transaction has modified the data between reads.
Example:
• Transaction T1 reads the balance of an account (balance = 600).
• Transaction T2 updates the balance to 800 and commits.
• T1 reads the balance again and sees 800. And add 700 update to 1500.
• T1 has observed a non-repeatable read because the balance changed between reads.
Solution: Repeatable Read Isolation Level
• T1 acquires a shared lock on the data during the first read and holds it until the
transaction completes, preventing T2 from modifying the data until T1 finishes.
4. Inconsistent Analysis Problem
Problem: The inconsistent analysis problem occurs when a transaction reads several values
from a database, but another transaction concurrently modifies some of those values, leading
to an inconsistent or incorrect analysis.
Example:
A transaction is an executing program that forms a logical unit of database processing. A
transaction includes one or more database access operations these can include insertion,
deletion, modification (update), or retrieval operations. If the database operations in a
transaction do not update the database but only retrieve data, the transaction is called a read-
only transaction; otherwise it is known as a read-write transaction.
Q. Draw a state diagram and discuss the typical states that a transaction goes through
during execution.
• BEGIN_TRANSACTION. This marks the beginning of transaction execution.
• READ or WRITE. These specify read or write operations on the database items that are
executed as part of a transaction. A transaction goes into an active state immediately after
it starts execution, where it can execute its READ and WRITE operation
• END_TRANSACTION. This specifies that READ and WRITE transaction operations
have ended and marks the end of transaction execution and moves to the partially
committed state. However, at this point it may be necessary to check whether the changes
introduced by the transaction can be permanently applied to the database (committed) or
whether the transaction has to be aborted because it violates serializability or for some
other reason.
• COMMIT_TRANSACTION. This signals a successful end of the transaction so that any
changes (updates) executed by the transaction can be safely committed to the database
and will not be undone.
• ROLLBACK (or ABORT). This signals that the transaction has ended unsuccessfully, so
that any changes or effects that the transaction may have applied to the database must be
undone.
• The terminated state corresponds to the transaction leaving the system .Transaction
information that is maintained in system tables while the transaction has been running is
removed when the transaction terminates.
Q. Discuss What is Concurrency Control? Why concurrency control is needed, and give
informal examples.
Concurrency control in a (DBMS) refers to the coordination of simultaneous execution of
transactions in a multiuser environment. The main objective of concurrency control is to
ensure that database transactions are executed in a safe manner and that the integrity of the
data is maintained, even when multiple transactions are executed concurrently. Techniques
like locking mechanisms, two-phase locking, and different isolation levels help manage the
challenges of concurrency, preventing anomalies such as lost updates, dirty reads, non-
repeatable reads, and phantom reads.
,Concurrency control is essential for several reasons:
1. Maintain Data Consistency: Ensure that concurrent transactions produce a
consistent and correct state of the database, preserving the integrity of data.
2. Prevent Anomalies: Avoid problems such as lost updates, dirty reads, non-repeatable
reads, and phantom reads that can arise when transactions interfere with each other.
3. Ensure Isolation: Each transaction should be executed in isolation from others,
meaning the intermediate states of a transaction should not be visible to other
transactions.
4. Maximize Throughput: Allow as many transactions as possible to proceed
concurrently without compromising the integrity of the database.
Informal Examples of Concurrency Problems and Control Mechanisms
1. Lost Updates
Problem: Two transactions update the same data item concurrently, and one update
overwrites the other, leading to lost updates and makes the value of some database items
incorrect.
Example:
• Transaction T1 reads the balance of an account (balance = 2500).
• Transaction T2 also reads the balance of the same account (balance = 2500).
• T1 updates the balance by subtracting 500 (new balance = 2000) and writes it back.
• T2 updates the balance by adding 700 (new balance = 3200) and writes it back.
• The final balance should be 2700, but due to lost updates, it is incorrectly set to 3200.
Solution: Locking Mechanism
• Before reading or writing the balance, a transaction acquires a lock. T1 or T2 must wait if
the lock is already held, ensuring one completes before the other begins.
2. Temporary Update or Dirty Reads
Problem: A transaction reads data that has been modified by another transaction that has not
yet committed, leading to inconsistent data if the modifying transaction is rolled back.
Example:
, • Transaction T1 updates the balance of an account 600 to 400 but has not committed.
• Transaction T2 reads the balance and sees 400. And then updates it to 1000 by adding
600.
• T1 rolls back, and the balance returns to its original value of 600 due to some failure
issues.
• T2 has read a "dirty" value that was never actually committed.
Solution: Strict Two-Phase Locking (2PL)
• T1 must hold an exclusive lock on the data until it commits or rolls back, preventing
T2 from reading the uncommitted data.
3. Non-Repeatable Reads
Problem: A transaction reads the same data multiple times and gets different values because
another transaction has modified the data between reads.
Example:
• Transaction T1 reads the balance of an account (balance = 600).
• Transaction T2 updates the balance to 800 and commits.
• T1 reads the balance again and sees 800. And add 700 update to 1500.
• T1 has observed a non-repeatable read because the balance changed between reads.
Solution: Repeatable Read Isolation Level
• T1 acquires a shared lock on the data during the first read and holds it until the
transaction completes, preventing T2 from modifying the data until T1 finishes.
4. Inconsistent Analysis Problem
Problem: The inconsistent analysis problem occurs when a transaction reads several values
from a database, but another transaction concurrently modifies some of those values, leading
to an inconsistent or incorrect analysis.
Example:
