Update, Delete and Transaction Management
MODIFYING ROWS USING UPDATE AND DELETE
2
UPDATE
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 enrolment SET mark = 80,
grade =HD WHERE sno = 112233
and
UPDATE table
SET column = (subquery) [, column = value, ] [WHERE condition];
UPDATE enrolment
SET mark = 85
WHERE unit_code = (SELECT unit_code FROM unit WHERE
AND mark = 80;
unit_name=Introduction to databases)
3
DELETE
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;
4
TRANSACTIONS
5
Transactions
Consider the following situation.
Sam is transferring $100 from his bank account to his
friend Jims.
Sams account should be reduced by 100. Jims account should be increased by 100.
6
Sams account should be reduced by 100.
Q1. Which of the following SQL statements is correct for the above operation? Assume Sams account number is 123.
A. UPDATE account
SET balance = balance 100;
B. UPDATE account
SET balance= balance 100 WHERE acc_no = 123;
C. UPDATE account
SET acc_no = balance + 100;
D. UPDATE account
SET balance = balance + 100 WHERE acc_no = 123;
7
Assume that Jims account number is 333. The transfer of money from Sams to Jims account will be written as the following SQL transaction:
T R A N S A C T IO N
UPDATE account
SET balance= balance 100
WHERE acc_no = 123;
UPDATE account SQL
SET balance= balance + 100 statements WHERE acc_no = 333;
COMMIT;
All statements need to be run as a single logical unit operation.
8
Transaction 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
9
Q2. According to the atomicity property, the transaction below is complete when statement number _____ is completed.
1 2 3
UPDATE account
SET balance= balance 100
WHERE acc_no = 123;
UPDATE account
SET balance= balance + 100
WHERE acc_no = 333;
COMMIT;
A. 1 B. 2 C. 3
D. None of the above.
10
Q3. Which transaction property is violated when a transaction T2 (Jim checking the account balance) is allowed to read the balance of Jims account while the transaction T1 (the money transfer from Sams to Jims) has not been completed?
A. Atomicity.
B. Isolation.
C. Consistency.
D. Durability.
11
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 Sams).
Consistency properties ensure that Sams account will be reset to the original balance because the money has not be transferred to Jims account.
The last consistent state is when the money transfer transaction has not been started.
12
Durability Example
Assume the server lost power after the commit statement has been reached.
The durability property ensures that the balance on both Sams and Jims accounts reflect the completed money transfer transaction.
13
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.
14
Concurrency
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)
Time:
Serial
Interleaved (non Serial)
15
The impact of interleaved transactions
16
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.
17
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.
18
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
19
Exclusive Locks Example 1
Write-locked items
require an Exclusive Lock
a single transaction exclusively holds the lock on the item
Trans 1
Xlock(P1)
Read P1 (35)
QOH = QOH + 100 Write P1 (135) Unlock(P1)
Database
Trans 2
Attempt to Lock Wait for Trans 1
Xlock(P1)
Read P1 (135) QOH = QOH 30 Write P1 (105) Unlock(P1)
Part # QOH P1 35
Part # QOH P1 135
Part # QOH P1 105
20
Shared Locks Example 2
Read-locked items
require a Shared Lock
allows other transactions to read the item
Trans 1 Database Trans 2
Part # QOH P1 35
Slock(P1)
Read P1 (35)
Slock(P1)
Read P1 (35)
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
21
Lock Example 3 what happens?
22
Wait-For-Graph (WFG)
23
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.
24
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.
25
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.
26
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.
27
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
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.
should be written to a multiple separate physical devices from that
28
Sample Transaction Log
29
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.
30
Oracle database not examined
31
Write Through Policy
The database is immediately updated by transaction operations during the transactions 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 32
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.
33
Q4. What transaction will need to be REDONE (in the REDO list)?
tc =time of checkpoint tf = time of failure
A. T1 and T2.
B. T2 and T4.
C. T2 and T5.
D. T1, T2 and T3.
E. None of the above.
34
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
35
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.
36
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 days 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.
37
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
38
Reviews
There are no reviews yet.