Transactions and concurrency
This presentation is the property of its rightful owner.
Sponsored Links
1 / 15

Transactions and Concurrency PowerPoint PPT Presentation


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

Transactions and Concurrency. Edel Sherratt. Overview. What is a Transaction Transaction Integrity Transactions and SQL Concurrency Control. What is a transaction. A useful piece of work For example: record customer order record a lodgement to a bank account record a withdrawal

Download Presentation

Transactions and Concurrency

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


Transactions and concurrency

Transactions and Concurrency

Edel Sherratt


Overview

Overview

  • What is a Transaction

  • Transaction Integrity

  • Transactions and SQL

  • Concurrency Control


What is a transaction

What is a transaction

  • A useful piece of work

  • For example:

    • record customer order

    • record a lodgement to a bank account

    • record a withdrawal

    • prepare a statement of account

  • Often embodied as a single execution of an application program


A transaction has steps

A transaction has steps

For example, a business to business customer order

  • Enter order data

  • Read customer record (or create a new record)

  • If (balance + order amount > credit then

    • reject order

      else

    • increase balance by order amount

    • store updated customer record

    • store the accepted order record


Transaction integrity

Transaction integrity

  • Preserve

    • atomicity

    • permanence

    • independence

  • To preserve transation integrity, the DBMS must recognize transaction boundaries – the logical beginning and end of each transaction


Acid properties

ACID properties

Atomicity

  • A transaction is an indivisible unit of work; it is either performed in its entirety, or is not performed at all.

    Consistency

  • Database goes from one consistent state (representation of reality) to another

    Isolation

  • Partial effect of one transaction invisible to other transactions; overall effect is as if transactions were executed sequentially

    Durability

  • Effects of a successful transaction are permanently recorded in the database


Transaction boundaries in sql

Transaction boundaries in SQL

  • The first SQL statement marks the beginning of a transaction

  • COMMIT marks the end of a successful transaction

  • ROLLBACK undoes the effects of a transaction

  • COMMIT and ROLLBACK mark transaction boundaries

  • The point at which a transaction is committed or rolled back is called a commit point

  • The next SQL statement after a COMMIT or ROLLBACK starts the next transaction


General structure embedded sql

General structure – embedded SQL

… other code, part of the application program …

SQL – the next statements are SQL

UPDATE borrowers

SET name = ‘Jones’ WHERE card_no =‘C12’

… other code …

if (some condition)

then SQL COMMIT

else SQL ROLLBACK


Concurrency control

Concurrency Control

  • Multiple concurrent transactions

  • Avoid undesirable interactions between transactions

  • Preserve ACID properties

  • No incomplete transactions, no interference between transactions

  • Effect of executing a collection of transactions concurrently is the same as if the transactions were run one by one in some order - serializability

  • Responsibility of DBMS


Example concurrent updates of a smith s savings account starting balance is 100

Example: concurrent updates of A. Smith’s savings account; starting balance is £100

  • lodge £25

    • retrieve customer record for A. Smith

    • add £25 to the balance

    • Store customer record

  • withdraw £50

    • retrieve customer record for A. Smith

    • subtract £50 from the balance

    • store customer record

What if the steps of these transactions were interleaved so that their execution order were ADBCEF?


Locking

Locking

  • Data is locked when a transaction begins, and released when the transaction is committed or rolled back

  • Lock level: database, table, tuple, attribute

  • Lock mode: shared (read access only), exclusive

  • Problems with locking

    • deadlock

    • starvation

  • Dealing with deadlock

    • prevention

    • detection and resolution


Timestamping

Timestamping

  • The system generates timestamps, usually a system clock time

  • Each transaction is stamped with its start time

  • Each data item is stamped with the times of the transactions that last read and last wrote the data item

  • Effect of running all the transactions concurrently is as if each transaction executed instantaneously at the time indicated by its timestamp

  • No transaction is allowed to read a value that was written in its future

  • No transaction writes a value to a data item if the old value of the data item is read in the transaction’s future


Concurrency control by timestamping

Concurrency control by timestamping

  • Writing: a transaction with timestamp t tries to write an item with read time tr and write time tw

    • perform the write if t ≥ trand t ≥ tw;if t ≥ tw set the write time t

    • do nothing if tr ≤ t < twthe transaction need not roll back since no other transaction has attempted to read the item between t and tw

    • roll back if t < tr

  • Reading: the transaction tries to read the item

    • perform the read if t ≥ tw;if t > trthe set the read time to t

    • roll back if t < tw


Potential problem

Potential problem

  • excessive rollback

  • could cause more delay than a locking system

  • compare with starvation


Summary

Summary

  • What is a Transaction

  • Transaction Integrity

  • Transactions and SQL

  • Concurrency Control


  • Login