1 / 59

Database Transactions and Transaction Management

Database Transactions and Transaction Management. Svetlin Nakov. National Academy for Software Development. academy.devbg.org. Agenda. What is a Transaction? ACID Transactions Concurrency Problems Concurrency Control Techniques Locking Strategies Optimistic vs. Pessimistic Locking

chynna
Download Presentation

Database Transactions and Transaction Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Database Transactions and Transaction Management Svetlin Nakov National Academy for Software Development academy.devbg.org

  2. Agenda • What is a Transaction? • ACID Transactions • Concurrency Problems • Concurrency Control Techniques • Locking Strategies • Optimistic vs. Pessimistic Locking • Deadlocks • Transactions and Recovery

  3. Agenda (2) • Transactions and SQL Language • Transaction Isolation Levels • When and How to Use Transactions?

  4. What is a Transaction?

  5. Transactions • Transactions are a sequence of actions (database operations) which are executed as a whole: • Either all of them execute successfully • Or none of the them • Example: • A bank transfer from one account into another (withdrawal + deposit) • If either the withdrawal or the deposit failsthe whole operation is cancelled

  6. A Transaction Read Write Durable starting state Collection of reads and writes Durable, consistent, ending state Commit Write Rollback

  7. Transactions Behavior • Transactions guarantee the consistency and the integrity of the database • All changes in a transaction are temporary • Changes become final when COMMIT is executed • At any time all changes can be canceled by ROLLBACK • All of the operations are executed as a whole, either all of them or none of them

  8. Withdraw $100 Read savings New savings =current - 100 Read checking New checking =current + 100 Write savings Write checking Transactions: Examples Transfer $100 • Read current balance • New balance = current - 100 • Write new balance • Dispense cash

  9. What Can Go Wrong? • Some actions fail to complete • For example, the application software or database server crashes • Interference from another transaction • What will happen if several transfers run for the same account in the same time? • Some data lost after actions complete • Database crashes after withdraw is complete and all other actions are lost

  10. ACID Transactions

  11. Transactions Properties • DBMS servers have built-in transaction support • Contemporary databases implement “ACID” transactions • ACID means: • Atomicity • Consistency • Isolation • Durability

  12. Atomicity • Atomicity means that • Transactions execute as a whole • DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are • Atomicity example: • Transfer funds between bank accounts • Either withdraw and deposit both execute successfully or none of them • In case of failure DB stays unchanged

  13. Consistency • Consistencymeans that • The database is in a legal state when the transaction begins and when it ends • Only valid data will be written to the database • Transaction cannot break the rules of the database, e.g. integrity constraints • Primary, foreign, alternate keys • Consistency example • Transaction cannot end with a duplicate primary key in a table

  14. Isolation • Isolationmeans that • Multiple transactions running at the same time not impact each other’s execution • Transactions don’t see other transaction’s uncommitted changes • Isolation level defines how deep transactions isolate from one another • Read committed, read uncommitted, repeatable read, serializable, etc. • Isolation example: • Manager can see the transferred funds on one account or the other, but never on both

  15. Durability • Durabilitymeans that • If a transaction is confirmed it become persistent • Cannot be lost or undone • Ensured through the use of database backups and transaction logs • Durability example: • After transfer funds and commit the power supply is lost • Transaction stays persistent

  16. ACID Transactions and RDBMS Servers • PopularRDBMS servers are transactional: • Oracle Database • Microsoft SQL Server • IBM DB2 • PostgreSQL • Borland InterBase / Firebird • All of the above servers support ACID transactions • MySQL can also run in ACID mode

  17. Concurrency Problems

  18. Scheduling Transactions • Serial schedule – the ideal case • An ordering of operations of the transactions so with no interleaving • Problem: Doesn’t allow for as much concurrency as we’d like • Conflicting operations • Two operations conflict if they 1) are from different transactions 2) access the same item, and 3) at least one of the transactions does a write operation to that item

  19. Serial Schedule – Example • T1: Adds 50 to the balance • T2: Subtracts 25 from the balance • T1 completes before T2 begins: no concurrency problems Time Trans. Step Value 1 T 1 Read balance 100 2 T 1 balance= 100 + 50 3 T 1 Write balance 150 4 T 2 Read balance 150 5 T 2 balance= 150 - 25 6 T 2 Write balance 125

  20. Serializable Transactions • Serializability • Want to get the effect of serial schedules, but allow for more concurrency • Serializable schedules • Equivalent to serial schedules • Produce same final result as serial schedule • Locking mechanisms can ensure serializability • Serializability is too expensive • Optimistic locking allows better concurrency

  21. Concurrency Problems • Problems from conflicting operations: • Dirty Read (Temporary Update) • A transaction updates an item, then fails • The item is accessed by another transaction before rollback • Non-Repeatable Read • A transactions reads an item twice and gets different values because of concurrent change • Phantom Read • A transaction executes a query twice, and obtains a different numbers of rows because another transaction inserted new rows meantime

  22. Concurrency Problems (2) • Problems from conflicting operations: • Lost Update • Two transactions update the same item • Second update overwrites the first (last wins) • Incorrect Summary • One transaction is calculating an aggregate function on some records while another transaction is updating them • The aggregate function calculate some values before updating and some after

  23. Dirty Read (Read Uncommitted) – Example Time Trans. Step Value 1 T 1 Read balance 100 balance= 100 + 50 2 T 1 3 T 1 Write balance 150 Uncommitted 4 T 2 Read balance 150 5 T 2 balance= 150 - 25 Undoes T1 6 T 1 Rollback 7 T 2 Write balance 125 • Update from T1 was rolled back, but T2 doesn’t know about it, so finally the balance is incorrect. T2 writes incorrect balance

  24. Lost Update – Example Time Trans. Step Value 1 T 1 Read balance 100 100 2 T 2 Read balance 3 T 1 balance= balance + 50 4 T 2 balance= balance - 25 150 5 T 1 Write balance 6 T 2 Write balance 75 • Update from T1 is lost because T2 reads balance before T1 was complete Lost update!!

  25. Concurrency Control Techniques

  26. Concurrency Control • The problem • Conflicting operations in simultaneous transactions may produce an incorrect results • What is concurrency control? • Managing simultaneous operations on the database without having them interfere with one another • Prevents conflicts when two or more users access database simultaneously

  27. Concurrency Control Techniques • Two basic concurrency control techniques: • Locking • Used in most RDBMS servers, e.g. Oracle, SQL Server, etc. • Timestamping • Both are conservative (pessimistic) approaches: delay transactions in case they conflict with other transactions • Optimistic methods assume conflict is rare and only check for conflicts at commit

  28. Locking • Transaction uses locks to deny access to shared data by the other transactions • Most widely used approach to ensure serializability • Generally, a transaction must claim a read (shared) or write (exclusive) lock on a data item before read or write • Lock prevents another transaction from modifying item or even reading it, in the case of a write lock • Deadlock is possible

  29. Timestamping • A unique identifier • Created by the DBMS • Indicates relative starting time of a transaction • Transactions ordered globally • Older transactions (earlier timestamps) get priority in the event of conflict • Conflict is resolved by rolling back and restarting transaction • No locks so no deadlock

  30. Locking Strategies

  31. Locking Strategies • Optimistic locking • Locks are not used • Conflicts are possible but are resolved before commit • High concurrency – scale well • Pessimistic locking • Use exclusive and shared locks • Transactions wait for each other • Low concurrency – does not scale

  32. Optimistic Locking • Optimistic locking means no locking • Based on assumption that conflicts are rare • It is more efficient to let transactions proceed without delays to ensure serializability • At commit, check is made to determine whether conflict has occurred • If there is a conflict, transaction must be rolled back and restarted • Allows greater concurrency than pessimistic locking

  33. Optimistic Locking Phases • Three phases • Read • Transaction reads the DB, does computations, then makes updates to a private copy of the DB (e.g. in the memory) • Validation • Make sure that transaction doesn’t cause any integrity/consistency problems • If no problems, transaction goes to write phase • If problems, changes are discarded and transaction is restarted • Write • Changes are made persistent to DB

  34. Pessimistic Locking • Assume conflicts are likely • Lock shared data to avoid conflicts • Transactions wait each other – does not scale well • Useshared and exclusive locks • Transactions must claim a read (shared) or write (exclusive) lock on a data item before read or write • Locks prevents another transaction from modifying item or even reading it, in the case of a write lock

  35. Locking – Basic Rules • If transaction has read lock on an item, the item can be read but not modified • If transaction has write lock on an item, the item can be both read and modified • Reads cannot conflict, so multiple transactions can hold read locks simultaneously on the same item • Write lock gives one transaction exclusive access to an item • Transaction can upgrade a read lock to a write lock, or downgrade a write lock to a read lock • Commits or rollbacks release the locks

  36. Deadlock • What is deadlock? • When two (or more) transactions are each waiting for locks held by the other to be released • Breaking a deadlock • Only one way to break deadlock: abort one or more of the transactions

  37. Dealing with Deadlock • Deadlock prevention • Transaction can’t obtain a new lock if the possibility of a deadlock exists • Deadlock avoidance • Transaction must obtain all the locks it needs before it starts • Deadlock detection and recovery • DB checks for possible deadlocks • If deadlock is detected, one of the transactions is killed, then restarted

  38. Lock Management • Lock and unlock requests are handled by the lock manager, stored in the “lock table” • Lock table entries store: • Number of transactions currently holding a lock • Type of lock held (shared or exclusive) • Pointer to queue of lock requests • Locking and unlocking have to be atomic operations • Lock upgrade: transaction that holds a shared lock can be upgraded to exclusive lock

  39. Locking Granularity • Size of data items chosen as unit of protection by concurrency control • Ranging from coarse to fine: • Entire database • File • Page (block) • Record • Field value of a record

  40. Coarse vs. Fine Granularity • Granularity is a measure of the amount of data the lock is protecting • Coarse granularity • Small number of locks protecting large segments of data, e.g. DB, file, page locks • Small overhead, small concurrency • Fine granularity • Large number of locks over small areas of data, e.g. table row of field in a row • More overhead, more concurrency • DBMS servers are “smart” and use both

  41. Transactions and Recovery

  42. Transactions and Recovery • Transactions represent basic unit of recovery • Recovery manager responsible for atomicity and durability • What happens at failure? • If transaction had not committed at failure time, recovery manager has to undo (rollback) any effects of that transaction for atomicity • If failure occurs between commit and database buffers being flushed to secondary storage, recovery manager has to redo (rollforward) transaction's updates

  43. Crash Before Completion – Sample Scenario • Application tries to transfer $100 • Read savings • new savings = current - 100 • Read checking • new checking = current + 100 • Write savings to DB • System crash before write of new checking balance

  44. Recovery from Crash • Rollback • Recover to the starting state: • Take snapshot (checkpoint) of starting state • E.g., initial bank balance (and all other states) • And keep a “redo” log • Alternative: keep an “undo” log • E.g., bank balance changed: old value was x • Resume (if recoverable) • Redo all committed actions (since last checkpoint) • Or undo all uncommitted actions

  45. Creating REDO Log • Keep a log of all database writes ON DISK (so that it is still available after crash) • <transaction ID>; <data item>; <new value> • (Tj; x=125) (Ti; y=56) • Actions must be idempotent (redoable) • NOT x = x + 100 • But don't write to the database yet • At the end of transaction execution • Add "commit <transaction ID>" to the log • Do all the writes to the database • Add "complete <transaction ID>" to the log

  46. Sample REDO Log File

  47. Recovering From a Crash • There are 3 phases in the recovery algorithm: • Analysis – scan the log forward to identify all transactions that were active, and all dirty pages in the buffer pool at the time of the crash • Redo – redoes all updates to dirty pages in the buffer pool, as needed, to ensure that all logged updates are in fact carried out and written to disk • Undo – all transactions that were active at the crash are undone, working backwards in the log • Some care must be taken to handle the case of a crash occurring during the recovery process!

  48. Transactions and SQL Language

  49. Transactions and SQL • Start a transaction • BEGIN TRANSACTION • Some databases assume implicit start • E.g. Oracle • Ending a transaction • COMMIT • Used to end a successful transaction and make changes “permanent” • ROLLBACK • “Undo” changes from an aborted transaction • May be done automatically when failure occurs

  50. Transactions in SQL Server: Example • We have a table with bank accounts: • We use a transaction to transfer money from one account into another CREATE TABLE ACCOUNT( id int NOT NULL, balancedecimal NOT NULL) CREATE OR REPLACE PROCEDURE sp_Transfer_Funds( from_account IN INT, to_account IN INT, ammount IN NUMBER) IS BEGIN BEGIN TRAN (example continues)

More Related