Erstellung einer OER über das Thema Transaktions-verarbeitung

Basierend auf einem OER von Christopher Olbrich lizenziert unter CC BY-SA 4.0

Christopher Olbrich (Lizenzinformationen)
6. Juli 2018

Motivation


Verteilung der Seminarthemen

  • Ihre Zuteilung: Einführung in Transaktionsverarbeitung einschließlich ACID
  • Transaktionsverarbeitung? Da war doch was!
  • Genau: Datenmanagement im 2. Semester
  • Was war nochmal genau Transaktionsverarbeitung?

Über Transaktionsverarbeitung informieren

  • Google
  • Datenmanagement-Skript
  • Youtube

Nach OER zum Thema Transaktionsverarbeitung suchen

Golden Circle

Golden Circle by Creative Commons under CC BY-SA 4.0; from Creative Commons Search Logo


MIT

Database Systems

Database Systems by MIT under CC BY-NC-SA 4.0; from MIT OpenCourseWare


Youtube

ACID database transactions

ACID database transactions by Jonathan Geisler under CC BY 3.0; from YouTube


Wikipedia

ACID

ACID by Wikipedia under CC BY-SA 3.0; from Wikipedia


Fazit der Recherche

Ziel: Informatikinterssierten einen Zugang zu dem Thema Transaktionsverarbeitung bieten

Anforderungen durch OER [HWSJ10] [DW14] Weitere Anforderungen
Erfüllung der Offenheitsprinzipien (5R’s) Mit wenig Vorwissen zu verstehen
Passende Lizenzierung Hochwertige Quellen
ALMS-Framework Eignung sowohl als Lehr- als auch Lernressourcen

Agenda

  1. Motivation
  2. Konzeption und Umsetzung
  3. Wie würde ich ein neues OER-Projekt angehen?
  4. Fazit und Diskussion

Konzeption und Umsetzung


Vorgehen bei der Konzeption und Umsetzung

  1. Anforderungen durch OER
  2. Aufbau
  3. Quellenauswahl
  4. Didaktische Elemente

Anforderungen durch OER


Medienauswahl

Alternativen

  • Video
  • Dokument
  • Website

Auswahl

  • Präsentation

    • Eignung als Lehr- und Lernressource

    Teacher

    Teacher by 3dman_eu under CC0 1.0; from Pixabay


Softwareauswahl: emacs-reveal

Verfielfältigen, Verwenden & Verbreiten

Screenshot from Introduction to transaction processing

Screenshot from Introduction to transaction processing by Christopher Olbrich under CC BY-SA 4.0; from Gitlab

Vermischen & Verarbeiten

Screenshot from Introduction to transaction processing

Screenshot from Introduction to transaction processing by Christopher Olbrich under CC BY-SA 4.0; from Gitlab


ALMS-Framework

 ALMS-Kriterium        Beispiele           Gegenbeispiele             
 Access to    
 editing tools       
                     
                      		 Freie/Libre und   
 Open Source Soft- 
 ware (z.B. LaTeX, 
 LibreOffice)       		 Powerpoint                 
 Google Docs                
                            
                            
 Level of     
 expertise required  
 to revise or remix   		                                                
               Schwieriges Thema …              
                                                
 Meaningfully 
 editable             		 LaTeX, Org Mode   
 (HTML)             		 (Gescanntes) PDF           
 Flash, Video               
 Source-file  
 access               		 LaTeX, Org Mode   
 (HTML)             		 PDF zu LaTeX               
 PDF zu Office-Präsentation 

Rechtliche und technische Anforderungen an OER: ALMS-Framework [HWSJ10] von Jens Lechtenbörger unter CC BY-SA 4.0 von GitLab


Veröffentlichung


Aufbau


Agenda

  • Introduction
  • Motivation: Database Anomalies
  • Transaction Concept
  • ACID Properties of Transactions
  • Concurrency Control
  • Conclusion

Erläuterung von DBMS

  • A DBMS gives access to the data within a database

    DBMS

    DBMS by Christopher Olbrich under CC BY-SA 4.0; from GitLab

  • The DBMS controls the access to the database
    • Creation and adminsistration of the database
    • Read and write operations
  • A DBMS uses transactions to control access to the database

Praktisches Beispiel zu Beginn

  • Banking is frequenly used to explain the need for transaction processing

    Cash-mashine

    Cash-mashine by 3dman_eu under CC0; from Pixabay

    • Data-acess happens concurrently, because multiple users need access to data (e.g. account balance)
    • A failure of the system or of a transaction should not affect the database
    • The database system should always reflect the correct account balance for each customer

Praktisches Beispiel zu Beginn

  • Example: Let’s assume that a bank only has one database system for its three customers
  • The database only has one table
    • Within this table the account balance of each customer is saved
  • Data access happens through transactions
 Id   Name    Balance 
  1 
  2 
  3  		 Alice 
 Bob   
 Eve    		 100     
 200     
 50      

Darstellung des Transaktionskonzeptes

  • A transaction symbolizes a unit of work
    • The unit of work is limited by begin and commit
    • Transactions can be aborted and are not valid anymore
  • Transactions can read data and manupulate data by writing a new value
  • Example: Add 100 to the balance of Alice
Time  Transaction T1         
  1 
  2 
  3 
  4 
  5  		 begin                  
 read(Balance of Alice) 
 Balance = Balance + 100
 write(Balance of Alice)
 commit                 

Beispiel: Lost Update

  • T1 & T2 read the same value for the balance of Alice
  • T1 writes its changes, followed by T2
  • Proplem: T2 overrides the update by T1, which is therefore a lost update
Time  Transaction T1          Transaction T2          
  1 
  2 
  3  		 begin                  
 read(Balance of Alice) 
 Balance = Balance - 10  		                        
                        
                        
  4 
  5 
  6  		                        
                        
                         		 begin                  
 read(Balance of Alice) 
 Balance = Balance + 100
  7 
  8  		 write(Balance of Alice)
 read(Balance of Bob)    		                        
                        
  9 
 10  		                        
                         		 write(Balance of Alice)
 commit                 
 11 
 12 
 13  		 Balnce = Balance + 10  
 write(Balance of Bob)  
 commit                  		                        
                        
                        

Voraussetzungen

  • Basic understanding of data management
  • No need to be familiar with SQL
    • This presentation will focus on transaction processing on an abstraction level
    • SQL is therefore not part of this introduction to transaction processing

Quellenauswahl


Ausgewählte Quellen

  • Eswaran, Gray, Lorie & Traiger, The Notions of Consistency and Predicate Locks in a Database System, Commun. ACM 19(11), 624-633 (1976). [EGLT76]
  • Haerder & Reuter, Principles of Transaction-oriented Database Recovery, ACM Comput. Surv. 15(4), 287-317 (1983) [HR83]
  • Weikum & Vossen, Transactional Information Systems: Theory, Algorithms, and the Practice of Concurrency Control and Recovery, Morgan Kaufmann Publishers Inc. [WV01]

Wissenschaftlicher Hintergrund

  • After Edgar F. Codd puplished his influential paper about the relational model for database systems in 1969 the first practical systems based on this model were implemented

    • These systems had problems with concurrent execution and crash recovery

    Jim Gray speeking (editet)

    Jim Gray speeking (editet) by Tony Hey, Stewart Tansley, Kristin Tolle (Eds.) under CC BY-SA 3.0; from Wikimedia


Zitat: Beispiel Atomarität

  • “It must be of the all-or-nothing type […] and the user must, whatever happens, know which state he or she is in.” [HR83]
  • All-or-nothing principle
    • Either all steps of a transaction should be reflected in the database or no step
    • If an error acures before the commit of a transaction, no changes should be reflected in the database
      • In this case a rollback is performed and the changes will be reset
      • Transaction recovery deals with undoing the changes a transaction made until it was aborted

Zitat: Isolation

  • “Events within a transaction must be hidden from other transactions running concurrently.” [HR83]
  • Concurrently running transactions should not influence each other
    • When transaction influence each other database anamolies happen
  • How transactions can be executed concurrently without influencing each other is discussed in the chapter concurrency control

Bild: 2-Phasen Sperrprotokol

  • The 2PL is the most famous locking protocol, because it is used in many of the commercial database systems. [WV01]

    Lock aquiring and release under 2PL

    Lock aquiring and release under 2PL by Christopher Olbrich under CC BY-SA 4.0; from GitLab

  • Idea of the 2PL: Lock aquisition and lock release are strictly seperated for a transaction
    • Growing phase: Locks are aquired
    • Shrinking phase: Locks are released

Didaktische Elemente


Lernziele

  • Give examples for dirty read and lost update anomalies
  • Explain reasons for the development of the transaction concept
  • Name and explain the ACID properites of transactions
  • Explain the idea behind serializability of transactions
  • Discuss advantages and limitations of locking schedule algorithm

Fragen zu Überprüfung

  • What is the name of the database anomalie?
    • lost-update
  • What is the resulting value for the Balance of Bob?
    • 240
  • What would be the value, if the transactions were executed one after another?
    • 210

Balance of Bob = 200

Time  Transaction T1          Transaction T2          
  1 
  2 
  3  		 begin                  
 read(Balance of Bob)   
 Balance = Balance - 30  		                        
                        
                        
  4 
  5 
  6  		                        
                        
                         		 begin                  
 read(Balance of Bob)   
 Balance = Balance + 40 
  7 
  8  		 write(Balance of Bob)  
 read(Balance of Eve)    		                        
                        
  9 
 10  		                        
 ...                     		 write(Balance of Bob)  
 ...                    

Fragen zur Überprüfung

  • Which of the following statements fits to which ACID princible?
    • Think about your answer before you check out the solutions!
  • Concurrently running transactions should not influence each other.
    • Isolation
  • Changes by a transaction, that does not reach it’s normal end, should not be reflected in the database.
    • Atomacity

Wie würde ich an ein neues OER-Projekt angehen?


Mein chronologisches Vorgehen

  1. Welche Gebiete der Transaktionsverarbeitung?
    • Sehr umfangreiche Literatur: z.B. von Prof. Vossen [WV01]
  2. Medium?
    • Emacs-reveal oder doch eine anderes Medium?
  3. Ziel- und Zielgruppendefinition
  • Anforderungen
  • Konzeption und Umsetzung

Mein Vorgehen für das nächste OER-Projekt

  • Warum?
    • Welches Ziel verfolge ich mit der Erstellung von OER?
    • Welche Zielgruppe lege ich fest?
  • Wie?
    • Was sind Anforderungen, um dieses Ziel umzusetzen?
  • Was?
    • Welches Medium? Welche Lizenz?
    • Welche Quelen? Welche Didaktischen Elemente?

Fazit und Diskussion


Fazit

  • Zieldefinition zu Beginn wichtig
    • Einführung in die Transaktionsverarbeitung
    • Wissen zugänglich machen
  • Neben OER bezogenen Anforderungen auch individuelle Anforderungen
    • Mit wenig Vorwissen zu verstehen, hochwertige Quellen & Lehr- und Lernressource
  • Umsetzung durch emacs-reveal
    • Datenbank Anomalien als Einführung
    • Lernziele und Fragen zur Üperprüfung
    • In Zukunft erweiterbar

Diskussion

Uncovering questions

Uncovering questions under CC0; converted, resized, background changed from Pixabay


Bibliography

  • [HWSJ10] Hilton, Wiley, Stein & Johnson, The four R's of openness and ALMS analysis: frameworks for open educational resources, Open Learning 25(1), 37-44 (2010).
  • [DW14] David Wiley, The Access Compromise and the 5th R, https://opencontent.org/blog/archives/3221
  • [EGLT76] Eswaran, Gray, Lorie & Traiger, The Notions of Consistency and Predicate Locks in a Database System, Commun. ACM 19(11), 624-633 (1976). http://doi.acm.org/10.1145/360363.360369
  • [HR83] Haerder & Reuter, Principles of Transaction-oriented Database Recovery, ACM Comput. Surv. 15(4), 287-317 (1983). http://doi.acm.org/10.1145/289.291
  • [WV01] Weikum & Vossen, Transactional Information Systems: Theory, Algorithms, and the Practice of Concurrency Control and Recovery, Morgan Kaufmann Publishers Inc., 2001.


License Information

Except where otherwise noted, this work, “Erstellung einer OER über das Thema Transaktions-verarbeitung”, is © 2018 by Christopher Olbrich, published under the Creative Commons license CC BY-SA 4.0.

No warranties are given. The license may not give you all of the permissions necessary for your intended use.

In particular, trademark rights are not licensed under this license. Thus, rights concerning third party logos (e.g., on the title slide) and other (trade-) marks (e.g., “Creative Commons” itself) remain with their respective holders.