ACID

Transaction

• Transaction

  • A collection of queries
  • One unit of work
  • E.g. Account deposit(SELECT, UPDATE, UPDATE)

• Transaction Lifespan

  • Transaction BEGIN
  • Transaction COMMIT
  • Transaction ROLLBACK
  • Transaction unexpected ending = ROLLBACK (e.g. crash)

• Nature of Transactions

  • Usually Transactions are used to change and modify data.
  • However, it’s perfectly normal to have a read only transaction.
  • Example, you want to generate a report and want to get consistent snapshot based at the time of transaction

• Demo: Send $100 from Account1 to Account 2

  ACCOUNT_ID	BALANCE
  1	$1000
  2	$500

  • BEGIN TX1
  • SELECT BALANCE FROM ACCOUNT WHERE ACCOUNT_ID = 1
  • If b1 > 100 then
    • UPDATE ACCOUNT SET BALANCE = BALANCE - 100 where ACCOUNT_ID = 1
    • UPDATE ACCOUNT SET BALANCE = BALANCE + 100 where ACCOUNT_ID=2
  • COMMIT TX1

Atomicity

• Atomicity
  • All queries in a transaction must succeed
  • If one query fails, all prior successful queries in the transaction should rollback
  • If the database went down prior to a commit of transaction, all the successful queries in the transactions should rollback

Isolation

• Read phenomena
  • Dirty reads
  • Non-repeatable reads
  • Phantom reads
  • Lost updates