Transactions

1. Transactions CMSC 461 Michael Wilson

2. Hibernate sessions • If you’ve used hibernate, you may notice that in order to issue queries, you typically have to do a certain set of commands first • beginSession • commit • What are these, exactly?

3. Database transactions • Some (not all) DBMSes are transactional • They allow for a set of operations to be performed, and rolled back if need be • If you were performing a set of queries only to run into an error, what do you do? • Rollback the changes made since the transaction has started

4. PostgreSQL transactions • Syntax • START TRANSACTION [transaction modes] • You can supply multiple transaction modes • Transaction modes deal with what data the transaction sees when other transactions are running simultaneously

5. Transaction modes • ISOLATION LEVEL {type} • SERIALIZABLE • REPEATABLE READ • READ COMMITTED • READ UNCOMMITTED • READ WRITE or READ ONLY

6. Serializable • All statements can only see rows committed before the first query that’s executed in the transaction • If a conflict occurs between several concurrent serializable transactions, the transaction gets rolled back with a serialization_failure error

7. REPEATABLE READ • All statements only see rows committed before the first query is executed in the transaction

8. READ COMMITTED • A statement only sees rows committed before it began • One statement at a time, so if there’s a change in the database between different lines, it will show up • This is default

9. READ UNCOMMITTED • This is treated the same as READ COMMITTED in PostgreSQL • Defined in the SQL ANSI standard • If this were implemented: allows a transaction to see data that has not been committed by other transactions • “Dirty reads”

10. ACID • ACID • Atomicity • Consistency • Isolation • Durability • Guarantees that database transactions are predictable and reliable

11. Atomicity • Database transactions are atomic • Transactions are “all or nothing” • All of the operations within a transaction succeed, or the whole transaction fails • In other words, in a transaction, if there is a single error, the whole transaction is rolled back

12. Atomicity – how? • Several methods • Keeping snapshots of the current state of the database before the transaction occurs • Journaling • Keeping track of the changes that have been made in a sort of log before executing a statement • Journaling filesystems

13. Consistency • Transactions are consistent • Transaction starts with the database in a consistent state • Transaction stops with the database in a consistent state • The data in the database is not corrupted, destroyed, etc. • The state is predictable

14. Isolation • This guarantees that executing several transactions at the same time will result in the same state as if the individual statements of each transaction were executed one after the other • Remember the isolation level from before • This can be altered by different isolation levels

15. Isolation – how? • Very complex • Concurrency control (threading, mutexes, locks, etc.) • Needs to be able to recover from errors • Varies from DBMS to DBMS • Two phase locking frequently used • Two phases: expanding phase (locks are acquired but not released), shrinking phase (locks are released but not acquired)

16. Isolation levels • In terms of strictness: • SERIALIZABLE • REPEATABLE READ • READ COMMITTED • READ UNCOMMITTED

17. Read phenomena • Dirty reads • Reading uncommitted data during a transaction • Non-repeatable read • The same row is queried twice during a transaction and results in separate values • Phantom read • A statement is executed twice and the rows that come back are different between the two

18. Isolation levels vs. read phenomena • READ UNCOMMITTED • Dirty reads, non-repeatable reads, and phantom reads can occur • READ COMMITTED • Dirty reads and non-repeatable reads • REPEATABLE READ • Phantom reads • SERIALIZABLE • None

19. Isolation levels and performance • The higher the isolation level, the more of a performance impact can occur • Keeping the isolation level to SERIALIZATION could have a significant performance impact on your application • Be leery of arbitrarily setting the isolation level too high

20. Durability • After a transaction has been committed, it will persist • The transaction’s effect is therefore durable • Includes logging the actions to be committed before actually executing them

21. Log recovery • Many DBMSes have some sort of log designed for recovery of data in the event of disaster • PostgreSQL has a “write ahead log” • When recovery is needed, “replay” the entries from the write ahead log from the last checkpoint made • Oracle has a similar mechanism

22. Log recovery and performance? • Write ahead logs can impact performance • Should you keep them on? • Depends on various factors • Something like a database backed website, probably • Something where the data is temporarily stored in a DBMS, maybe not

23. Parallels to filesystems • Maybe of the same issues that plague databases (ACID) also plague filesystems • Networked filesystems can borrow transactional concepts • Some filesystems actually use a sort of DBMS under the covers • WinFS, a canceled Windows filesystem, used a database in its underlying implementation