Transactions and concurrency
1 / 15

Transactions and Concurrency - PowerPoint PPT Presentation

  • Uploaded on

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

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

PowerPoint Slideshow about 'Transactions and Concurrency' - joanna

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

  • 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


    • 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


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


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


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


  • 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)



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 starting balance is £100

  • 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 starting balance is £100

  • 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 starting balance is £100timestamping

  • 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 starting balance is £100

  • excessive rollback

  • could cause more delay than a locking system

  • compare with starvation

Summary starting balance is £100

  • What is a Transaction

  • Transaction Integrity

  • Transactions and SQL

  • Concurrency Control