CSE 599c Hot Topics in Data Management Systems

Lecture 13: Traditional Distributed Databases

Magdalena Balazinska

Winter 2006

Today's Paper

C. Mohan, B. Lindsay, and R. Obermarck. Transaction Management in the R* Distributed Database Management System. ACM Transactions On Database Systems 11(4), 1986.

Content of today's lecture comes also from:

M. Tamer Ozsu and P. Valduriez. Principles of Distributed Database Systems. 2nd Ed. Prentice Hall.

Distributed Database Components

Data fragmentation and distribution

Distributed query optimization and execution

Distributed transactions (today)

Isolation: distributed concurrency control

Atomicity & Durability: two-phase commit

Replication (will discuss later)!

Distributed Concurrency Control

In theory, different techniques are possible

Pessimistic, optimistic, locking, timestamps

In practice, distributed two-phase locking

Simultaneously hold locks at all sites involved

Deadlock detection techniques

Global wait-for graph

Timeouts

If deadlock: abort least costly local transaction

Two-Phase Commit (2PC): Motivation

Coordinator

Subordinate 1

Subordinate 2

Subordinate 3

1) User decides

to commit

2) COMMIT

3) COMMIT

4) Coordinator

crashes

What do we do now?

But I already aborted!

Two-Phase Commit Protocol

One coordinator and many subordinates

Phase 1: prepare

Phase 2: commit or abort

Principle

subordinate first force-writes a log record

only then subordinate responds to message

2PC: Phase 1, Prepare

Coordinator

Subordinate 1

Subordinate 2

Subordinate 3

1) User decides

to commit

2) PREPARE

2) PREPARE

2) PREPARE

3) Force-write: prepare

3) Force-write: prepare

3) Force-write: prepare

4) YES

4) YES

4) YES

2PC: Phase 2, Commit

Coordinator

Subordinate 1

Subordinate 2

Subordinate 3

1) Force-write:

commit

2) COMMIT

2) COMMIT

2) COMMIT

3) Force-write: commit

3) Force-write: commit

3) Force-write: commit

4) ACK

4) ACK

4) ACK

Transaction is

now committed!

5) Commit transaction

and “forget” it

5) Commit transaction

and “forget” it

5) Commit transaction and “forget” it

5) Write: end, then forget transaction

2PC with Abort

Coordinator

Subordinate 1

Subordinate 2

Subordinate 3

1) User decides

to commit

2) PREPARE

2) PREPARE

2) PREPARE

3) Force-write: prepare

3) Force-write: abort

3) Force-write: abort

4) YES

4) No

4) NO

5) Abort transaction

and “forget” it

5) Abort transaction and “forget” it

2PC with Abort

Coordinator

Subordinate 1

Subordinate 2

Subordinate 3

1) Force-write:

abort

2) ABORT

3) Force-write: abort

4) ACK

5) Write: end, then forget transaction

5) Abort transaction

and “forget” it

Coordinator State Machine

All states involve waiting for messages

COMMITTING

ABORTING

INIT

Receive: Commit

Send: Prepare

R: No votes

FW: Abort

S: Abort

R: Yes votes

FW: Commit

S: Commit

END

COLLECTING

R: ACKS

FW: End

Forget

R: ACKS

FW: End

Forget

Subordinate State Machine

INIT and PREPARED involve waiting

PREPARED

COMMITTING

ABORTING

INIT

R: Prepare

FW: Prepare

S: Yes vote

R: Prepare

FW: Abort

S: No vote

Abort and

forget

R: Abort

FW: Abort

S: Ack

R: Commit

FW: Commit

S: Ack

Commit and

forget

Failure Handling Principles

If doesn't know anything about transaction respond “abort”

If there are no log records for a transaction after a crash then abort transaction and “forget” it

Retry mechanism

In prepared state, periodically query coordinator

In committing/aborting state, periodically resend messages to subordinates

Approach 1: Magical failure detection

Failure Handling

Approach 2: no failure detection

More retrying & blocking

Approach 3: timeouts

Since unilateral abort is ok,

Subordinate can timeout in prepared state

Coordinator can timeout in collecting state

Prepared state is still blocking

2PC is a blocking protocol

Presumed Abort Protocol

Optimization goals

Fewer messages and fewer force-writes

Principle: if nothing known about a transaction, assume ABORT

Aborting transactions need no force-writing

Avoid log records for read-only transactions

Reply with a READ vote instead of YES vote

Optimizes read-only transactions

2PC State Machines (repeat)

COMMITTING

ABORTING

INIT

Receive: Commit

Send: Prepare

R: No votes

FW: Abort

S: Abort

R: Yes votes

FW: Commit

S: Commit

END

COLLECTING

R: ACKS

FW: End

R: ACKS

FW: End

PREPARED

COMMITTING

ABORTING

INIT

R: Prepare

FW: Prepare

S: Yes vote

R: Prepare

FW: Abort

S: No vote

Abort and

forget

R: Abort

FW: Abort

S: Ack

R: Commit

FW: Commit

S: Ack

Commit and

forget

Presumed Abort State Machines

COMMITTING

INIT

Receive: Commit

Send: Prepare

R: No votes

W: Abort

S: Abort

R: Yes votes

FW: Commit

S: Commit

END

COLLECTING

R: ACKS

FW: End

PREPARED

COMMITTING

ABORTING

INIT

R: Prepare

FW: Prepare

S: Yes vote

R: Prepare

W: Abort

S: No vote

Abort and

forget

R: Abort

W: Abort

R: Commit

FW: Commit

S: Ack

Commit and

forget

Presumed Abort for Read-Only

INIT

Receive: Commit

Send: Prepare

END

COLLECTING

R: Read

Forget

DONE

INIT

R: Prepare

S: Read vote

Forget

Presumed Commit Protocol

Want to optimize for commit instead

Principle: if nothing known about a transaction, assume COMMIT

Not a direct dual of PA!

Before prepare phase, force-write collecting

Force-write all messages related to abort

Need not acknowledge COMMIT message

Presumed Commit State Machines

ABORTING

INIT

R: No votes

FW: Abort

S: Abort

R: Yes votes

FW: Commit

S: Commit

END

COLLECTING

R: ACKS

FW: End

PREPARED

COMMITTING

ABORTING

INIT

R: Prepare

FW: Prepare

S: Yes vote

R: Prepare

FW: Abort

S: No vote

Abort and

forget

R: Abort

FW: Abort

S: Ack

R: Commit

W: Commit

Commit and

forget

Receive: Commit

FW: Collecting

Send: Prepare

Presumed Commit for Read-Only

INIT

Receive: Commit

FW: Collecting

Send: Prepare

END

COLLECTING

R: Read

W: Commit

Forget

DONE

INIT

R: Prepare

S: Read vote

Forget

Discussion

Modern DBMSs support distribution

Some limitations of traditional approach

2PC is expensive

Assumes site have no autonomy

Assumes site uniformity

Limited scalability

Tight coupling between sites

2006-01-01T23:16:26