Transactions

1. Transactions

2. Full stacks • bitnami, xampp, for the free dbs: mysql, postgresql • Build your own • Macs have Apache, and windows machines have IIS • Macs have PHP, windows has its own environment • Mix and match: use bitnami or xampp with Oracle or SQL Server • Web development environments • Ruby on rails • Grails, Turbo gears, etc. First: database app stacks

3. Transactions are a unit of work • Atomic • Not part of any other unit of work The unit of work

4. What do transactions do? • Update db • Query db • Initiate an outside activity, like ejecting money from an ATM • We need to know if it happened, precisely Why ACID?

5. Consistency: enforcing integrity constraints • PKs, FKs, non-nulls, correct domain values • Enforced immediately or at commit point Or - goal of a txn protocol: protecting that state

6. All or nothing at all: atomic • Must be able to undo • Must make commit atomic • Two phases • First – request, get, wait for locks and compute • Second - commit Atomicity

7. Isolate a transaction: • Cannot give any information to another transaction • Cannot change its behavior according to direct communication from another transaction • Cannot make a computation of another transaction invalid or different Isolation

8. Making a transaction durable • Related to atomic – once it happens it cannot be undone • To get it done: • Use persistent storage • Keep old copies of db Durability

9. Integrity constraint violation • Deadlock on waiting for a lock • Computational problem like a zero divide or illegal update of a view table • Hardware failure • Operating system failure When transactions abort

10. Compensating transaction: intractable to generalize • Put results in a log or duplicate db until commit How to undo

11. Serializability • The steps of individual transactions are intertwined • But only in a way that makes it appear that the transactions executed in some serial order • All serial orders are equivalent by definition • Must use some serializable schedule Another perspective of ACID

12. Serializability is odd in one sense • Different schedules can lead to different results • 2 phase transactions are not good for • Long transactions • Interactive transactions • Transaction chains What about broadening the nature of a transaction?

13. Allow transactions to have subtransactions • Perhaps allow a subtransation to abort without outer transaction aborting • Allow subtransactions to run in parallel or sequentially • Allow flow of control decisions decide what subtransactions run • If a root transaction aborts, subtransactions must abort or we are not in control of the final state Nest transactions

14. Transaction x+1 starts only after x finishes • Perhaps no other transaction can start in between • To protect the environment x sets up for x+1 • Cannot undo x even if x+1 aborts • Could introduce shared data space for the chain • Order of transactions in a chain or the specific transactions participating could be determined dynamically Serial or chained transactions

15. Complex transaction protocols can make it difficult for programmers to visualize the semantic nature of a transaction • Longer transactions increase wait times and aborts • Not enough applications demand any specific alternative protocol Complications

16. Two approaches • Detection with long waits • Prevention with waits-for graphs • Must be very rare Deadlocks

17. 2 phase and nested/chained transactions are in a sense “pessimistic” • To keep the system moving, most locks must be in a sense unnecessary • Yet, we use locks every time an object is requested • There are optimistic protocols that achieve the same semantics • But overhead is not reduced An observation

18. Based on a logical or physical item • Could be large or small grained • Trend has been toward logical, small objects, like individual tuples to increase concurrency Lock granularity

19. Distinguish between read and write locks • A read lock can be upgraded to a write lock • Read locks are shared • Write locks are not shared Lock upgrades

20. Keep checkpoints of DB • Keep a log of transactions • Be able to rerun (but would probably lead to a different state) Larger grained protection

21. Low level transaction specification language • Becomes difficult to know what a transaction will do • Too easy to create a protocol that seems correct but isn’t • Difficult to optimize a widely flexible protocol • “declare all needed objects up front” protocols • We cannot determine what a transaction could need • Transactions would simply lock large chunks of databases Crazy ideas of the past

22. Versions of db’s, tables, tuples • Multiple concurrent states • State merging or resolution • Manipulation of temporal properties • Such as transaction vs. valid time • Did the doctor provide the best treatment known at the time? • Far more complex constraints for engineering apps Other things we might want in a txn protocol

23. Real time transactions • Extremely long term transactions • Making some transactions more vital than others • Making high level application actions protected by transaction protocols, as well as security and recoverability • Write-mostly databases Other limitations of txn based DBMSs

24. Ignoring restarts, it forces txs to commit in the order they start • No lock table • Each object has last write and read times on it (txnnums) • Txn cannot read an object written by a younger txn • Txn cannot write an object read by an older txn • Reads don’t conflict • Write write conflicts mean kill the write Optimistic CC protocols

25. Maintain db copies • Maintain accompanying txn log\ • If you rerun a log, you might well get a different, but ok state Checkpoint recovery

26. Vertical systems • Invest in hardware at the bottom • Layers • Client GUI • Presentation services • Applications • Transactions • DBMS server Transaction processinug

27. Single user system • Centralized system • Two tiered system (distributed) • Three tiered system (distributed) • Three tiered system (distributed) transaction server • Parallel systems • Distributed transactions, coordinated and not coordinated Various diagrams in book