Transactions Flashcards
A critera for DMBS?
• DBMS must allow concurrent access to
databases.
– Imagine a bank where account information is
stored in a database not allowing concurrent
access. Then only one person could do a
withdrawal in an ATM machine at the time –
anywhere!
• Uncontrolled concurrent access may lead
to problems.
Explain running in Parallell
Explain Interleaving.
Explain Serializability.
Explain Atomicity.
Which are ACID transactions?
Explain Transactions in SQL.
How do we controll transactions?
Explain Read-only vs Read-write
Explain SET TRANSACTIONS.
Explain drawbacks with serializability and atomicity.
Drawbacks
• Serializability and atomicity are necessary,
but don’t come without a cost.
– We must retain old data until the transaction
commits.
– Other transactions may need to wait for one
to complete.
• In some cases some interference may be
acceptable, and could speed up the
system greatly.
What is the worst that could happen?
Explain Isolation Levels.
Isolation levels
• ANSI SQL standard defines four isolation
levels, which are choices about what kinds
of interference are allowed between
transactions.
• Each transaction chooses its own isolation
level, deciding how other transactions may
interfere with it.
• Isolation level is defined in terms of three
phenomena that can occur.
Which are intereference types.
Kinds of interference
The ANSI SQL standard describes:
• Dirty read
• Non-repeatable read
• Phantom
What is Dirty Read?
Dirty read
• Transaction T1 modifies a data item.
• Another transaction T2 then reads that
data item before T1 performs a COMMIT
or ROLLBACK.
• If T1 then performs a ROLLBACK, T2 has
read a data item that was never committed
and so never really existed.
Explain Non-Repeatable Read.
Non-repeatable read
• Transaction T1 reads a data item.
• Another transaction T2 then modifies or
deletes that data item and commits.
• If T1 then attempts to re-read the data
item, it receives a modified value or
discovers that the data item has been
deleted.
Explain Phantom.
Which are the isolation levels?
Choosing isolation level
• Within a transaction we can choose the
isolation level:
where X is one of
SET TRANSACTION ISOLATION LEVEL X;
• SERIALIZABLE
• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
What are the differences between isolation levels?
Explain READ UNCOMMITTED.
READ UNCOMMITTED
• If a transaction is run with isolation level
READ UNCOMMITTED, then the transaction
allows other transactions to modify the
database while running.
• Anything that is changed by another
transaction affects the reads of this
transaction, even if the other transaction
has not yet committed!
Explain READ COMMITTED.
READ COMMITTED
• If a transaction is run with isolation level
READ COMMITTED, then the transaction
allows other transactions to modify the
database while running.
• Anything that is committed by another
transaction affects the reads of this
transaction.
Explain REPEATABLE READ.
REPEATABLE READ
• If a transaction is run with isolation level
REPEATABLE READ, it works like read
committed, except:
• If the transaction reads more than once,
we are guaranteed to get at least the
same tuples again (though we could get
more).
Explain Seriaizable.
Summarize Transactions.
Summary transactions
• DBMS must ensure that different
processes don’t interfere with each other!
– ”ACID”: Atomicity, Consistency, Isolation,
Durability.
– The isolation levels of transactions may vary.
• Serializable
• Read Committed
• Read Uncommitted
• Repeatable Read
– Isolation level affects only that transaction!
