what is a transaction n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
What is a Transaction? PowerPoint Presentation
Download Presentation
What is a Transaction?

Loading in 2 Seconds...

play fullscreen
1 / 33

What is a Transaction? - PowerPoint PPT Presentation


  • 237 Views
  • Uploaded on

What is a Transaction?. Logical unit of work Must be either entirely completed or aborted No intermediate states are acceptable. Figure 9.1. Example Transaction. Examine current account balance Consistent state after transaction No changes made to Database.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'What is a Transaction?' - whitfield


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
what is a transaction
What is a Transaction?
  • Logical unit of work
  • Must be either entirely completed or aborted
  • No intermediate states are acceptable

Figure 9.1

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

example transaction
Example Transaction
  • Examine current account balance
  • Consistent state after transaction
  • No changes made to Database

SELECT ACC_NUM, ACC_BALANCEFROM CHECKACCWHERE ACC_NUM = ‘0908110638’;

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

example transaction1
Example Transaction
  • Register credit sale of 100 units of product X to customer Y for $500
  • Consistent state only if both transactions are fully completed
  • DBMS doesn’t guarantee transaction represents real-world event

UPDATE PRODUCTSET PROD_QOH = PROD_QOH - 100WHERE PROD_CODE = ‘X’;

UPDATE ACCT_RECEIVABLE

SET ACCT_BALANCE = ACCT_BALANCE + 500WHERE ACCT_NUM = ‘Y’;

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

transaction properties acid
Transaction Properties (ACID)
  • Atomicity
    • All or nothing
  • Consistency provided
    • Database is consistent before and after transaction
    • Database not guaranteed consistent during a transaction
  • Isolation
    • Transaction data isolated from other transactions until its execution is complete
  • Durability
    • Permanently recorded in DB and must be protected

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

transaction management with sql
Transaction Management with SQL
  • Transaction support
    • COMMIT
    • ROLLBACK
  • Transaction begins with a BEGIN TRANSACTION and ends with COMMIT or ROLLBACK
  • At COMMIT point (synch point) all updates made permanent and locks released
  • Requires the use of a log or journal

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

transaction log
Transaction Log
  • Tracks all transactions that update database
  • Needed in ROLLBACK operation
  • May be used to recover from system failure
  • Log stores
    • Record for beginning of transaction
    • Each transaction component
      • Type of action (insert, delete, update)
      • Names of objects involved
      • Before and after images of affected objects
      • Pointers to previous and next entries
    • Commit Statement

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

transaction log example
Transaction Log Example

Table 9.1

Write-ahead Log Rule: Log is physically written

before COMMIT completes to enable restart

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

checkpoints
Checkpoints
  • How to know at restart time which transactions to undo and which ones to redo?
  • Checkpoints periodically taken
  • “Taking a checkpoint” involves force-writing buffers and writing a checkpoint record to the log consisting of all transactions in progress at checkpoint time
  • For example ...

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

algorithm for undo redo
Algorithm for Undo/Redo
  • At restart from checkpoint, set UNDO list to transactions that were in progress at the time
  • Set REDO list to null
  • Search forward through log starting from checkpoint
  • If BEGIN TRANSACTION found, add to UNDO list
  • If COMMIT found, move from UNDO to REDO

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

concurrency control
Concurrency Control
  • Coordinates simultaneous transaction execution in multiprocessing database
  • Potential problems in multiuser environments
    • Lost updates
    • Uncommitted data
    • Inconsistent retrievals

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

lost updates
Lost Updates

Table 9.2 Normal execution of two transactions

Table 9.3 Lost update

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

uncommitted data
Uncommitted Data

Table 9.4

Table 9.5

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

inconsistent retrievals
Inconsistent Retrievals
  • Also known as “dirty reads” or “unrepeatable reads”
  • Occurs when a transaction reads several values, some of which are being updated
  • Example: T1 sums the total quantity on hand while T2 transfers an amount on hand from one item to another (correcting an incorrect posting, for instance)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

inconsistent retrievals1
Inconsistent Retrievals

The two transactions

T2

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

inconsistent retrievals results with interleaved transactions
Inconsistent RetrievalsResults with interleaved transactions

Table 9.8

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

serializability
Serializability
  • It is possible for T1 followed by T2 to result in a different state than T2 followed by T1
  • But both would be correct (consistent) from the DB point of view
  • Transaction serializabilitymeans that transactions executing concurrently must be interleaved in such a way that the resulting DB state is equal to someserial execution of the same transactions
  • Goal is to avoid the concurrency problems (lost update, uncommitted data, inconsistent retrieval)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

the scheduler
The Scheduler
  • Establishes order of concurrent transaction execution
  • Interleaves execution of database operations to ensure serializability
  • Uses a protocol for producing serializable schedules:
    • Locking
    • Time stamping
    • Optimistic
  • Ensures efficient use of computer’s CPU

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

concurrency control with locking methods
Concurrency Control with Locking Methods
  • Lock guarantees current transaction exclusive use of data item
  • Acquire lock prior to access
  • Lock released when transaction is completed
  • DBMS automatically initiates and enforces locking procedures
  • Lock granularity indicates level of lock use

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

locks
Locks
  • Read (sharing) or Write (exclusive)
  • At various levels: DB, table, page, row, field
  • Many Read locks simultaneously possible for a given item, but only one Write lock
  • Transaction that requests a lock that cannot be granted must wait
  • Possible to upgrade Read lock to Writelock or downgrade Writelock to Read lock
  • Locks released at commit point (or earlier)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

shared exclusive locks
Shared/Exclusive Locks
  • Shared (Read)
    • Exists when concurrent transactions granted READ access
    • Issued when transaction wants to read and exclusive lock not held on item
  • Exclusive (Write)
    • Exists when access reserved for locking transaction
    • Used when potential for conflict exists
    • Issued when transaction wants to update unlocked data

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

problems with locking
Problems with Locking
  • Transaction schedule may not be serializable
    • Managed through two-phase locking
  • Schedule may create deadlocks
    • Managed by using deadlock detection and prevention techniques

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

two phase locking protocol 2pl
Two-Phase Locking Protocol (2PL)
  • Growing phase: acquire all locks needed
  • Shrinking phase: after releasing a lock, acquire no new locks
  • Consequently
    • No unlock operation can precede a lock operation in the same transaction
    • No data are affected until all locks are obtained
  • 2PL solves the 3 problems of concurrency

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

two phase locking protocol
Two-Phase Locking Protocol

Figure 9.6

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

deadlock
Deadlock
  • Also called deadly embrace
  • “Occurs when two transactions wait for each other to unlock data”
  • Wrong!
    • eg, T1 waits for T2, T2 waits for T3, T3 waits for T1
  • Notation: T1  T2 means T1 waits for data held by T2
  • A system is in deadlock if there is a set of waiting transactions {T0, T1, …, Tn} such that T0  T1, T1  T2, … , Tn  T0

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

deadlock detection
Wait-for-graphDeadlock Detection

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

recovery from deadlock
Recovery from Deadlock

One or more transactions must be aborted

  • Determine transactions to roll back
    • Want to incur minimum “cost”
    • May be based on time running, time left, amount of data used, how many transactions are involved in rollback (cascades)
  • Total or partial rollback
  • Starvation
    • Can happen that same transaction is always chosen as victim
    • Use the number of times rolled back in determining the cost

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

deadlock prevention
Deadlock Prevention
  • Could require all locks to be acquired at once
    • but may not always know what is needed
    • potentially inefficient -- many items locked unnecessarily for possibly long time
  • Ordering of data items
    • once a transaction locks an item, it cannot lock anything occurring earlier in the ordering
  • Preemption and rollback with timestamps
    • wait-die
    • wound-wait

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

concurrency control with time stamping methods
Concurrency Control with Time Stamping Methods
  • Assigns global unique time stamp to each transaction
  • Produces order for transaction submission
  • Properties
    • Uniqueness
    • Monotonicity
  • Some time stamping necessary to avoid “livelock”: where a transaction cannot acquire any locks even though the DBMS is not deadlocked (eg, unfair waiting algorithm)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

deadlock prevention with time stamps
Deadlock Prevention with Time Stamps
  • Wait-die
    • If T1 requests item locked by T2, then T1 is allowed to wait only if it is older than T2 (smaller time stamp). Otherwise T1 is rolled back (dies)
  • Wound-wait
    • If T1 requests item locked by T2, then T1 is allowed to wait only if T1 is younger than T2 (larger time stamp). Otherwise T2 is rolled back (wounded by the older transaction)
  • Both avoid starvation, since eventually a failing transaction will be the oldest

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

concurrency control with optimistic methods
Concurrency Control with Optimistic Methods
  • Assumes most database operations do not conflict
  • Transaction executed without restrictions until committed
  • Transactions execute in 3 Phases in order:
    • Read Phase
    • Validation Phase
    • Write Phase
  • Transactions are still interleaved, but may have to be rolled back

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

phases in validation based control
Phases in Validation-based Control
  • Read phase
    • Transaction reads data and stores in local variables
    • Any writes are made to local variables without updating the actual DB
  • Validation phase
    • Validation test performed to see whether DB can be changed without violating serializability
    • Relies on time stamping each transaction at each phase
  • Write phase
    • If the validation test is successful, the transaction updates the actual DB. Otherwise it is rolled back.

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

database recovery management
Database Recovery Management
  • Restores a database to previously consistent state
  • Based on the atomic transaction property
  • Level of backup
    • Full backup
    • Differential
    • Transaction log

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

transaction recovery
Transaction Recovery
  • Deferred-write and Deferred-update
    • Changes are written to the transaction log
    • Database updated after transaction reaches commit point
  • Write-through
    • Immediately updated by during execution
    • Before the transaction reaches its commit point
    • Transaction log also updated
    • Transaction fails, database uses log information

to ROLLBACK

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel