Transaction

1. Indra Budi indra@cs.ui.ac.id Transaction

2. Exercise • A series of actions to be taken on the database such that either all actions are completed successfully, or none of them can be completed, is known as a(n): a.) checkpoint. b.) log. c.) lock. d.) transaction. e.) concurrent.

3. Exercise • When two transactions are being processed against the database at the same time, a.) they are called concurrent transactions. b.) they are usually interleaved. c.) they always result in a lost update problem. d.) one must be rolled back. e.) both a and b

4. Exercise • …. means a transaction executes when all actions of the transaction are completed fully, or none are. This means there are no partial transactions (such as when half the actions complete and the other half do not). • …. involves beginning a transaction with a ’consistent’ database, and finishing with a ’consistent’ database. For example, in a bank database, money should never be ”created” or ”deleted” without an appropriate deposit or withdrawal. Every transaction should see a consistent database. • ….ensures that a transaction can run independently, without considering any side effects that other concurrently running transactions might have. • ….define the persistence of committed data: once a transactioncommits, the data should persist in the database even if the system crashesbefore the data is written to non-volatile storage. • A ….is a series of (possibly overlapping) transactions. • A …..occurs when a transaction reads a database object that has beenmodified by another not-yet-committed transaction. • A ….over a set S of transactions is a schedule whose effecton any consistent database instance is identical to that of some completeserial schedule over the set of committed transactions in S. • A …..is one in which a transaction can commit only afterall other transactions whose changes it has read have committed.

5. Recoverable schedule • Serializable schedule • Schedule • Atomicity • Durability • Isolation • Dirty Read • Unrepeatable problem • Consistency • Cascading rollback

6. Exercise • Do exercise 17.23 Elmasri pages 581

7. A B 25 25 125 250 125 250 250 250 Schedule C is Serializable ? T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B); B  B+100; Write(B); Read(B);B  B2; Write(B);

8. A B 25 25 125 250 50 150 250 150 Is Schedule D serializable ? T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B);

9. Database Administration • All large and small databases need database administration • Data administration refers to a function concerning all of an organization’s data assets • Database administration (DBA) refers to a person or office specific to a single database and its applications

10. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository

11. Managing Database Structure • DBA’s tasks: • Participate in database and application development • Assist in requirements stage and data model creation • Play an active role in database design and creation • Facilitate changes to database structure • Seek community-wide solutions • Assess impact on all users • Provide configuration control forum • Be prepared for problems after changes are made • Maintain documentation

12. Concurrency Control • Concurrency control ensures that one user’s work does not inappropriately influence another user’s work • No single concurrency control technique is ideal for all circumstances • Trade-offs need to be made between level of protection and throughput

13. Atomic Transactions • A transaction, or logical unit of work (LUW), is a series of actions taken against the database that occurs as an atomic unit • Either all actions in a transaction occur or none of them do

14. Example: Atomic Transaction

15. Example: Atomic Transaction

16. Concurrent Transaction • Concurrent transactions refer to two or more transactions that appear to users as they are being processed against a database at the same time • In reality, CPU can execute only one instruction at a time • Transactions are interleaved meaning that the operating system quickly switches CPU services among tasks so that some portion of each of them is carried out in a given interval • Concurrency problems: lost update and inconsistent reads

17. Example: Concurrent Transactions

18. Example: Lost Update Problem

19. Concurrency Control and Locking • We need a way to guarantee that our concurrent transactions can be serialized. Locking is one such means. • Locking is done to data items in order to reserve them for future operations. • A lock is a logical flag set by a transaction to alert other transactions the data item is in use.

20. Resource Locking • Resource locking prevents multiple applications from obtaining copies of the same record when the record is about to be changed

21. Characteristics of Lock • Locks may be applied to data items in two ways:Implicit Locks are applied by the DBMSExplicit Locks are applied by application programs. • Locks may be applied to: • a single data item (value) • an entire row of a table • a page (memory segment) (many rows worth) • an entire table • an entire database • This is referred to as the Lock granularity • Locks may be of type types depending on the requirements of the transaction: • An Exclusive Lock prevents any other transaction from reading or modifying the locked item. • A Shared Lock allows another transaction to read an item but prevents another transaction from writing the item.

22. Example: Explicit Locks

23. This is a protocol which ensures conflict-serializable schedules. Phase 1: Growing Phase transaction may obtain locks transaction may not release locks Phase 2: Shrinking Phase transaction may release locks transaction may not obtain locks The protocol assures serializability. It can be proved that the transactions can be serialized in the order of their lock points (i.e. the point where a transaction acquired its final lock). The Two-Phase Locking Protocol

24. 2PL Examples • User A places an exclusive lock on the balance • User A reads the balance • User A deducts $100 from the balance • User B attempts to place a lock on the balance but fails because A already has an exclusive lock User B is placed into a wait state • User A writes the new balance of $100 • User A releases the exclusive lock on the balanceUser B places an exclusive lock on the balance • User B reads the balance • User B deducts $100 from the balance • User B writes the new balance of $100

25. 2PL Example • User A places a shared lock on item raise_rate • User A reads raise_rate • User A places an exclusive lock on item Amy_salary • User A reads Amy_salary • User B places a shared lock on item raise_rate • User B reads raise_rate • User A calculates a new salary as Amy_salary * (1+raise_rate) • User B places an exclusive lock on item Bill_salary • User B reads Bill_salary • User B calculates a new salary as Bill_salary * (1+raise_rate) • User B writes Bill_salary • User A writes Amy_salary • User A releases exclusive lock on Amy_salary • User B releases exclusive lock on Bill_Salary • User B releases shared lock on raise_rate • User A releases shared lock on raise_rate

26. Deadlock • User A places an exclusive lock on item 1001 • User B places an exclusive lock on item 2002 • User A attempts to place an exclusive lock on item 2002 User A placed into a wait state • User B attempts to place an exclusive lock on item 1001 User B placed into a wait state • This is called a deadlock. One transaction has locked some of the resources and is waiting for locks so it can complete. A second transaction has locked those needed items but is awaiting the release of locks the first transaction is holding so it can continue.

27. Deadlock • Deadlock, or the deadly embrace, occurs when two transactions are each waiting on a resource that the other transaction holds • Preventing deadlock • Allow users to issue all lock requests at one time • Require all application programs to lock resources in the same order • Breaking deadlock • Almost every DBMS has algorithms for detecting deadlock • When deadlock occurs, DBMS aborts one of the transactions and rollbacks partially completed work

28. Another Deadlock Example

29. Optimistic/Pessimistic Locking • Optimistic locking assumes that no transaction conflict will occur • DBMS processes a transaction; checks whether conflict occurred • If not, the transaction is finished • If so, the transaction is repeated until there is no conflict • Pessimistic locking assumes that conflict will occur • Locks are issued before transaction is processed, and then the locks are released • Optimistic locking is preferred for the Internet and for many intranet applications

30. Example: Optimistic Locking

31. Example: Pessimistic Locking

32. Final Test • Scheduled on Dec, 29th 2004, 09.00 – 11.00 WIB • May open all notes written by hand, no copies, no print-out, close textbook • Material from “Introduction to DB” to Concurrency Control

33. Next Wednesday (Dec 15th) • Quiz • Close Books & Close Notes • Material • SQL (Join, Aggregation, Grouping, Having, View) • Transactions Processing & Concurrency Control