Transactions

Question 1

What is a database transaction?

Answer 1

A unit of program execution that accesses and possibly updates various data items delimited by begin transaction and end transaction statements

Question 2

What does the ACID acronym stand for in database transactions?

Answer 2

Atomicity Consistency Isolation Durability

Question 3

What is atomicity in database transactions?

Answer 3

Either all operations of the transaction are reflected in the database or none are

Question 4

What is consistency in database transactions?

Answer 4

The database remains in a consistent state before and after transaction execution

Question 5

What is isolation in database transactions?

Answer 5

Each transaction appears to execute alone even when multiple transactions run concurrently

Question 6

What is durability in database transactions?

Answer 6

Once a transaction completes successfully its changes persist even if system failures occur

Question 7

What are the two basic operations transactions use to access data?

Answer 7

read(X) and write(X)

Question 8

What are the five possible states of a transaction?

Answer 8

Active Partially committed Failed Aborted Committed

Question 9

What happens in the partially committed state?

Answer 9

Final statement executed but transaction may still need to be aborted as data might still be in main memory

Question 10

What is conflict serializability?

Answer 10

A schedule that can be transformed into a serial schedule by swapping non-conflicting operations

Question 11

When do two operations conflict in a transaction schedule?

Answer 11

When they are from different transactions operate on same data item and at least one is a write operation

Question 12

How can you determine if a schedule is conflict serializable?

Answer 12

Create a precedence graph - if it has no cycles the schedule is conflict serializable

Question 13

What is a recoverable schedule?

Answer 13

One where transactions commit only after all transactions whose changes they read have committed

Question 14

What is a cascadeless schedule?

Answer 14

One where transactions only read data written by committed transactions

Question 15

What are the three main implementation techniques for transaction isolation?

Answer 15

Locking Timestamp ordering and Versioning

Question 16

What is snapshot isolation?

Answer 16

A versioning technique where each transaction works with its own version of the database when it begins

Question 17

What information does a precedence graph contain?

Answer 17

Vertices represent transactions edges represent conflicts between operations

Question 18

What are the two ways to handle an aborted transaction?

Answer 18

1. Restart it (if aborted due to hardware/software error) 2. Kill it

Question 19

What is cascading rollback?

Answer 19

When failure of one transaction forces rollback of other dependent transactions

Question 20

What is view serializability?

Answer 20

A schedule that is equivalent to a serial schedule in terms of initial reads writes and final writes

Question 21

Why is concurrent execution of transactions preferred over serial execution?

Answer 21

Two reasons: 1) Improved throughput and resource utilization 2) Reduced waiting time for transactions

Question 22

What is the fundamental problem with immediate external writes (like ATM cash dispense) in transactions?

Answer 22

They cannot be undone once executed so they should only occur after the transaction has fully committed

Question 23

What is stored in a transaction log?

Answer 23

The old values of any data on which a transaction performs a write stored on disk for recovery purposes

Question 24

What are the three conditions for two schedules to be view equivalent?

Answer 24

1) Same initial reads 2) Same reads following writes 3) Same final writes for each data item

Question 25

For conflict operations what are the four possible combinations and which ones conflict?

Answer 25

read-read: no conflict read-write: conflict write-read: conflict write-write: conflict

Question 26

What’s the main difference between view serializability and conflict serializability?

Answer 26

Every conflict serializable schedule is view serializable but not vice versa. View serializability is also NP-complete to check

Question 27

What happens when a transaction enters the failed state?

Answer 27

The system determines it can’t proceed with normal execution and must be rolled back to enter the aborted state

Question 28

What is a compensating transaction?

Answer 28

A transaction that reverses the effects of a committed transaction when external actions can’t be undone (like ATM cash dispense)

Question 29

What must be true about the read and write timestamps in timestamp-based concurrency control?

Answer 29

Read timestamp holds the largest (most recent) timestamp of transactions that read the item; write timestamp holds the timestamp of the transaction that wrote the current value

Question 30

In locking implementation what’s the difference between database-level and item-level locking?

Answer 30

Database-level locking locks the entire database for each transaction while item-level locking only locks the specific data items being accessed