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 Jim’s.
– Sam’s account should be reduced by 100. – Jim’s account should be increased by 100.
6
Sam’s account should be reduced by 100.
Q1. Which of the following SQL statements is correct for the above operation? Assume Sam’s 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 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:
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 Jim’s account while the transaction T1 (the money transfer from Sam’s to Jim’s) 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 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.
12
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.
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 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 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 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.
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