Transactions and Wrap-Up - PowerPoint PPT Presentation

Loading...

PPT – Transactions and Wrap-Up PowerPoint presentation | free to download - id: 88361-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Transactions and Wrap-Up

Description:

Final examination will be available on Tuesday. 3. What is a Transaction? ... Suppose we have a table of bank accounts which contains the balance of the account ... – PowerPoint PPT presentation

Number of Views:31
Avg rating:3.0/5.0
Slides: 25
Provided by: zack4
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Transactions and Wrap-Up


1
Transactions and Wrap-Up
  • Zachary G. Ives
  • University of Pennsylvania
  • CIS 550 Database Information Systems
  • December 8, 2005

Some slide content derived from Ramakrishnan
Gehrke
2
Reminders
  • Please be sure youre signed up for a project
    demo
  • Due at that time 5-10 page report describing
  • What your project goals were
  • What you implemented
  • Basic architecture and design
  • Division of labor
  • And the code!
  • Also please email me an assessment of how well
    your group worked group members contributions
    your contributions
  • Final examination will be available on Tuesday

3
What is a Transaction?
  • A transaction is a sequence of read and write
    operations on data items that logically functions
    as one unit of work
  • should either be done entirely or not at all
  • if it succeeds, the effects of write operations
    persist (commit) if it fails, no effects of
    write operations persist (abort)
  • these guarantees are made despite concurrent
    activity in the system, and despite failures that
    may occur

4
How Things Can Go Awry
  • Suppose we have a table of bank accounts which
    contains the balance of the account
  • An ATM deposit of 50 to account 1234 would be
    written as
  • This reads and writes the accounts balance
  • What if two accountholders make deposits
    simultaneously from two ATMs?

update Accounts set balance balance 50 where
account 1234
5
Concurrent Deposits
  • This SQL update code is represented as a
    sequence of read and write operations on data
    items (which for now should be thought of as
    individual accounts)
  • where X is the data item representing the
    account with account 1234.

Deposit 1 Deposit
2 read(X.bal)
read(X.bal) X.bal X.bal 50 X.bal
X.bal 10 write(X.bal)
write(X.bal)
6
A Bad Concurrent Execution
  • Only one action (e.g. a read or a write) can
    actually happen at a time, and we can interleave
    deposit operations in many ways

Deposit 1 Deposit
2 read(X.bal)
read(X.bal) X.bal
X.bal 50
X.bal X.bal 10 write(X.bal)

write(X.bal)
7
A Good Execution
  • Previous execution would have been fine if the
    accounts were different (i.e. one were X and one
    were Y), i.e., transactions were independent
  • The following execution is a serial execution,
    and executes one transaction after the other

Deposit 1 Deposit
2 read(X.bal) X.bal
X.bal 50 write(X.bal)
read(X.bal)
X.bal X.bal 10

write(X.bal)
8
Good Executions
  • An execution is good if it is serial
    (transactions are executed atomically and
    consecutively) or serializable (i.e. equivalent
    to some serial execution)
  • Equivalent to executing Deposit 1 then 3, or vice
    versa
  • Why would we want to do this instead?

Deposit 1 Deposit
3 read(X.bal)
read(Y.bal) X.bal
X.bal 50
Y.bal Y.bal 10 write(X.bal)

write(Y.bal)
9
Atomicity
  • Problems can also occur if a crash occurs in the
    middle of executing a transaction
  • Need to guarantee that the write to X does not
    persist (ABORT)
  • Default assumption if a transaction doesnt commit

10
Transactions in SQL
  • A transaction begins when any SQL statement that
    queries the db begins.
  • To end a transaction, the user issues a COMMIT or
    ROLLBACK statement.

Transfer UPDATE Accounts SET balance
balance - 100 WHERE account 1234 UPDATE
Accounts SET balance balance 100 WHERE
account 5678 COMMIT
11
Read-Only vs. Read-Write Transactions
  • We can tell the DBMS that we wont be performing
    any updates
  • If we are going to modify the DBMS, we need

SET TRANSACTION READ ONLY SELECT FROM
Accounts WHERE account1234
SET TRANSACTION READ WRITE UPDATE Accounts SET
balance balance - 100 WHERE account 1234
...
12
Dirty Reads
  • Dirty data is data written by an uncommitted
    transaction a dirty read is a read of dirty data
    (WR conflict)
  • Sometimes we can tolerate dirty reads other
    times we cannot
  • e.g., if we wished to ensure balances never went
    negative in the transfer example, we should test
    that there is enough money first!

13
Bad Dirty Read
EXEC SQL select balance into bal
from Accounts where
account1234 if (bal gt 100) EXEC SQL
update Accounts set balance
balance - 100 where account
1234 EXEC SQL update Accounts
set balance balance 100
where account 5678 EXEC SQL COMMIT
If the initial read (italics) were dirty, the
balance could become negative!
14
Acceptable Dirty Read
  • If we are just checking availability of an
    airline seat, a dirty read might be fine! (Why is
    that?)
  • Reservation transaction

EXEC SQL select occupied into occ
from Flights where Num
123 and date11-03-99
and seat23f if (!occ) EXEC SQL
update Flights set
occupiedtrue where Num 123 and
date11-03-99 and
seat23f else notify user that seat is
unavailable
15
Other Undesirable Phenomena
  • Unrepeatable read a transaction reads the same
    data item twice and gets different values (RW
    conflict)
  • Why? Someone changed the tuple
  • Phantom problem a transaction retrieves a
    collection of tuples twice and sees different
    results
  • Why? Someone added or removed a tuple

16
Isolation
  • The problems weve seen are all related to
    isolation
  • General rules of thumb w.r.t. isolation
  • Fully serializable isolation is expensive
  • We cant do as many things concurrently (or we
    have to undo them frequently)
  • For performance, the DBMS lets you relax the
    isolation level if your application can tolerate
    it, e.g
  • SET TRANSACTION READ WRITE
  • ISOLATION LEVEL READ UNCOMMITTED

17
Implementing Isolation Levels
  • One approach use locking at some level
  • each data item is either locked (in some mode,
    e.g. shared or exclusive) or is available (no
    lock)
  • an action on a data item can be executed if the
    transaction holds an appropriate lock
  • consider granularity of locks how big of an
    item to lock
  • Larger granularity fewer locking operations but
    more contention!
  • tuple, page, table, etc.
  • Appropriate locks
  • Before a read, a shared lock must be acquired
  • Before a write, an exclusive lock must be acquired

18
Locks Prevent Bad Execution
  • If the system used locking, the first bad
    execution could have been avoided

Deposit 1 Deposit
2 xlock(X) read(X.bal)
xlock(X)
is not granted X.bal X.bal 50
write(X.bal) release(X)
xlock(X)
read(X.bal)
X.bal X.bal 10

write(X.bal)
release(X)
19
Locking and Serializability
  • A transaction must hold all locks until it
    terminates (a condition called strict locking)
  • It turns out that this is crucial to guarantee
    serializability
  • Note that the first (bad) example could have been
    produced if transactions acquired and immediately
    released locks.

20
Well-Formed, Two-Phased Transactions
  • A transaction is well-formed if it acquires at
    least a shared lock on Q before reading Q or an
    exclusive lock on Q before writing Q and doesnt
    release the lock until the action is performed
  • Locks are also released by the end of the
    transaction
  • A transaction is two-phased if it never acquires
    a lock after unlocking one
  • i.e., there are two phases a growing phase in
    which the transaction acquires locks, and a
    shrinking phase in which locks are released

21
Two-Phased Locking Theorem
  • If all transactions are well-formed and
    two-phase, then any schedule in which conflicting
    locks are never granted ensures serializability
  • i.e., there is a very simple scheduler!
  • However, if some transaction is not well-formed
    or two-phase, then there is some schedule in
    which conflicting locks are never granted but
    which fails to be serializable
  • i.e., one bad apple spoils the bunch

22
Summary
  • Transactions are all-or-nothing units of work
    guaranteed despite concurrency or failures in the
    system
  • Theoretically, the correct execution of
    transactions is serializable (i.e. equivalent to
    some serial execution)
  • Practically, this may adversely affect throughput
    ? isolation levels
  • With isolation levels, users can specify the
    level of incorrectness they are willing to
    tolerate

23
What to Look for Down the Road
  • Well, no one really knows the answer to this
  • But here are some current directions
  • Sensors and streaming data
  • Peer-to-peer meets databases and data integration
  • The Semantic Web
  • Security and privacy especially as integration
    becomes more commonplace
  • Uncertainty and ranked retrieval

24
A Plug for Next Semester
  • CSE 455/CIS 555 Internet and Web Systems
  • Focus building and interconnecting scalable Web
    servers and services information retrieval
    integration
  • Heavy emphasis on implementation, experimentation
    need substantial coding experience
  • Meanwhile Best of luck on your projects and
    exams and have a wonderful break!
  • I hope you learned a lot in this course and that
    it at least for stretches was enjoyable!
About PowerShow.com