Atomicity indicates the permanence of the database's consistent state.

DBF-Lecture11-Chapter12.ppt

Database Principles: Fundamentals of Design, Implementations and Management

Lecture11- CHAPTER 12: Transaction Management and Concurrency Control

Presented by Rabia Cherouk

*

Objectives

In this chapter, you will learn:

About database transactions and their properties

What concurrency control is and what role it plays in maintaining the database’s integrity

What locking methods are and how they work

How stamping methods are used for concurrency control

How optimistic methods are used for concurrency control

How database recovery management is used to maintain database integrity

*

What is a Transaction?

A transaction is a logical unit of work that must be either entirely completed or aborted

Successful transaction changes database from one consistent state to another

One in which all data integrity constraints are satisfied

Most real-world database transactions are formed by two or more database requests

Equivalent of a single SQL statement in an application program or transaction

Same as Fig. 12.1 in your book

*

Same as Fig. 12.1 in your book

*

Evaluating Transaction Results

Not all transactions update the database

SQL code represents a transaction because database was accessed

Improper or incomplete transactions can have devastating effect on database integrity

Some DBMSs provide means by which user can define enforceable constraints

Other integrity rules are enforced automatically by the DBMS

Same as Fig. 12.2 in your book

*

Figure 9.2

Same as Fig. 12.2 in your book

*

Transaction Properties

All transactions must display atomicity, consistency, durability and serializability (ACIDS).

Atomicity

All operations of a transaction must be completed

Consistency

Permanence of database’s consistent state

Isolation

Data used during transaction cannot be used by second transaction until the first is completed

*

Transaction Properties (cont..)

Durability

Once transactions are committed, they cannot be undone

Serializability

Concurrent execution of several transactions yields consistent results

Multiuser databases are subject to multiple concurrent transactions

*

Transaction Management with SQL

ANSI (American National Standard Institute) has defined standards that govern SQL database transactions

Transaction support is provided by two SQL statements: COMMIT and ROLLBACK

Transaction sequence must continue until:

COMMIT statement is reached

ROLLBACK statement is reached

End of program is reached

Program is abnormally terminated

*

The Transaction Log

A DBMS uses a Transaction log to store:

A record for the beginning of transaction

For each transaction component:

Type of operation being performed (update, delete, insert)

Names of objects affected by transaction

“Before” and “after” values for updated fields

Pointers to previous and next transaction log entries for the same transaction

Ending (COMMIT) of the transaction

Table 12.1 in your book

*

The Transaction Log

Table 12.1 in your book

*

Concurrency Control

Is the coordination of simultaneous transaction execution in a multiprocessing database

Objective is to ensure serializability of transactions in a multiuser environment

Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems

Lost updates

Uncommitted data

Inconsistent retrievals

*

Lost Updates

Lost update problem:

Two concurrent transactions update same data element

One of the updates is lost

Overwritten by the other transaction

Lost Updates

*

Lost Updates (cont..)

*

*

Uncommitted Data

Uncommitted data phenomenon:

Two transactions executed concurrently

First transaction rolled back after second already accessed uncommitted data

Uncommitted Data

*

Uncommitted Data (cont..)

*

*

Inconsistent Retrievals

Inconsistent retrievals:

First transaction accesses data

Second transaction alters the data

First transaction accesses the data again

Transaction might read some data before they are changed and other data after changed

Yields inconsistent results

*

*

*

The Scheduler

Special DBMS program

Purpose is to establish order of operations within which concurrent transactions are executed

Interleaves execution of database operations:

Ensures serializability

Ensures isolation

Serializable schedule

Interleaved execution of transactions yields same results as serial execution

The Scheduler (cont..)

Bases its actions on concurrency control algorithms

Ensures computer’s central processing unit (CPU) is used efficiently

Facilitates data isolation to ensure that two transactions do not update same data element at same time

*

*

Database Recovery Management

Database recovery

Restores database from given state, usually inconsistent, to previously consistent state

Based on atomic transaction property

All portions of transaction treated as single logical unit of work

All operations applied and completed to produce consistent database

If transaction operation cannot be completed, transaction must be aborted, and any changes to database must be rolled back (undone)

Transaction Recovery

Makes use of deferred-write and write-through techniques

Deferred write

Transaction operations do not immediately update physical database

Only transaction log is updated

Database is physically updated only after transaction reaches its commit point using transaction log information

*

*

Transaction Recovery (cont..)

Write-through technique

Database is immediately updated by transaction operations during transaction’s execution, even before transaction reaches its commit point

Recovery process

Identify last checkpoint

If transaction was committed before checkpoint

Do nothing

If transaction committed after last checkpoint

DBMS redoes the transaction using “after” values

If transaction had ROLLBACK or was left active

Do nothing because no updates were made

Transaction Recovery (cont..)

*

*

Summary

Transaction: sequence of database operations that access database

Logical unit of work

No portion of transaction can exist by itself

Five main properties: atomicity, consistency, isolation, durability, and serializability

COMMIT saves changes to disk

ROLLBACK restores previous database state

SQL transactions are formed by several SQL statements or database requests

*

Summary (cont..)

Transaction log keeps track of all transactions that modify database

Concurrency control coordinates simultaneous execution of transactions

Scheduler establishes order in which concurrent transaction operations are executed

Lock guarantees unique access to a data item by transaction

Two types of locks: binary locks and shared/exclusive locks

*

Summary (cont..)

Serializability of schedules is guaranteed through the use of two-phase locking

Deadlock: when two or more transactions wait indefinitely for each other to release lock

Three deadlock control techniques: prevention, detection, and avoidance

Time stamping methods assign unique time stamp to each transaction

Schedules execution of conflicting transactions in time stamp order

*

Summary (cont..)

Optimistic methods assume the majority of database transactions do not conflict

Transactions are executed concurrently, using private copies of the data

Database recovery restores database from given state to previous consistent state

CHAPTER 12: Transaction Management and Concurrency Control

ADDITIONAL SLIDES pages 635 to 644 in your

Book..

*

*

Two-Phase Locking to Ensure Serializability (cont..)

Governed by the following rules:

Two transactions cannot have conflicting locks

No unlock operation can precede a lock operation in the same transaction

No data are affected until all locks are obtained—that is, until transaction is in its locked point

*

Concurrency Control

with Locking Methods

Lock

Guarantees exclusive use of a data item to a current transaction

Required to prevent another transaction from reading inconsistent data

Lock manager

Responsible for assigning and policing the locks used by transactions

*

Lock Granularity

Indicates level of lock use

Locking can take place at following levels:

Database

Table

Page

Row

Field (attribute)

*

Lock Granularity (cont..)

Database-level lock

Entire database is locked

Table-level lock

Entire table is locked

Page-level lock

Entire diskpage is locked

Row-level lock

Allows concurrent transactions to access different rows of same table

Even if rows are located on same page

Field-level lock

Allows concurrent transactions to access same row as long as they

Require the use of different fields (attributes) within the row

Fig 12.3 in your book

*

Fig 12.3 in your book

Fig 12.4 in your book

*

Fig 12.4 in your book

Fig. 12.5 in your book

*

Lock Granularity (cont..)

Fig. 12.5 in your book

Fig. 12.6 in your book

*

Lock Granularity (cont..)

Fig. 12.6 in your book

*

Lock Types

Binary lock

Two states: locked (1) or unlocked (0)

Exclusive lock

Access is specifically reserved for transaction that locked object

Must be used when potential for conflict exists

Shared lock

Concurrent transactions are granted read access on basis of a common lock

Table 12.10 in your book

*

Table 12.10 in your book

*

Two-Phase Locking to Ensure Serializability

Defines how transactions acquire and relinquish locks

Guarantees serializability, but does not prevent deadlocks

Growing phase

Transaction acquires all required locks without unlocking any data

Shrinking phase

Transaction releases all locks and cannot obtain any new lock

Deadlocks (cont..)

*

*

Deadlocks

Condition that occurs when two transactions wait for each other to unlock data

Possible only if one of the transactions wants to obtain an exclusive lock on a data item

No deadlock condition can exist among shared locks

*

Table 12.11 in your book

Deadlocks (cont..)

*

Deadlocks (cont..)

Three techniques to control deadlock:

Prevention

Detection

Avoidance

Choice of deadlock control method depends on database environment

Low probability of deadlock, detection recommended

High probability, prevention recommended

*

Concurrency Control

with Time Stamping Methods

Assigns global unique time stamp to each transaction

Produces explicit order in which transactions are submitted to DBMS

Uniqueness

Ensures that no equal time stamp values can exist

Monotonicity

Ensures that time stamp values always increase

*

Wait/Die and Wound/Wait Schemes

Wait/die

Older transaction waits and younger is rolled back and rescheduled

Wound/wait

Older transaction rolls back younger transaction and reschedules it

Wait/Die and Wound/Wait Schemes (cont..)

*

*

Concurrency Control

with Optimistic Methods

Optimistic approach

Based on assumption that majority of database operations do not conflict

Does not require locking or time stamping techniques

Transaction is executed without restrictions until it is committed

Phases: read, validation, and write

*

*

*

*

*

*

*

*

*

Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com