Update, Delete and Transaction
Management
2MODIFYING ROWS USING
UPDATE AND DELETE
3UPDATE
▪ Changes the value of existing data.
▪ For example, at the end of semester, change the mark and grade
from null to the actual mark and grade.
UPDATE table
SET column = (subquery) [, column = value, ...]
[WHERE condition];
UPDATE enrolment
SET mark = 80,
grade ='HD'
WHERE sno = 112233
and ……
UPDATE enrolment
SET mark = 85
WHERE unit_code = (SELECT unit_code FROM unit WHERE
unit_name='Introduction to databases')
AND mark = 80;
4DELETE
• Removing data from the database
DELETE FROM table
[WHERE condition];
DELETE FROM enrolment
WHERE sno='112233'
AND
unit_code= (SELECT unit_code FROM unit
WHERE unit_name='Introduction to Database' )
AND
semester='1'
AND
year='2012';
5TRANSACTIONS
6Transactions
▪ Consider the following situation.
Sam is transferring $100 from his bank account to his
friend Jim’s.
– Sam’s account should be reduced by 100.
– Jim’s account should be increased by 100.
8Assume that Jim’s account number is '333'. The transfer of money
from Sam’s to Jim’s account will be written as the following SQL
transaction:
UPDATE account
SET balance= balance – 100
WHERE acc_no = '123';
UPDATE account
SET balance= balance + 100
WHERE acc_no = '333';
COMMIT;
SQL
statements
T
R
A
N
S
A
C
T
IO
N
All statements need to be run as a single logical unit
operation.
9Transaction Properties
▪ A transaction must have the following properties:
– Atomicity
• all database operations (SQL requests) of a transaction must be entirely
completed or entirely aborted
– Consistency
• it must take the database from one consistent state to another
– Isolation
• it must not interfere with other concurrent transactions
• data used during execution of a transaction cannot be used by a second
transaction until the first one is completed
– Durability
• once completed the changes the transaction made to the data are durable,
even in the event of system failure
12
Consistency - Example
▪ Assume that the server lost its power during the execution
of the money transfer transaction, only the first statement is
completed (taking the balance from Sam’s).
▪ Consistency properties ensure that Sam’s account will be
reset to the original balance because the money has not be
transferred to Jim’s account.
▪ The last consistent state is when the money transfer
transaction has not been started.
13
Durability - Example
▪ Assume the server lost power after the commit statement
has been reached.
▪ The durability property ensures that the balance on both
Sam’s and Jim’s accounts reflect the completed money
transfer transaction.
14
Transaction Management
▪ Follows the ACID properties.
▪ Transaction boundaries
– Start
• first SQL statement is executed (eg. Oracle)
• Some systems have a BEGIN WORK type command
– End
• COMMIT or ROLLBACK
▪ Concurrency Management
▪ Restart and Recovery.
15
Serial and Interleaved transactions.
T0 T1
Read(X)
X=X+1
Write(x)
Read(Y)
Y=Y*2
Write(Y)
Read(x)
X=X+2
Write(X)
T0 T1
Read(X)
X=X+1
Write(x)
Read(Y)
Y=Y*2
Write(Y)
Read(x)
X=X+2
Write(X)
Serial Interleaved (non Serial)
Time:
Concurrency
16
The impact of interleaved transactions
17
Concurrency Management - Solution
▪ Locking mechanism.
▪ A mechanism to overcome the problems caused by interleaved
transactions.
▪ A lock is an indicator that some part of the database is temporarily
unavailable for update because:
– one, or more, other transactions is reading it, or,
– another transaction is updating it.
▪ A transaction must acquire a lock prior to accessing a data item and
locks are released when a transaction is completed.
▪ Locking, and the release of locks, is controlled by a DBMS process
called the Lock Manager.
18
Lock Granularity
▪ Granularity of locking refers to the size of the units that are, or can be,
locked. Locking can be done at
– database level
– table level
– page level
– record level
Allows concurrent transactions to access different rows of the
same table, even if the rows are located on the same page.
– attribute level
Allows concurrent transactions to access the same row, as long as
they require the use of different attributes within that row.
19
Lock Types
– Shared lock. Multiple processes can simultaneously hold
shared locks, to enable them to read without updating.
• if a transaction Ti has obtained a shared lock (denoted by S)
on data item Q, then Ti can read this item but not write to this
item
– Exclusive lock. A process that needs to update a record
must obtain an exclusive lock. Its application for a lock will
not proceed until all current locks are released.
• if a transaction Ti has obtained an exclusive lock (denoted X)
on data item Q, then Ti can both read and write to item Q
20
Exclusive Locks – Example 1
• Write-locked items
• require an Exclusive Lock
• a single transaction exclusively holds the lock on the item
Trans 1 Part # QOH
P1 35 Xlock(P1)
Read P1 (35)
QOH = QOH + 100
Write P1 (135)
Unlock(P1)
Part # QOH
P1 135
Part # QOH
P1 105
Trans 2
Attempt to Lock
Wait for Trans 1
Xlock(P1)
Read P1 (135)
QOH = QOH - 30
Write P1 (105)
Unlock(P1)
Database
21
Shared Locks – Example 2
Database Trans 2
Part # QOH
P1 35
Slock(P1)
Read P1 (35)
Slock(P1)
Read P1 (35)
Trans 1
•Read-locked items
• require a Shared Lock
• allows other transactions to read the item
•Shared locks improve the amount of concurrency in a system
If Trans 1 and Trans 2 only wished to read P1 with no subsequent update
they could both apply an Slock on P1 and continue
24
Lock - Problem
▪ Deadlock.
Scenario:
– Transaction 1 has an exclusive lock on data item A, and
requests a lock on data item B.
– Transaction 2 has an exclusive lock on data item B, and
requests a lock on data item A.
Result: Deadlock, also known as “deadly embrace”.
Each has locked a resource required by the other, and will
not release that resource until it can either commit, or abort.
Unless some “referee” intervenes, neither will ever proceed.
25
Dealing with Deadlock
▪ Deadlock prevention
– A transaction must acquire all the locks it requires before it
updates any record.
– If it cannot acquire a necessary lock, it releases all locks, and tries
again later.
▪ Deadlock detection and recovery
– Detection involves having the Lock Manager search the Wait-for
tables for lock cycles.
– Resolution involves having the Lock Manager force one of the
transactions to abort, thus releasing all its locks.
26
Dealing with Deadlock
▪ If we discover that the system is in a state of deadlock, some of
the transactions causing the deadlock must be aborted. Choosing
which transaction to abort is called as victim selection.
▪ The algorithm for victim selection should generally avoid selecting
transactions that have been running for a long time and that have
performed many updates, and should try instead to select
transactions that have not made any changes or that are involved
in more than one deadlock cycle in the wait-for graph.
27
Database Restart and Recovery
▪ Restart
– Soft crashes
• loss of volatile storage, but no damage to disks. These
necessitate restart facilities.
▪ Recovery
– Hard crashes
• hard crashes - anything that makes the disk permanently
unreadable. These necessitate recovery facilities.
▪ Requires transaction log.
28
Transaction Log
▪ The log, or journal, tracks all transactions that update the database.
It stores
– For each transaction component (SQL statement)
• Record for beginning of transaction
• Type of operation being performed (update, delete, insert)
• Names of objects affected by the transaction (the name of the table)
• “Before” and “after” values for updated fields
• Pointers to previous and next transaction log entries for the same
transaction
• The ending (COMMIT) of the transaction
The log should be written to a multiple separate physical devices from that
holding the database, and must employ a force-write technique that ensures that
every entry is immediately written to stable storage, that is, the log disk or tape.
29
Sample Transaction Log
30
Checkpointing
• Although there are a number of techniques for checkpointing, the
following explains the general principle. A checkpoint is taken regularly,
say every 15 minutes, or every 20 transactions.
• The procedure is as follows:
– Accepting new transactions is temporarily halted, and current
transactions are suspended.
– Results of committed transactions are made permanent
(force-written to the disk).
– A checkpoint record is written in the log.
– Execution of transactions is resumed.
31
Oracle database – not examined
32
Write Through Policy
▪ The database is immediately updated by transaction
operations during the transaction's execution, before the
transaction reaches its commit point
▪ If a transaction aborts before it reaches its commit point a
ROLLBACK or UNDO operation is required to restore the
database to a consistent state
▪ The UNDO (ROLLBACK) operation uses the log before values
33
Restart Procedure for Write Through
▪ Once the cause of the crash has been rectified, and the database is
being restarted:
– The last checkpoint before the crash in the log file is identified. It is
then read forward, and two lists are constructed:
– a REDO list containing the transaction-ids of transactions that
were committed.
– and an UNDO list containing the transaction-ids of transactions
that never committed
▪ The database is then rolled forward, using REDO logic and the
after-images and rolled back, using UNDO logic and the before-images.
35
An alternative - Deferred Write
▪ The database is updated only after the transaction reaches
its commit point
▪ Required roll forward (committed transactions redone) but
does not require rollback
36
Recovery
▪ A hard crash involves physical damage to the disk, rendering it
unreadable. This may occur in a number of ways:
– Head-crash. The read/write head, which normally “flies” a
few microns off the disk surface, for some reason actually
contacts the disk surface, and damages it.
– Accidental impact damage, vandalism or fire, all of which
can cause the disk drive and disk to be damaged.
▪ After a hard crash, the disk unit, and disk must be replaced,
reformatted, and then re-loaded with the database.
37
Backup
– A backup is a copy of the database stored on a different device to
the database, and therefore less likely to be subjected to the same
catastrophe that damages the database. (NOTE: A backup is not
the same as a checkpoint.)
– Backups are taken say, at the end of each day’s processing.
– Ideally, two copies of each backup are held, an on-site copy, and
an off-site copy to cater for severe catastrophes, such as building
destruction.
– Transaction log – backs up only the transaction log operations that
are not reflected in a previous backup of the database.
38
Recovery
▪ Rebuild the database from the most recent backup.
This will restore the database to the state it was in say,
at close-of-business yesterday.
▪ REDO all committed transactions up to the time of the
failure - no requirement for UNDO

























































































































































































































































































































































学霸联盟