Transcript Read(B) - Fakultas Ilmu Komputer Universitas Indonesia

Transaction
Indra Budi
[email protected]
Exercise
A series of actions to be taken on the
database such that either all actions are
completed successfully, or none of them can
be completed, is known as a(n):
a.) checkpoint.
b.) log.
c.) lock.
d.) transaction.
e.) concurrent.
2
Fakultas Ilmu Komputer UI
Exercise
When two transactions are being processed
against the database at the same time,
a.) they are called concurrent transactions.
b.) they are usually interleaved.
c.) they always result in a lost update
problem.
d.) one must be rolled back.
e.) both a and b
3
Fakultas Ilmu Komputer UI
Exercise
1.
2.
3.
4.
5.
6.
7.
8.
4
…. means a transaction executes when all actions of the transaction are completed
fully, or none are. This means there are no partial transactions (such as when half
the actions complete and the other half do not).
…. involves beginning a transaction with a ’consistent’ database, and finishing with a
’consistent’ database. For example, in a bank database, money should never be
”created” or ”deleted” without an appropriate deposit or withdrawal. Every
transaction should see a consistent database.
….ensures that a transaction can run independently, without considering any side
effects that other concurrently running transactions might have.
….define the persistence of committed data: once a transaction commits, the data
should persist in the database even if the system crashes before the data is written
to non-volatile storage.
A …. is a series of (possibly overlapping) transactions.
A …..occurs when a transaction reads a database object that has been modified by
another not-yet-committed transaction.
A ….over a set S of transactions is a schedule whose effect on any consistent
database instance is identical to that of some complete serial schedule over the set
of committed transactions in S.
A …..is one in which a transaction can commit only after all other transactions
whose changes it has read have committed.
Fakultas Ilmu Komputer UI
a.
b.
c.
d.
e.
f.
g.
h.
i.
j.
5
Recoverable schedule
Serializable schedule
Schedule
Atomicity
Durability
Isolation
Dirty Read
Unrepeatable problem
Consistency
Cascading rollback
Fakultas Ilmu Komputer UI
Exercise
Do exercise 17.23 Elmasri pages 581
6
Fakultas Ilmu Komputer UI
Schedule C is Serializable ?
T1
Read(A); A  A+100
Write(A);
T2
Read(A);A  A2;
Write(A);
Read(B); B  B+100;
Write(B);
A
25
125
250
125
Read(B);B  B2;
Write(B);
250
7
Fakultas Ilmu Komputer UI
B
25
250
250
Is Schedule D serializable ?
T1
Read(A); A  A+100
Write(A);
T2
Read(A);A  A2;
Write(A);
Read(B);B  B2;
Write(B);
A
25
125
250
50
Read(B); B  B+100;
Write(B);
250
8
Fakultas Ilmu Komputer UI
B
25
150
150
Database Administration
All large and small databases need database
administration
Data administration refers to a function concerning
all of an organization’s data assets
Database administration (DBA) refers to a person or
office specific to a single database and its
applications
9
Fakultas Ilmu Komputer UI
DBA Tasks
Managing database structure
Controlling concurrent processing
Managing processing rights and responsibilities
Developing database security
Providing for database recovery
Managing the DBMS
Maintaining the data repository
10
Fakultas Ilmu Komputer UI
Managing Database Structure
DBA’s tasks:
Participate in database and application development
• Assist in requirements stage and data model creation
• Play an active role in database design and creation
Facilitate changes to database structure
•
•
•
•
•
11
Seek community-wide solutions
Assess impact on all users
Provide configuration control forum
Be prepared for problems after changes are made
Maintain documentation
Fakultas Ilmu Komputer UI
Concurrency Control
Concurrency control ensures that one user’s
work does not inappropriately influence
another user’s work
No single concurrency control technique is ideal for all
circumstances
Trade-offs need to be made between level of protection
and throughput
12
Fakultas Ilmu Komputer UI
Atomic Transactions
A transaction, or logical unit of work (LUW), is a series
of actions taken against the database that occurs as
an atomic unit
Either all actions in a transaction occur or none of them do
13
Fakultas Ilmu Komputer UI
Example: Atomic Transaction
14
Fakultas Ilmu Komputer UI
Example: Atomic Transaction
15
Fakultas Ilmu Komputer UI
Concurrent Transaction
Concurrent transactions refer to two or more
transactions that appear to users as they are being
processed against a database at the same time
In reality, CPU can execute only one instruction at a
time
Transactions are interleaved meaning that the operating system
quickly switches CPU services among tasks so that some portion
of each of them is carried out in a given interval
Concurrency problems: lost update and inconsistent
reads
16
Fakultas Ilmu Komputer UI
Example: Concurrent Transactions
17
Fakultas Ilmu Komputer UI
Example: Lost Update Problem
18
Fakultas Ilmu Komputer UI
Concurrency Control and Locking
We need a way to guarantee that our
concurrent transactions can be serialized.
Locking is one such means.
Locking is done to data items in order to
reserve them for future operations.
A lock is a logical flag set by a transaction to
alert other transactions the data item is in
use.
19
Fakultas Ilmu Komputer UI
Resource Locking
Resource locking prevents multiple applications from
obtaining copies of the same record when the record
is about to be changed
20
Fakultas Ilmu Komputer UI
Characteristics of Lock
Locks may be applied to data items in two ways:
Implicit Locks are applied by the DBMS
Explicit Locks are applied by application programs.
Locks may be applied to:
1.
2.
3.
4.
5.
a single data item (value)
an entire row of a table
a page (memory segment) (many rows worth)
an entire table
an entire database
This is referred to as the Lock granularity
Locks may be of type types depending on the
requirements of the transaction:
1.
2.
21
An Exclusive Lock prevents any other transaction from reading or
modifying the locked item.
A Shared Lock allows another transaction to read an item but
prevents another transaction from writing the item.
Fakultas Ilmu Komputer UI
Example: Explicit Locks
22
Fakultas Ilmu Komputer UI
The Two-Phase Locking Protocol
This is a protocol which ensures conflictserializable schedules.
Phase 1: Growing Phase
transaction may obtain locks
transaction may not release locks
Phase 2: Shrinking Phase
transaction may release locks
transaction may not obtain locks
The protocol assures serializability. It can be
proved that the transactions can be serialized
in the order of their lock points (i.e. the point
where a transaction acquired its final lock).
23
Fakultas Ilmu Komputer UI
2PL Examples
User A places an exclusive lock on the balance
User A reads the balance
User A deducts $100 from the balance
User B attempts to place a lock on the balance but
fails because A already has an exclusive lock User B
is placed into a wait state
User A writes the new balance of $100
User A releases the exclusive lock on the balance
User B places an exclusive lock on the balance
User B reads the balance
User B deducts $100 from the balance
User B writes the new balance of $100
24
Fakultas Ilmu Komputer UI
2PL Example
User A places a shared lock on item raise_rate
User A reads raise_rate
User A places an exclusive lock on item Amy_salary
User A reads Amy_salary
User B places a shared lock on item raise_rate
User B reads raise_rate
User A calculates a new salary as Amy_salary * (1+raise_rate)
User B places an exclusive lock on item Bill_salary
User B reads Bill_salary
User B calculates a new salary as Bill_salary * (1+raise_rate)
User B writes Bill_salary
User A writes Amy_salary
User A releases exclusive lock on Amy_salary
User B releases exclusive lock on Bill_Salary
User B releases shared lock on raise_rate
User A releases shared lock on raise_rate
25
Fakultas Ilmu Komputer UI
Deadlock
User A places an exclusive lock on item 1001
User B places an exclusive lock on item 2002
User A attempts to place an exclusive lock on item
2002 User A placed into a wait state
User B attempts to place an exclusive lock on item
1001 User B placed into a wait state
This is called a deadlock. One transaction has
locked some of the resources and is waiting
for locks so it can complete. A second
transaction has locked those needed items
but is awaiting the release of locks the first
transaction is holding so it can continue.
26
Fakultas Ilmu Komputer UI
Deadlock
Deadlock, or the deadly embrace, occurs when two
transactions are each waiting on a resource that the
other transaction holds
Preventing deadlock
Allow users to issue all lock requests at one time
Require all application programs to lock resources in the same
order
Breaking deadlock
Almost every DBMS has algorithms for detecting deadlock
When deadlock occurs, DBMS aborts one of the transactions and
rollbacks partially completed work
27
Fakultas Ilmu Komputer UI
Another Deadlock Example
28
Fakultas Ilmu Komputer UI
Optimistic/Pessimistic Locking
Optimistic locking assumes that no transaction
conflict will occur
DBMS processes a transaction; checks whether conflict occurred
• If not, the transaction is finished
• If so, the transaction is repeated until there is no conflict
Pessimistic locking assumes that conflict will occur
Locks are issued before transaction is processed, and then the
locks are released
Optimistic locking is preferred for the Internet and
for many intranet applications
29
Fakultas Ilmu Komputer UI
Example: Optimistic Locking
30
Fakultas Ilmu Komputer UI
Example: Pessimistic Locking
31
Fakultas Ilmu Komputer UI
Final Test
Scheduled on Dec, 29th 2004, 09.00 –
11.00 WIB
May open all notes written by hand, no
copies, no print-out, close textbook
Material from “Introduction to DB” to
Concurrency Control
32
Fakultas Ilmu Komputer UI
Next Wednesday (Dec 15th)
Quiz
Close Books & Close Notes
Material
SQL (Join, Aggregation, Grouping, Having, View)
Transactions Processing & Concurrency Control
33
Fakultas Ilmu Komputer UI