Transactions

Question 1

What does the A stand for in ACID?

Answer 1

Atomicity: Either all of the transaction should occur or none of it

Question 2

What does the C stand for in ACID?

Answer 2

Consistency: The transaction should not leave the database in an inconsistent state

Question 3

What does the I stand for in ACID?

Answer 3

Isolation: Transactions shouldn’t be aware of other transactions

Question 4

What does the D stand for in ACID?

Answer 4

Durability: All changes made to the database should be persisted to storage.

Question 5

What are the 5 transaction states?

Answer 5

• Active
• Partially committed
• Committed
• Failed
• Aborted

Question 6

When is the transaction in the Active state

Answer 6

The initial state and the state the transaction stays in while executing.

Question 7

When is the transaction in the partially committed state?

Answer 7

After the final statement in the application logic has been executed.

Question 8

When is the transaction in the committed state?

Answer 8

After successful completion of the transaction.

Question 9

When is the transaction in the failed state?

Answer 9

After the discovery normal executing can no longer proceed.

Question 10

When is the transaction in the aborted state?

Answer 10

After the roll back occurs and the database is back in its state prior to the start of the transaction. Two options after abort:
Restart transaction
Kill the transaction

Question 11

What is a schedule?

Answer 11

Order in which instructions of transactions are executed. Must consist of all instructions for scheduled transactions and preserve their order.

Question 12

What is a serial schedule?

Answer 12

Transactions are run serially to completion - ensure consistency.

Question 13

What are non-serial schedules?

Answer 13

Used for concurrent execution, doesn’t always give consistent results.

Question 14

Why do we need equivalent schedules?

Answer 14

Need to have non-serial schedules function like a serial scheduler in order to have consistent database.

Question 15

When are two schedules conflict equivalent?

Answer 15

IF:

1. One can be transformed into the other by a series of swaps of non-conflicting adjacent instructions.
2. It is conflict equivalent to some serial schedule.

Question 16

How do we construct a directed precedence graph?

Answer 16

Add edge between any vertices that can’t be swapped for a data item (AKA anything that isn’t 2 reads).

Question 17

What does each edge in a directed precedence graph mean?

Answer 17

Ti-> Tj,

Ti must be executed before Tj in any serial schedule S’ == S

Question 18

What happens if a directed precedence graph has no cycles?

Answer 18

Schedule is conflict serialisable

Question 19

When there are cycles in a directed precedence graph, what does that mean?

Answer 19

The schedule is not conflict serialisable.

Question 20

What modes of locking are there?

Answer 20

Exclusive, lock-X. R/W

Shared, lock-S. R only

Question 21

What is a locking protocol?

Answer 21

Set of rules followed by all transactions while requesting and releasing locks. Restrict the set of possible schedulers. Deadlocks and starvation may occur.

Question 22

How are deadlocks and starvation related?

Answer 22

Deadlocks cause rollbacks of the lowest-valued transaction. If the same one is constantly rolled back then it is starved.

Question 23

How does the 2-phase locking protocol work?

Answer 23

Phase 1: Growing phase. Transaction may only obtain locks.

Phase 2: Shrinking phase. Transaction may only release locks.

Question 24

What are the advantages of having a 2 phase locking protocol?

Answer 24

Transaction execution schedulers are equivalent to serial schedulers in order of their lock points

Question 25

What is a lock point?

Answer 25

Point where transaction acquired its final lock.

Question 26

How do we construct wait for graphs?

Answer 26

When Ti is waiting for Tj to release a data item, then we draw an edge.

Question 27

How do we recover from deadlocks?

Answer 27

Roll back transaction to break deadlock.

Question 28

How does the wait-die scheme work?

Answer 28

Older transaction wait for younger to release data item, younger transaction never wait for old ones - roll back instead.

Question 29

How does the wound-wait scheme work?

Answer 29

Older transaction “wounds” (forces rollback of) younger transactions. Younger transactions may wait for older ones.

Question 30

How do timeout-based schemes work?

Answer 30

A transaction waits for a lock for a specified amount of time, after which it rolls back.

Question 31

How do Graph-based protocols work?

Answer 31

Impose partial ordering on the set. If di -> dj then acquiring a lock on dj is only allowed if a lock on the parent Di is obtained first.