Lis 384k 11 database management principles and applications
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

LIS 384K.11 Database-Management Principles and Applications PowerPoint PPT Presentation


  • 81 Views
  • Uploaded on
  • Presentation posted in: General

LIS 384K.11 Database-Management Principles and Applications. Transaction Analysis and Management, & Concurrency Control R. E. Wyllys Last revised 2002 Apr 22. Transaction Analysis and Management, & Concurrency Control. Transaction analysis and management (TAM) and concurrency control (CC)

Download Presentation

LIS 384K.11 Database-Management Principles and Applications

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lis 384k 11 database management principles and applications

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


Transaction analysis and management concurrency control

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


Transaction analysis and management concurrency control1

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


Transaction analysis and management concurrency control2

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


Transaction analysis and management concurrency control3

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


Concurrency control

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


How can we avoid lost changes

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


How can we avoid lost changes1

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


How can we avoid lost changes2

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


How can we avoid lost changes3

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


Transaction analysis and management

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


Example of a transaction

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


Example of a transaction cont d

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


Example of a transaction cont d1

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


Uncompleted transactions

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


Uncompleted transactions1

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


Uncompleted transactions2

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


Transaction analysis and management concurrency control4

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


Transaction analysis and management concurrency control5

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


Properties of transactions

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


Properties of transactions cont d

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


Properties of transactions cont d1

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


Delimiting transactions

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


Transaction logs

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


Two phase locking

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


Transactions can be numerous frequent and complex

Transactions Can Be Numerous, Frequent, and Complex

GSLIS - The University of Texas at Austin

LIS 384K.11, Database-Management Principles and Applications


  • Login