Transcript chapter19

Chapterb19
Transaction Management
Transaction:
An action, or series of actions, carried out by a single user
or application program, which reads or updates the
contents of the database.
Also a logical unit of work on the database.
Single statement, part or all of a program
Transaction Management
Consistent/inconsistent state of DB
Consistency implies all integrity constraints are satisfied.
If a transaction does not complete properly, update all
affected components, referential integrity may be lost placing
the database in an inconsistent state.
Transaction Management
Outcomes of a transaction:
committed - successful completion with DB in a new consistent
state.
Aborted
- transaction did not execute successfully
Transaction Management
Aborted transaction
DB must be returned to the consistent state it was in before the
transaction started.
Transaction must be rolled back.
DBMS systems usually provide:
BEGIN TRANSACTION, COMMIT, ROLLBACK
Transaction Management
Transaction Properties:
ACID
Atomicity - all or nothing. A transaction is indivisible performed in its entirety or not performed at all.
Consistency - a transaction must move the DB from one
consistent state to another consistent state.
Isolation - Transactions execute independently of one another.
Transactions do not interfere with one another.
Durability - completed transactions are permanently recorded
ACID example
• Transfer $50 from account A to account B
1. Read A
2. A = A – 50
3. Write A
4. Read B
5. B = B + 50
6. Write B
ACID example
Consistency: the sum of A and B is unchanged by the transaction
Atomicity : if the system fails after step 3 and before step 6, the
system should ensure that its updates are not reflected
in the database, else inconsistency will result.
Duribility : once the user has been notified that the transaction has
been completed, the updates to the database by the
transaction must persist despite failures.
Isolation : if between steps 3 and 6, another transaction is allowed
to access the partially updated database, it will see an
inconsistent database.
ACID properties can be ensured by running the transactions serially.
However, loose the benefits of executing multiple transactions
Concurrently.
Transaction Management
Concurrency Control
managing simultaneously operations on the database
without having them interfere with one another.
Interleaved transactions from multiple users must satisfy the
ACID properties producing results as if they were executed
serially.
Transaction Management
Transaction Schedule
a sequence of operations by a set of concurrent transactions
that preserves the order of the operations in each of the
individual transactions.
Serial schedule
a schedule where the operations of each transaction are
executed consecutively without any interleaved operations
from other transactions.
Nonserial schedule
a schedule where the operations from a set of concurrent
transactions are interleaved.
Example Schedule
T1 transfers $50 from A to B, and T2 transfers 10% of the balance
From A to B.
Schedule 1:
T1
T2
read A
A = A – 50
write A
read B
B = B + 50
write B
read A
tmp = A*0.1
A = A – tmp
write A
read B
B = B + tmp
write B
Concurrent Trancactions
T1
T2
read A
A = A –50
Write A
Read A
tmp = A*0.1
A = A – tmp
write A
Read B
B = B + 50
Write B
read B
B = B + tmp
write B
Transaction Management
Serializable schedule
a nonserial schedule of concurrent transactions that produces
the same results as some serial execution.
Nonserial Schedule
• Schedule where operations from set of
concurrent transactions are interleaved.
• Objective of serializability is to find
nonserial schedules that allow transactions
to execute concurrently without interfering
with one another.
• In other words, want to find nonserial
schedules that are equivalent to some serial
schedule. Such a schedule is called
serializable.
Transaction Management
Locking
a procedure used to control concurrent access to data.
Used to deny access to a database component being used by
another transaction.
Shared lock:
transaction is permitted reads but not updates
Exclusive lock:
transaction can both read and update the item
Transaction Management
To guarantee serializability a protocol known as two-phase
locking is often used. (2PL)
all locking operations precede the first unlock operation
growing phase - acquiring all needed locks but not releasing
any locks.
Shrinking phase - releases locks but not allowed to to acquire
new locks.
Database recovery
• The process of restoring the database to a
correct state in the event of a failure
•
•
•
•
Failure causes:
System crashes
media failures
Application errors user errors
Sabotage
natural disasters
Database recovery
• Transactions represent the basic unit of
recovery in a database system
• Recovery manager must ensure the ACID
properties are maintained.
Database recovery
• Transaction processing involves locating a
record on disk, transferring it to a main
memory buffer, updating the buffer data
and writing the buffer contents back to disk.
• Once the buffer is flushed the changes can
be considered permanent. (commit)
Database recovery
• Failure at various stages of a transaction
may require:
• a redo – a commit has occurred but the
failure prevented the disk from being
updated properly (durability)
• an undo (rollback) – failure before a
commit and transaction effects to date must
be reversed.
Recovery Facilities
• DBMS should provide following facilities
to assist with recovery:
– Backup mechanism, which makes periodic
backup copies of database.
– Logging facilities, which keep track of current
state of transactions and database changes.
– Checkpoint facility, which enables updates to
database in progress to be made permanent.
– Recovery manager, which allows DBMS to restore
database to consistent state following a failure.
Log File
• Contains information about all updates to
database:
– Transaction records.
– Checkpoint records.
• Often used for other purposes
example, auditing).
(for
Log File
• Transaction records contain:
– Transaction identifier.
– Type of log record, (transaction start, insert,
update, delete, abort, commit).
– Identifier of data item affected by database
action (insert, delete, and update operations).
– Before-image of data item.
– After-image of data item.
– Log management information.
Checkpointing
Checkpoint
Point of synchronization between database
and log file. All buffers are force-written to
secondary storage.
• Checkpoint record is created containing
identifiers of all active transactions.
• When failure occurs, redo all transactions
that committed since the checkpoint and
undo all transactions active at time of
crash.
ORACLE Memory Components
Buffer Cache
Buffers the size of DB blocks that store data needed by SQL statements
Can hold several rows of a table.
Data changes to the rows are kept here and can be written later.
Redo Log buffer
Store in memory the redo entry information generated by DML
statements until the changes are written to disk.
A redo entry is a small amount of info produced and saved by Oracle to
reconstruct, or redo, changes made to the database by insert, update,
delete, create, alter and drop statements.
If a failure occurs the DBA can use redo info to recover the DB to the
point of the DB failure.
ORACLE Basics
• Moving data changes from memory to disk
• Two background process are used
• DBW0 and LGWR
LGWR – log writer process
writes redo log entries from the redo log buffer to online redo log files
when
a transaction commits
the redo log buffer is 1/3 full
more than 1MB of changes in the redo log buffer
before DBW0 writes dirty blocks in the DB buffer to DB files
tells DBWR to write dirty buffers to disk at checkpoints
ORACLE Basics
•
DBW0 database writer process
• Writes dirty data blocks from buffer cache to disk
– When
• The server process needs to make room in the buffer cache to read
more data in
• Told to write data to disk by the LGWR process
• Every 3 seconds due to a timeout
• The number of dirty buffers reaches a threshold
CKPT (Checkpoint) – causes DBWR to write all the dirty blocks since last
checkpoint to the data files and other info to record
the
checkpoint
ORACLE Basics
• The LGWR writes the online redo log files in a cyclical fashion.
• Wraps around to first when all are filled.
ARCH – a process which, when archiving is on, makes a copy of each
redo log file before overwriting it.
ORACLE Basics
Database Recovery
requires a backup of the database
and, for recovery to the point of the last committed transaction,
the archived redo log files since the last backup.
Deadlock
An impasse that may result when two (or
more) transactions are each waiting for
locks held by the other to be released.
Deadlock
• Only one way to break deadlock: abort
one or more of the transactions.
• Deadlock should be transparent to user, so
DBMS should restart transaction(s).
• Three general techniques for handling
deadlock:
– Timeouts.
– Deadlock prevention.
– Deadlock detection and recovery.
Timeouts
• Transaction that requests lock will only
wait for a system-defined period of time.
• If lock has not been granted within this
period, lock request times out.
• In this case, DBMS assumes transaction
may be deadlocked, even though it may not
be, and it aborts and automatically restarts
the transaction.
Deadlock Prevention
• DBMS looks ahead to see if transaction
would cause deadlock and never allows
deadlock to occur.
• Could order transactions using transaction
timestamps:
– Wait-Die - only an older transaction can wait
for younger one, otherwise transaction is
aborted (dies) and restarted with same
timestamp.
Deadlock Prevention
– Wound-Wait - only a younger transaction can
wait for an older one. If older transaction
requests lock held by younger one, younger one
is aborted (wounded).
Deadlock Detection and Recovery
• DBMS allows deadlock to occur but
recognizes it and breaks it.
• Usually handled by construction of wait-for
graph
(WFG)
showing
transaction
dependencies:
– Create a node for each transaction.
– Create edge Ti -> Tj, if Ti waiting to lock item locked
by Tj.
• Deadlock exists if and only if WFG
contains cycle.
• WFG is created at regular intervals.
Example - Wait-For-Graph
(WFG)
Recovery
Detection
from
Deadlock
• Several issues:
– choice of deadlock victim;
– how far to roll a transaction back;
– avoiding starvation.
Timestamping
• Transactions ordered globally so that older
transactions, transactions with smaller
timestamps, get priority in the event of
conflict.
• Conflict is resolved by rolling back and
restarting transaction.
• No locks so no deadlock.
Timestamping
Timestamp
A unique identifier created by DBMS that
indicates relative starting time of a
transaction.
• Can be generated by using system clock at
time
transaction
started,
or
by
incrementing a logical counter every time
a new transaction starts.
Timestamping
• Read/write proceeds only if last update on
that data item was carried out by an older
transaction.
• Otherwise,
transaction
requesting
read/write is restarted and given a new
timestamp.
• Also timestamps for data items:
– read-timestamp - timestamp of last transaction
to read item;
– write-timestamp - timestamp of last
transaction to write item.