CS 839: Design the Next-Generation Database Lecture 2: Transaction - - PowerPoint PPT Presentation

▶
cs 839 design the next generation database lecture 2
SMART_READER_LITE
LIVE PREVIEW

CS 839: Design the Next-Generation Database Lecture 2: Transaction - - PowerPoint PPT Presentation

May 04, 2023 274 likes •764 views

CS 839: Design the Next-Generation Database Lecture 2: Transaction Basics Xiangyao Yu 1/23/2020 1 Announcements Course website http://pages.cs.wisc.edu/~yxy/cs839-s20/index.html Email me if you are not in HotCRP

slide-1
SLIDE 1

Xiangyao Yu 1/23/2020

CS 839: Design the Next-Generation Database Lecture 2: Transaction Basics

1

slide-2
SLIDE 2

Announcements

Course website http://pages.cs.wisc.edu/~yxy/cs839-s20/index.html Email me if you are not in HotCRP https://wisc-cs839-ngdb20.hotcrp.com

2

slide-3
SLIDE 3

Today’s Agenda

OLTP vs. OLAP ACID properties

  • Atomicity
  • Consistency
  • Isolation
  • Durability

3

slide-4
SLIDE 4

OLTP vs. OLAP

OLTP: On-Line Transaction Processing

  • Users submit transactions that contain simple read/write operations
  • Example: banking, online shopping, etc.

OLAP: On-Line Analytical Processing

  • Complex analytics queries that reveal insights behind data
  • Example: business report, marketing, forecasting, etc.

4

slide-5
SLIDE 5

OLTP vs. OLAP

OLTP database Transactions

(Update Intensive)

slide-6
SLIDE 6

OLTP vs. OLAP

OLTP database Transactions OLAP database

(Update Intensive) (Read Intensive, rare updates)

6

slide-7
SLIDE 7

OLTP vs. OLAP

OLTP database Transactions OLAP database

(Update Intensive) (Read Intensive, rare updates)

  • Takes hours for

conventional databases

  • Takes seconds for Hybrid

transactional/analytical processing (HTAP) systems

7

slide-8
SLIDE 8

OLTP vs. OLAP

OLTP database OLAP database

(Update Intensive) (Read Intensive, rare updates)

This lecture Next lecture

8

slide-9
SLIDE 9

Transaction Definition

9

slide-10
SLIDE 10

Transaction Definition

What are the required properties of a database transaction?

ACID

10

slide-11
SLIDE 11

Transaction Definition

What are the required properties of a database transaction?

ACID

Example transactions:

If checking.balance > 100 checking.balance -= 100 saving.balance += 100 Begin Read(X) Write(Y) Insert(Z) Commit

11

slide-12
SLIDE 12

ACID: Atomicity

If checking.balance > 100 checking.balance -= 100 saving.balance += 100

Atomicity: Either all operations occur, or nothing occurs (All or nothing)

12

slide-13
SLIDE 13

ACID: Consistency

If checking.balance > 100 checking.balance -= 100 saving.balance += 100

Consistency: Integrity constraints must be maintained. Example Integrity constraint: balance of checking account must be above $0

Checking Saving $50 $1000

  • $50

$1100 RunTxn()

13

slide-14
SLIDE 14

ACID: Durability

Example transaction:

If checking.balance > 100 checking.balance -= 100 saving.balance += 100 Checking Saving $1000 $1000

14

slide-15
SLIDE 15

ACID: Durability

Example transaction:

If checking.balance > 100 checking.balance -= 100 saving.balance += 100 Checking Saving $1000 $1000 $900 $1100 RunTxn()

15

slide-16
SLIDE 16

ACID: Durability

Example transaction:

If checking.balance > 100 checking.balance -= 100 saving.balance += 100 Checking Saving $1000 $1000 $900 $1100 RunTxn()

CRASH !!

??? ???

16

slide-17
SLIDE 17

ACID: Durability

Example transaction:

If checking.balance > 100 checking.balance -= 100 saving.balance += 100

Durability: A transaction’s updates persist in case of system failure

Checking Saving $1000 $1000 $900 $1100 RunTxn()

CRASH !!

??? ???

17

slide-18
SLIDE 18

ACID: Isolation

Initailly checking.balance = 1000

18

slide-19
SLIDE 19

ACID: Isolation

If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal Initailly checking.balance = 1000

19

slide-20
SLIDE 20

ACID: Isolation

If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal

1

Initailly checking.balance = 1000 bal = 1000

20

slide-21
SLIDE 21

ACID: Isolation

If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal

1 2

Initailly checking.balance = 1000 bal = 1000 bal = 1000

21

slide-22
SLIDE 22

ACID: Isolation

If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal

1 2

Initailly checking.balance = 1000 bal = 1000 bal = 1000 bal = 900 bal = 900

22

slide-23
SLIDE 23

ACID: Isolation

If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal

1 2 3

Initailly checking.balance = 1000 bal = 1000 bal = 1000 bal = 900 bal = 900 checking = 900

23

slide-24
SLIDE 24

ACID: Isolation

If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal If checking.balance > 100 bal = checking.balance bal = bal – 100 checking.balance = bal

1 2 3 4

Initailly checking.balance = 1000 bal = 1000 bal = 1000 bal = 900 bal = 900 checking = 900 checking = 900

24

slide-25
SLIDE 25

ACID: Isolation

Strong isolation: Serializability (Focus of this course) Weak isolation: Snapshot Isolation, Read Committed, Read Uncommitted, etc. Weaker isolation levels allow more interleaving of transactions Schedule of concurrent transactions is equivalent to some serial schedule

25

slide-26
SLIDE 26

ACID: Isolation – Why Strong Isolation?

Attackers stole 896 Bitcoins ≈ 3 million US dollars

April 2014

MongoDB & Bitcoin: How NoSQL design flaws brought down two exchanges Why you should pick strong consistency, whenever possible Systems that don't provide strong consistency … create a burden for application developers

January 2018

SQL (before 2000) -> NoSQL (since 2000) -> NewSQL (since 2010s) 26

slide-27
SLIDE 27

How to Enforce ACID

Atomic & Isolation: Concurrency control Consistency: Check integrity for transactions Durability: Logging

27

slide-28
SLIDE 28

Concurrency Control

Pessimistic Optimistic

28

slide-29
SLIDE 29

Pessimistic – Two Phase Locking (2PL)

T1 Begin Read(X) Write(Y) Commit

29

Time

slide-30
SLIDE 30

Pessimistic – Two Phase Locking (2PL)

T1 Begin Read(X) Write(Y) Commit

30

Time

slide-31
SLIDE 31

Pessimistic – Two Phase Locking (2PL)

T1 Begin Read(X) Write(Y) Commit

31

Time

slide-32
SLIDE 32

Pessimistic – Two Phase Locking (2PL)

T1 Begin Read(X) Write(Y) Commit Begin Write(X) T2

32

Time

slide-33
SLIDE 33

Pessimistic – Two Phase Locking (2PL)

T1 Begin Read(X) Write(Y) Commit Begin Write(X) T2

33

Time

slide-34
SLIDE 34

Pessimistic – Two Phase Locking (2PL)

T1 Begin Read(X) Write(Y) Commit Begin Write(X) Commit T2

34

Time

slide-35
SLIDE 35

Pessimistic – Deadlock in 2PL

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

35

Time

slide-36
SLIDE 36

Pessimistic – Deadlock in 2PL

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

36

Time

slide-37
SLIDE 37

Pessimistic – Deadlock in 2PL

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

37

Time

slide-38
SLIDE 38

Pessimistic – Deadlock in 2PL

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

38

Time

slide-39
SLIDE 39

Pessimistic – Deadlock in 2PL

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

39

Time

slide-40
SLIDE 40

Pessimistic – Deadlock in 2PL

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

Deadlock !

40

Time

slide-41
SLIDE 41

Pessimistic – Deadlock Resolution

T1 Begin Read(X) Write(Y) Begin Write(Y) Write(X) T2

Deadlock !

Detect and break cycles Allow only certain waits

  • NoWait
  • WaitDie
  • WoundWait
  • LimitedDepth
  • etc.

41

Wait-for Wait-for

slide-42
SLIDE 42

Optimistic Concurrency Control (OCC)

T1 Begin Read(X) Write(Y) Validate(X) Abort Begin Write(X) Commit T2

42

Time

slide-43
SLIDE 43

How to Enforce ACID

Atomic & Isolation: Concurrency control Consistency: Check integrity for transactions Durability: Logging

43

slide-44
SLIDE 44

Durability: Logging

Begin Initially checking = 1000 Write

(Checking = 900)

Commit

CRASH

Recovery Read

(Checking = 900)

44

slide-45
SLIDE 45

Durability: Logging

Begin Initially checking = 1000 Write

(Checking = 900)

Commit

CRASH

Recovery Read

(Checking = 900)

Logging T1 T2 T3 T4 T5 Log (on disk)

…

45

slide-46
SLIDE 46

Summary

A transaction in an OLTP system has ACID properties Atomicity, Consistency, Isolation, Durability Concurrency control (enforces Atomicity and Isolation) Two Phase Locking (2PL) Optimistic Concurrency Control (OCC) Logging (enforces Durability)

46

slide-47
SLIDE 47

Group Discussion

Discuss the relative advantages and disadvantages of 2PL and OCC. What is your opinion on the debate between SQL and NoSQL (strong guarantee vs. high performance)? Do you see any scalability problem with logging? If so, any potential solution?

47

slide-48
SLIDE 48

Before Next Lecture

Submit discussion summary to https://wisc-cs839-ngdb20.hotcrp.com

  • One summary per group
  • Authors: group members
  • Any format is ok (e.g., pdf, doc, txt)
  • Feel free to comment on others’ discussion

Submit review for [optional] C-Store: A Column-oriented DBMS

48