Transaction Management

Question 1

Transaction

Answer 1

Process which takes the database from one consistent state to another consistent state. They basically exist to ensure that the data and operations you work with succeed in full.

Question 2

ACID Properties

Answer 2

• Atomic: All transaction actions will be completed, or nothing
• Consistent: After commit/abort, data satisfy all integrity constraints
• Isolation: Any changes are invisible to other transactions until commit
• Durable: Nothing is lost in future; failures occurring after commit cause no loss of data

Question 3

Anomalies from Interleaved Execution

Answer 3

Reading Uncommitted Data (dirty reads)

Unrepeatable Reads

Overwriting Uncommitted Data

Question 4

Reading uncommitted data(“dirty reads”)

Answer 4

A dirty read occurs if one transaction reads data that has been modified by another transaction.

Question 5

Unrepeatable reads

Answer 5

A non-repeatable read occurs when a object is read twice within a transaction; and between the reads, it is modified by another transaction, therefore, the second read returns different values as compared to the first; i.e., the read operation is non-repeatable

Question 6

Overwriting uncommitted data

Answer 6

A lost update occurs when two transactions read the same object and then modify this object independently. The transaction that is committed last overwrites the changes made by the earlier transaction.

Question 7

Two-Phase Locking Protocol

Answer 7

The Two-Phase Locking protocol allows each transaction to make a lock or unlock request in two steps:
• Growing Phase: In this phase transaction may obtain locks but may not release any locks.
• Shrinking Phase: In this phase, a transaction may release locks but not obtain any new lock
It is true that the 2PL protocol offers serializability. However, it does not ensure that deadlocks do not happen.

Question 8

Strict Two-Phase Locking Method

Answer 8

Strict-Two phase locking system is almost similar to 2PL. The only difference is that Strict-2PL never releases a lock after using it. It holds all the locks until the commit point and releases all the locks at one go when the process is over.

Question 9

Transaction Syntax SQL

Answer 9

START TRANSACTION

COMMIT

ROLLBACK

Question 10

BASE

Answer 10

Basically Available Soft State Eventually Consistent