4. Database Transactions Quiz

Transactions

• Transactions are indivisible units of operations
• Either all operations succeed or none do
• Example: Transferring money
  • Funds are withdrawn from one account
  • Funds are added to another account
• What happens if the system fails after the first step?

Database Transaction Example

• Account information held by the bank
• Example table:
  • AccountNo | Balance
  • LT9001 | 2000.0
  • LT9003 | 45.0

Transaction DB Example (2)

• Example queries for transferring money
  • UPDATE Accounts SET Balance = Balance - 400 WHERE AccountNo = 'LT9001';
  • UPDATE Accounts SET Balance = Balance + 400 WHERE AccountNo = 'LT9003';
• What happens if the program fails after the first query?

DB Solution

• SET AUTOCOMMIT FALSE;
• UPDATE Accounts SET Balance = Balance - 400 WHERE AccountNo = 'LT9001';
• UPDATE Accounts SET Balance = Balance + 400 WHERE AccountNo = 'LT9003';
• COMMIT;
• SET AUTOCOMMIT TRUE;

DB Solution (2)

• SET AUTOCOMMIT FALSE; - Turns off automatic commit after each statement
• SET AUTOCOMMIT TRUE; - Turns on automatic commit after each statement
• COMMIT; - Confirm changes
• ROLLBACK; - Cancel all changes in a transaction
• START TRANSACTION (BEGIN);- Indicates start of a transaction, commonly used in MySQL

Isolation

• Different Database Management Systems (DBMS) support different isolation levels
• Ensures data isolation in multi-user databases
• Ensures unfinished transactions are not visible outside
• Guarantees a transaction operates with specific data unaffected by other transactions
• Ensures consistent data for the lifetime of a transaction.
• Example isolation levels available:
  • Read committed
  • Repeatable read
  • Serializable
  • Read uncommitted

Isolation Level H2

• Standard isolation level is Read Committed
• Transaction data visible to others only after COMMIT
• Consistency in repeated data reading within a transaction
• Effect on views of results of other transactions

Task

• Test INSERT, UPDATE, and DELETE statements in transactions (using ROLLBACK)
• Test ALTER TABLE statements in transactions (adding a new column)
• Test DROP TABLE statements in a transaction

Test your knowledge on database transactions, their properties, and examples of how transactions are implemented in SQL. This quiz covers key concepts like atomicity, failure scenarios, and the use of autocommit in databases.