LIS 384K.11 Database-Management Principles and Applications

1. LIS 384K.11Database-Management Principles and Applications Transaction Analysis and Management, & Concurrency Control R. E. Wyllys Last revised 2002 Apr 22 GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

2. Transaction Analysis and Management, & Concurrency Control • Transaction analysis and management (TAM) and concurrency control (CC) • Pose no problems in the management of single-user DBs • Are extremely important in the management of multi-user DBs GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

3. Transaction Analysis and Management, & Concurrency Control • Transaction analysis and management (TAM) and concurrency control (CC) • Are sets of procedures aimed at • Preventing concurrent changes to a record, and thus avoiding a resultant loss of data • Preventing initial steps in an uncompleted transaction from corrupting records in files involved in the transaction • Vary somewhat in their implementation among different DBMSs • Nevertheless, basic principles of TAM and CC exist and apply to all multi-user DBMSs GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

4. Transaction Analysis and Management, & Concurrency Control • We can say that the goal of TAM and CC is to keep databases consistent. • A consistent DB is one in which all data-integrity constraints are satisfied, i.e., in which all relationships among the data are correct and there are no mismatches. • Note: This does not mean that there can be no errors in the DB, only that if there are errors, they are consistent with one another. • Example: If "Simth", instead of "Smith", appears throughout the DB, the DB would be consistent, even though the particular spelling, Simth, is incorrect. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

5. Transaction Analysis and Management, & Concurrency Control • In multi-user DBs • The critical CC issue is preventing two or more users from making concurrent changes to the same record • The critical TAM issue is ensuring that if all the steps in a complicated transaction cannot be completed, all tentative changes caused by the initial steps in the transaction are undone: i.e., ensuring that, in DB parlance, they are "rolled back". GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

6. Concurrency Control • What happens if two or more users make concurrent changes (i.e., updates) to the same record? • Each user is working with a copy of the original record that is in the DB • When one of the users finishes changing her copy, she saves it to the DB, replacing the original record • When, a microsecond or more later, the other user finishes his changes, he saves his copy to the DB, replacing the record as changed by the first user • Result: The first user's changes are lost! • THIS IS BAD! It must be avoided! GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

7. How Can We Avoid Lost Changes? • How can the error of lost changes (i.e., updates) be avoided? • One way is for the DBMS to allow only one user access to a given record at any one time. That is, the DBMS blocks, or locks out, all other potential users as soon as one user accesses the record, and keeps them locked out till that user releases the record. • This can work satisfactorily with databases in which there is low activity on individual records, i.e., databases in which the probability that more than one user will try to access a given record during a given short interval (e.g., 2 minutes) is quite low (e.g., less than 1%). • Many DBs have this kind of low activity, and can use the total lock-out method successfully. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

8. How Can We Avoid Lost Changes? • Another way of avoiding the error of lost updates is to distinguish between "permission to read" a record and "permission to update" a record. • The right to do no more than read a record is called a "read-only" permission. • The right to change (update) a record, i.e., to re-write the record with one or more modifications, is called a "write" or "update" permission. • With this distinction, the DBMS can, for a given record, grant an update permission to only one user at a time, and can grant all other users merely "read-only" permissions till such time as no update permission is in effect. • This method works well with DBs in which there is high activity on individual records. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

9. How Can We Avoid Lost Changes? • Granting update-access to a record to only one user at a time involves further complications. For example, • For a variety of practical reasons, it is often more efficient for the DBMS to read, or write to, a group of records at a time rather than to just one record. • This can occur if, for example, several records are stored together in one physical file and must be retrieved as a unit. In this case, if any change is made, the several records must be written back to the physical file as a unit. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

10. How Can We Avoid Lost Changes? • Also it can often happen that a DBMS will need to retrieve several different records simultaneously, modify them, and then return ("write back") the modified records to the DB. • Such situations arise frequently in the processing of transactions (which we are about to discuss) and clearly complicate the business of granting read-only-access and update-access to records. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

11. Transaction Analysis and Management • A transaction is a sequence of steps that constitute some well-defined business activity. Examples: • Sale of an item to a customer • Giving an employee a raise in pay • Ordering a piece of equipment • Charging out a book to a library user GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

12. Example of a Transaction • Consider some possible steps in charging out a book to a library user. You need to: • Identify the book and make a copy of the book's record from the circulation file in preparation for entering further data about this circulation transaction • Establish that the book is available for borrowing; e.g., • Book is not a non-circulating item • Book was properly charged in after last previous time it was borrowed • If not, the book's record in circulation file must be suitably modified to reflect the book's having been returned • Book is not on hold for another user GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

13. Example of a Transaction (cont'd) • Further possible steps in charging out a book to a library user. You need to • Identify the borrower • Establish that the borrower is permitted to charge out items; e.g., • Borrower is registered student in current semester • Borrower has no unpaid fines outstanding • Establish the loan period • Determine how book's charge periods interact with borrower's status (e.g., undergraduate students may borrow books for 2 weeks, but faculty members may borrow books for remainder of semester unless book is limited to 3-day charge period) GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

14. Example of a Transaction (cont'd) • Still more possible steps in charging out a book to a library user. You need to • Specify the return date • Enter data from the preceding steps into the working copy of the book's record. • Place this copy in the circulation file in place of the existing book record GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

15. Uncompleted Transactions • The sequence of steps in a transaction must be either completed or else cancelled entirely. Using the book-charging example, • Suppose you discover, after several earlier steps, that the borrower has unpaid fines outstanding and is thus ineligible to borrow books • Then you must discard the changes made in the working copy of the book's record from the circulation file, and you must ensure that the record in the circulation file is not changed GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

16. Uncompleted Transactions • In what ways can transactions fail to be completed? • In the book-charging example, if the whole transaction cannot be completed, it is easy to discard the changes made in the working copy of the book's record in the circulation file. • But in more complicated transactions, involving several files, changes in some of the files may have been made along the way through the transaction. • If the whole transaction cannot be completed, then these intermediate changes must be undone, i.e., they must be rolled back. • If the intermediate changes are not rolled back, some of the files may, incorrectly, remain changed; i.e., the files will have been corrupted. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

17. Uncompleted Transactions • In the book-charging example, the whole transaction failed to be completed because one of the steps showed that the would-be borrower was ineligible to borrow. • This is one way in which transactions can fail to be completed. Other ways include • Power failure • Hardware failure • Software problems • Someone involved in the transaction changes his or her mind about going through with the transaction (e.g., decides not to borrow the book after all, decides to buy a different item) GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

18. Transaction Analysis and Management & Concurrency Control • Transaction analysis and management, and concurrency control (TAMCC) • Are sets of procedures aimed at • Preventing concurrent changes to a record, and thus avoiding a resultant loss of data • Preventing initial steps in an uncompleted transaction from corrupting records in files involved in the transaction • Vary somewhat in their implementation among different DBMSs • Nevertheless, basic principles of TAMCC exist and apply to all multi-user DBMSs GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

19. Transaction Analysis and Management & Concurrency Control • We can say that the goal of TACC is to keep databases consistent. • A consistent DB is one in which all data-integrity constraints are satisfied, i.e., in which all relationships among the data are correct and there are no mismatches. • Note: This does not mean that there can be no errors in the DB, only that if there are errors, they are consistent with one another. • Example: If "Simth", instead of "Smith", appears throughout the DB, the DB would be consistent, even though the particular spelling, Simth, is incorrect. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

20. Properties of Transactions • Desirable properties of transactions include • Atomicity • Durability • Serializability • Isolation • These are properties that well designed DBMSs strive to achieve in their functioning GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

21. Properties of Transactions (cont'd) • Atomicity • Each transaction must be treated as a single logical unit, all of whose steps must be complete; and if even one step fails, the transaction must be aborted, i.e., any already finished steps must be rolled back. • Durability • A transaction, if successful, must take the DB from one consistent state to another consistent state. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

22. Properties of Transactions (cont'd) • Serializability • Concurrent transactions must be handled by the DBMS as though they were executed in sequence, i.e., in serial order. • Isolation • Any data (at a minimum, fields; possibly records or larger units) used by one transaction must not be used by another transaction till the first transaction has been be completed. • Note: Simultaneous read-only transactions are okay, but any set of transactions that are not purely read-only transactions needs to be handled by the DBMS in such a way as to achieve isolation GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

23. Delimiting Transactions • An essential part of TAM is to identify clearly the start and the end of a transaction • SQL does this by requiring • That any SQL statement that manipulates a field, record, file, etc., be deemed to be the start of a transaction; and • That the transaction be deemed to continue till the special SQL statement COMMIT is received, or till the statement ROLLBACK is used to cancel the transaction and undo whatever parts of it have been completed • In many implementations of SQL the statement BEGIN (or BEGIN TRANSACTION) may be used to mark the start of a transaction explicitly GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

24. Transaction Logs • A transaction log is • A basic tool for good transaction processing and security • A separate table (or tables) established by the DBMS to record steps in transactions as they are taken. This table provides • Backup (e.g., in case of power failure) • Support for rolling back (i.e., undoing) the steps if the the whole transaction cannot be completed GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

25. Two-Phase Locking From: Rob, P.; Coronel, C. Database Systems. 4th ed. P. 426. GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

26. Transactions Can Be Numerous, Frequent, and Complex GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications