Transcript Transactions

Transactions
Transaction Processing
Most of the information systems in businesses are
transaction based (databases or TP-Monitors).
 The market for transaction processing is many tens of
billions of dollars per year
 Past: transaction processing was used mostly in large
companies
 Now: WWW, small TP-Monitors, CORBA, Internet
provider, J2EE have a need for transactions
 Transactions are now no more just a database
technology, they started to be a core distributed
information systems technology.

421B: Database Systems - Transactions
2
Business transactions
Business transaction: involves exchange
between two or more entities (selling, buying,
renting, booking ….).
 In computers: business transactions become
electronic transactions (same ideas)

Seller
Buyer
State
State
State
Book-keeping
421B: Database Systems - Transactions
3
Money Transfer
1.
Transfer(id1,id2,value)
SELECT balance into :balance
FROM accounts
WHERE accountid = :id1
balance += value
UPDATE accounts
SET balance = :balance
WHERE accountid = :id1
1.
2.
3.
4.
5.
6.
read(A)
A := A + 50
write(A)
read(B)
B := B - 50
write(B)
SELECT balance into :balance
FROM accounts
WHERE accountid = :id2
balance -= value
UPDATE accounts
SET balance = :balance
WHERE accountid = :id2
421B: Database Systems - Transactions
4
Electronic transaction

A txn encapsulate operations that belong
logically together
 Operations within the DBS increase account of
seller, decrease account of buyer
 Operations outside the DBS: the good is given to
the buyer
 Electronic txn: programming and DB part
 Consider transaction as a sphere of control in which
to encapsulate certain behavior of particular pieces
of code
 A transaction is basically a set of service
invocations, usually from a program (or a interactive
user interface)
421B: Database Systems - Transactions
5
Some issues

An electronic transaction is not simply a
sequence of DB operations but a complex idea:
 Specify of what operations build a transaction
 Transactions must be correct even if many of them
are running concurrently (at the same time over the
same data).
 Transactions must be atomic. Partially executed
transactions are almost always incorrect
 Legally, most business transactions require a
written record. So do electronic transactions
 The system must handle a high volume (high
throughput), must provide fast response time, and
must be available all the time (mission critical).
421B: Database Systems - Transactions
6
Automated Teller Machine

Tables:
 AccountBalance(Acct#, balance)
 HotCard-List(Accct#): stolen/canceled/suspended cards
 AccountVelocity(Acct#,sumWithdrawals): stores the latest
transaction and accumulated amount
 PostingLog(Acct#,ATMid,Amount): a record of each operation

Operations for withdrawal:









GetInput(Acct#,ATMid,type,PIN,Txn-id,Amount)
Write request to PostingLog
Check PIN
Check Acct# with HotCard-List table
Check Acct# with AccountVelocity table
Update AccountVelocity table
Update balance in AccountBalance
Write withdrawal record to PostingLog
Commit transaction and dispense money.
421B: Database Systems - Transactions
7
Stock Exchange

Tables
 Users: list of traders and market watchers
 Stocks
 BuyOrders/SellOrders: orders entered during the day
 Trades: all trades executed during the day
 Price: buy and sell total volume, and number of orders
for each stock and price
 Log: all users’ requests and system replies
 notificationMsg: all messages sent to the users
421B: Database Systems - Transactions
8
Stock Exchange

Operation execute trade:
 Read information about stock from stock table
 Get timestamp
 Read scheduled trading periods for the stock
 Check validity of operation (time, value, price)
 If valid, find a matching trade operation, update trades,
notif, orders, prices, stocks, …
 Write the system’s response to the log
 Commit transaction
 Broadcast the new situation

Volumes bank/stock: 250000 transactions per day
421B: Database Systems - Transactions
9
Application vs. DBS
A user’s program may carry out many operations on the data
retrieved from the database, but the DBMS is only
concerned about what data is read/written from/to the
database.
 A transaction is the DBMS’s abstract view of a user
program: a sequence of read operations r(X) and write
operations w(X) on objects (X) (tuple, relation,…) of the DB

 Read: bring object into main memory from disk, send value to
application (same as copy value into program variable)
 Write: bring object into main memory from disk and modify it.
Write it back to disk (might be done sometime later)

Need for concurrency:
 There might be considerable time between the submission of two
consecutive SQL statements within one transaction
 While the DBS is reading a tuple from the disk, the CPU can be used
to parse and compile the SQL statements of other transactions.
421B: Database Systems - Transactions
10
ACID Properties

The success of transactions was due to well-defined
properties that are provided by transactional systems:
 ATOMICITY: A transaction is atomic if it is executed in its
entirety or not at all
 CONSISTENCY: a transaction must preserve the consistency
of the data
 ISOLATION: in case that transactions are executed
concurrently: The effect must be the same as if each
transaction were the only one in the system.
 DURABILITY: The changes made by a transaction must be
permanent (= they must not be lost in case of failures)
421B: Database Systems - Transactions
11
Atomicity

ALL or NOTHING principle
 In Money transfer: if transaction fails after step 3 partial
results should be undone
 A transaction T might commit after completing all its actions.
If the user is informed about the commit he/she can be sure
that all changes performed by T are installed in the DB.
 A transaction might abort (or be aborted by the DBMS) after
executing some actions. In this case the DBMS undoes all
modifications so far. After the abort the DB state is as if the
transaction had never started. After notification of abort the
user knows that none of the transaction’s modifications is
reflected in the database.
 Local recovery: eliminating partial results
421B: Database Systems - Transactions
12
Atomicity
consistent
database
Txn
Recovery
Manager
421B: Database Systems - Transactions
abort
inconsistent
database
Database
log
13
Consistency

Each transaction must leave the database in a consistent state if
the DB is consistent when the transaction begins.
 In Money transfer: sum of balances of two accounts the same before




and after transaction execution
This is the responsibility of the application!
In practice, transactions make mistakes (introduce negative
salaries, empty social security numbers, different names for the
same person …). These mistakes violate database consistency.
Limited forms of data consistency can be enforced by the system
through integrity constraints: Null constraints, foreign keys,
check constraints…
Integrity constraints acts as filters determining whether a
transaction is acceptable or not.
consistent
database
421B: Database Systems - Transactions
Txn
consistent
database
14
Isolation


Isolation: Users submit transactions, and can think of each
transaction as executing by itself.
The DBMS, however, allows many transactions being
executed concurrently over the same data.
 By allowing several transactions to execute concurrently we can
keep the CPU busy and process more transactions per time unit.

Isolation is enforced by a concurrency control protocol,
which aims at guaranteeing serializability.
 Net effect of transactions executing concurrently is identical to
executing all transactions one after the other in some serial order
 In Money transfer: disallow that concurrent transaction reads
balance of both accounts after step 3 and before step 6
421B: Database Systems - Transactions
15
Isolation
consistent
database
Txn 1
Txn 2
inconsistent
database
Txn 1
Txn 2
…
consistent
database
consistent
database
Concurrency
Control
421B: Database Systems - Transactions
16
Durability

Durability: There must be a guarantee that the changes
introduced by a transaction will last,i.e., survive failures
• In Money transfer: once user has confirmation the change must
persist in database despite possible crash
• In case of failures,
 Server Crash:

restart server and perform global recovery:
 bringing the database back into an consistent state
 At the time of the crash, some transactions might have been in the
middle of execution: their modifications performed so far must be
undone (abort of these transactions)
 At the time of the crash, not all changes of committed transactions
might have been reflected in the database: redo these
modifications.
In order to be able to perform recovery: logging of additional
information during normal processing
 Disk Crash: use replication: database backups, mirrored disks.

421B: Database Systems - Transactions
17
Global Recovery
consistent Txn consistent Txn
db
db
inconsistent db
System
crash
Recovery
Manager
421B: Database Systems - Transactions
Database
log
18
Architecture
Txn
(r,w,c,a)
Transaction
Manager
Scheduler
(concurrency control)
Recovery Manager
Buffer Manager
Buffer
421B: Database Systems - Transactions
Stable
DB
Log
19
Architecture

Each of the modules is a complex component
 Highly optimized; many engineering optimizations

Components highly interconnected
 Modularity only partially given
 Complex calling strategies
 One module relies heavily on the semantics of other
modules
421B: Database Systems - Transactions
20