Understanding Firebird Transactions

1. Understanding Firebird Transactions Pavel Císař IBPhoenix Firebird Conference 2003

2. Transactions Everyone can use them

3. Transactions 101 A B

4. Transactions 101 Transaction processing is a general concept to ensure integrity of any dynamic system in the course of transition from one consistent state to another consistent state.

5. Functional description • Atomicity • Consistency • Isolation • Durability

6. Atomicity All transformations in the course of transaction are treat as ONE, impartible transformation. • Pivotal and most visible attribute. • Nesting and save points.

7. Consistency When transaction ends (anyhow), system must be in consistent state. • System could be in the inconsistent state during transaction. • Depends on how „Consistent state“ is defined.

8. Isolation Transactions are isolated from each other, so they don‘t mutually interfere. • Source of all trouble with transactions. • Desirable for consistency • Horrible for concurrency

9. Durability After (and only after) the transaction is successfully completed, all transformations are permanent. • Special meaning of commit (and rollback).

10. Implementation strategies (Atomicity and Durability) • Changes held in memory, written on Commit ? • Changes immediately written to database, with recovery information: • Outside the database (Transaction log) • Right in database (Row versions)

11. Isolation – Cardinal problem In ideal isolation, all updates are mutually exclusive with any other operation (including read).

12. Implementation strategies (Isolation) • Transaction log • Locks (many types) • Data versioning • State of transaction • Locks (supporting method)

13. Transaction Isolation Levels • Defined by SQL standard. • Degree of give-and-take between consistency and concurrency. • Bound to transaction.

14. SQL92 Transaction Isolation Levels • Read Uncommitted • Read Committed (required) • Repeatable Read • Serializable (required)

15. Read Uncommitted • Also called „Dirty Read“ • Very basic (update) consistency • Highest concurrency • Acceptable for transactions that do not read data • Not supported by Firebird

16. Read Committed • Basic consistency • Cannot read uncommited changes • Non-repeatable reads • Phantom rows • Good for • „Change monitoring“ transactions • Transactions that do not depend on repeatable read (mostly update) • Supported by Firebird with options: • Record versions – Pending changes do not block reads • No record versions – Compatible with SQL standard

17. Repeatable Read • Higher consistency, much lower concurrency • SQL Standard allows phantom rows • Suitable for reports and some calculations • No direct support in Firebird. Firebird has isolation level called Snapshot (or Concurrency)that is similar to Repeatable Read, but: • Do not allows phantom rows. • Do not guarantee that transaction can update data that have been read.

18. Serializable • Total consistency at the cost of very poor concurrency. • The only one that do not allow phantom rows. • Necessary for some calculations. • Supported by Firebird as Snapshot Table Stability (or Consistency). You can get the same result with Snapshot + Table reservation.

19. Main differences between SQL standard and Firebird • SQL standard is defined along lock&log implementation. • Rows locked by update/delete (or insert) could not be read by other transactions.This do not apply for Firebird, except for Read Committed with „No record version“ option. • Rows locked by read are updateable by blocking transaction.This do not apply for Firebird, except for Snapshot Table Stability or transactions with table reservation.

20. Read Uncommitted Read Committed Repeatable Read Serializable Read Write Read Write Read Write Read Write Read Uncommitted Read Write U/D pending U/D U/D U/D U/D/I U/D/I Read Committed Read pending pending pending pending Write U/D pending U/D U/D U/D U/D/I U/D/I Repeatable Read Read U/D U/D U/D U/D Write U/D pending U/D U/D U/D U/D/I U/D/I Serializable Read U/D/I U/D/I U/D/I U/D/I Write U/D/I pending U/D/I U/D U/D/I U/D/I U/D/I Collisions - SQL Isolation Levels

21. Read Committed Snapshot Snapshot Table Stability Read Write Read Write Read Write Read Committed Read Write U/D U/D U/D/I U/D Snapshot Read Write U/D U/D U/D/I U/D Snapshot Table Stability Read U/D/I U/D/I U/D/I Write U/D U/D U/D/I U/D Collisions – Firebird Isolation Levels

22. „Optimistic locking“ • System always knows better what must be protected, and when. • All „pending“ changes are always protected. • All reads are protected from update for Repeatable Read and Serializable (SQL standard). Firebird protects only Snapshot Table Stability or on demand (table reservation).

23. Table reservation • Available for all Firebird isolation levels. • Tables are reserved for the specified access when the transaction is started (prevent possible deadlocks and update conflicts that can occur if locks are taken only when actually needed). • Fine-grained access specification for individual tables: • Shared read: All others can read and update, most liberal • Shared write: All others except „Serializable“ can read and update. • Protected read: All can only read. • Protected write: All except „Serializable“ can read, but only blocking transaction can write.

24. Lock resolution • WaitThe default, transaction will wait until locked resources are released. Once the resources are released, the transaction retries its operation. • No waitTransaction returns a lock conflict error without waiting for locks to be released.

25. MGA implementation I. • Record versions • Linked list starting from most recent version. • Each version contains transaction number. • Bitmap of Transaction States • Used to determine the visibility of particular version for a transaction. • Snapshot transactions have a snapshot created when they start. • Read Committed transactions use shared map with actual states.

26. MGA implementation II. • Transaction state bitmap from OIT (not committed transaction) • 00 – Active • 01 – Limbo • 10 – Dead (rollback) • 11 – Committed • Stored in database as Transaction Inventory Pages (TIP). • 1K page can hold up to 4016 transactions (4 transactions per byte * (1024 bytes – 16 byte header – 4 byte next TIP page number)) • On first db attachment: • Transaction state bitmap is restored from TIPs • Active transactions are marked as dead

27. 60 60 – Active RC 59 – Active S 56 58 – Rollback S 52 57 – Commit RC 50 56 – Commit RC 32 55 – Active S 54 – Rollback RC 53 – Active RC Oldest Active Transaction (OAT) 52 – Commit S 51 – Commit RC 50 – Rollback S Oldest Interesting Transaction (OIT) MGA implementation III.T-Shirt Question! Current Transaction States Chain of row versions Transaction 53 will get version 56

28. MGA implementation IV. • New row version is always written in the place of old version. • Previous version is moved to another place, preferably on the same page. • When there is no more room on Data page for previous version or new version, everything starts to deteriorate.

29. Page header Page number Relation Number N Offset_1, Size_1 Offset_2, Size_2 Offset_3, Size_3 … Offset_N, Size_N Data - row N … Data - row 3 Data - row 2 Data - row 1 Data page

30. MGA implementation V. • All versions from dead transactions or versions from transactions beyond first committed transaction lower than OAT could be removed. • Unnecessary versions are detected whenever Firebird access a row. This process is called Garbage Collection. • Classic removes unnecessary versions immediately. • Super Server marks them for separate GC thread. • Garbage collection doesn't ensure that all row versions from dead transactions are removed. • Long-running transactions are a disaster.

31. MGA implementation VI. • Commit/Rollback Retaining doesn't really ends the transaction. • Rollback requested by client may use an undo log: • If undo log is not too big. • If undo log is not disabled by client. • Read Only transactions in Read Committed isolation level do not block garbage collection.

32. MGA implementation VII. • Save points • Have been there for long time for internal use • Modified and surfaced for general use in Firebird 1.5SAVEPOINT nameROLLBACK [WORK] TO [SAVEPOINT] name • Undo log for save point • Starts at transaction level • Created for statements, stored procedures and triggers • Nested when necessary and merged up • Much improved in Firebird 1.5 (use of B+tree)

33. MGA implementation VIII. • „Frozen“ Oldest Interesting Transaction • Slow down start of new transactions • Eats server resources • Gap between OIT and current transaction may start the Sweep process (depend on sweep interval settings) • Sweep does the garbage collection of all unnecessary versions in database + advance OIT • Full database GC (by gbak for example) do not replace sweep!

34. MGA implementation IX. • New in Firebird 1.5 • SELECT <...> [FOR UPDATE [OF col [, col ...]] [WITH LOCK]] • For use with server-side cursors only • Dosn‘t mess with record versions like „fake update“ • Use at your own risk Explicit record locking

35. Firebird tips & tricks • Avoid use of Commit Retaining and stable db_keys. • Avoid designs that require many changes to single row. • Avoid designs that require updates as part of insert. • If transaction reads many rows for later update, use table reservation. • For long-running „change monitoring“ transactions, use Read Committed with Read Only attribute, or events. • Do not perform many changes in nested procedures. • Keep number of changes per transaction reasonably small.

36. That‘s all (for now) http://www.ibphoenix.com We have answers