polimd/Data Bases 2/lesson_01.md

DB2 - lesson 01

Paraboschi

6 October 2015

1. Transaction Model

Transaction Properties and Mechanisms

• ##### Atomicity
  • Abort-rollback-restart
  • Commit protocols
• ##### Consistency
  • Integrity checking of the DBMS
• ##### Isolation
  • Concurrency control
• ##### Durability
  • Recovery management

Corresponding modules

• ##### Atomicity and Durability
  • Reliability Manager
• ##### Isolation
  • Concurrency Control System

• Consistency

  • Integrity Control System at query execution time (with DDL Compilers support)

    2. Concurrency

    Advantages of Concurrency

Modern computers are capable of executing different processes in a concurrent way, Concurrency is also important in DBMS because it allows multiple Transactions at the same time

ex: Two students enroll contemporarily on the Politecnico site.

Concurrency is made by

• time sharing on a single processor
• or sharing the processes on multiple CPU cores.

Concurrenct executions

A problem may rise when we heve two or more concurrent operations modifying the same data.

There are several ways of managing the problem:

• ##### Serial executions The two operations are queued and executed separately in time.

• Interleaved execution

• Nested execution

  ###Concurrency Problems ####Lost update

  T1: R(D,V1)
  V1 = V1 + 3
  T2: R(D,V2)
  V2 = V2 + 6
  T1: W(V1,D)     D=103
  T2: W(V2,D)     D=106!
  

  ####Dirty read

  T1: R(X,V1)
  T1: V1 = V1+3
  T1: W(V1,X)     D=103
  T2:R(X,V2)
  T1: ROLLBACK
  T2: V2 = V2+6
  T2: W(V2,D)     D=109
  

  ####Non-repeatable read

  T1: R(D,V1)
  T2: R(D,V2)
  T2: V2 = V2 + 6
  T2: W(V2,D)      D=106
  T1: R(D,V3)     V3<>V1!
  

  ####Ghost update

  T1: R(D,V1)
  T2: R(D,V2)
  T2: V2 = V2 + 6
  T2: W(V2,D)      D=106
  T1: R(D,V3)     V3<>V1!