07 Transactions

Question 1

types of transaction scheduling

Answer 1

1. serial
   - does not allow interleaving
   - highest level of safety
   - poor efficiency
2. equivalent
   - allow interleaving
   - same output
3. serializable
   - combination of serial and equivalent

Question 2

transaction properties: ACID

Answer 2

A: atomicity

• 1 transaction is one unit
• do all or nothing

C: consistency

• each transaction preserves the consistency of db
• before and after transaction
• consistency does not guarantee correctness

I: isolation
- transactions are isolated until completion

D: durability

• once transaction completed, holds true even if system crashes
• record history, go back if cannot complete
• stored on disk

Question 3

transactions keywords

Answer 3

1. commit
   - end transaction and make changes permanently
2. rollback
   - discard all pending changes
3. save point
   - marker that divides transaction to pieces
4. rollback to save point
   - revert to specified save point

Question 4

recovery

Answer 4

1. transactions unable to protect everything eg.
   - system crash
   - power failure
   - disc crash
   - user mistake
2. good practice to prevent
   - robust system (linux)
   - security (local network)

create save points
backup transaction logs
backup power supply

Question 5

concurrency control: Lock

Answer 5

1. force system to become non-concurrent (like mutex)
2. lock granularity
   - database level
   - table level
   - row level
   - field level
3. dead lock may occur

Question 6

optimisation methods for locks

Answer 6

1. no need locks for read operations
2. many write operations do not write the same data.
3. even if same data, often not at the same time
4. go ahead with execution assuming best case scenario.
5. if violations happen, restore to original state
6. 3 method phase
   - read
   - validate integrity and consistency
   - write