Transcript Introduction to PL/SQL Lecture 1 [Part 1]

Introduction to PL/SQL
Lecture 1 [Part 1]
Nick Rossiter (Emma-Jane Phillips-Tait)
Room PB121
[email protected]
Overview of next 3 sessions






Overview of PL/SQL
Development of a coded block
Interacting with an Oracle Database
Controlling PL/SQL process flow
Cursor handling
Error handling
Session 1



Using PL/SQL to access Oracle
Variable assignment
Overview of the next 2 weeks
Re-visiting SQL



Instructions to Oracle identifying the
information you wish to select, insert,
delete or update
SQL*Plus is Oracle's version of the SQL
standard
Notes on SQL are on Blackboard
PL/SQL - introduction




Procedural extension allowing for
modularity, variable declaration, loops and
logical constructs.
Allows for advanced error handling
Communicates natively with other oracle
database objects.
Managed centrally within the Oracle
database.
Other Databases


All have procedural facilities
SQL is not functionally complete



Lacks full facilities of a programming language
So top up functionality by embedding SQL in a
procedural language
PL/SQL techniques are specific to Oracle

but procedures and functions can be ported to other
systems
Why use PL/SQL




Manage business rules – through middle
layer application logic.
Generate code for triggers
Generate code for interface
Enable database-centric client/server
applications
Centralised V’s De-centralised
Begin
:
Server
End;
Common copy of
executed code – one
copy to maintain
Begin
Begin
Begin
:
:
:
End;
End;
End;
Multiple copies of
executable code on
the decentralised
system – multiple
copies to maintain
leading to increase
difficulty in
maintaining the
system
Server
Advantages of using PL/SQL to
access Oracle




PL/SQL is managed centrally within the database
Code is managed by the DBA and execution
privileges are managed in the same was as with
other objects
PL/SQL objects are first-class Oracle DB objects
Easy to read
 With modularity features and error handling
Centralised control

Enables DBA to:


Specify rules in one place (as procedure,
function, package in PL/SQL)
Force user access through the predefined
PL/SQL so users cannot write their own
procedural code and use this instead.

Define for instance security privileges giving users
access to table(s) only through a particular
procedure
Using PL/SQL as a programming
language

Permits all operations of standard programming
languages e.g.



Provides loops for controlling iteration


Conditions IF-THEN-ELSE-END IF;
Jumps GOTO
LOOP-EXIT; WHEN-END LOOP; FOR-END
LOOP; WHILE-END LOOP
Allows extraction of data into variables and its
subsequent manipulation
Modules in PL/SQL
There are 4 types of modules in PL/SQL

Procedures – series of statements may or may
not return a value
 Functions – series of statements must return a
single value
 Triggers – series of PL/SQL statements (actions)
executing after an event has triggered a condition
(ECA)
 Packages – collection of procedures and function
that has 2 parts:
 a listing and a body.
Procedures
Creation command
Create or replace procedure sample1 as
Variable declarations
v_num1 constant number := 2.5;
v_num2 constant number := 4;
v_product number;
Body of code
BEGIN
v_product := v_num1 * v_num2;
END;
Use of Data-Types




Number – used to store any number
Char(size) & varchar2(size) e.g.: char(10) –
used to store alphanumerical text strings,
the char data type will pad the value stored
to the full length declared.
Date – used to store dates
Long – used to store large blocks of text up
to 2 gigabytes in length (limited operations)
More data-types



Long raw – stores large blocks of data
stored in binary format
Raw – stores smaller blocks of data in
binary formal
Rowid – used to store the special format of
rowid’s on the database
Non-database Data Types





DEC, DECIMAL, REAL, INTEGER, INT – these are
numerical data types that are a subset of number.
Binary_integer – binary format for number type but can
not be stored in database unless converted first.
Character – same as char
Boolean – true/false value
Table/record – tables can be used to store the equivalent
of an array while records store the variables with
composite data types.
Using SQL in procedures



Select values into PL/SQL variables
 using INTO
Record.element notation will address
components of tuples (dot notation)
%rowtype allows full rows to be selected
into one variable
V_employee employee%rowtype
Empid
empname addr1
addr2
addr3
postcode
grade
salary
Example – Single record retrieval
– No cursor necessary
Declare
v_employee
employee%rowtype;
Begin
select *
Selects entire row of data into 1
variable called v_employee
into v_employee
from employee
where empid = 65284;
update employee
set salary = v_employee.salary + 10000
where empid = v_employee.empid;
end
Is updating the value of
salary based on selected
element of a variable
Cursor overview




Very powerful in PL/SQL modules
Allows more than one set of data to be
retrieved and accessed at the same time in
loops
Sets are created by executing SQL
statements embedded in the PL/SQL code
Cursor attributes - %notfound, %rowcount,
%found & %isopen
Error handling




Prevents database locking
Ensures that errors are presented to the user
in a sensible format
Makes code robust
Essential when using PL/SQL as formal
programming language or interfacing with
Oracle applications.
PL/SQL programming
Procedures and Cursors
Lecture 1 [Part 2]
Nick Rossiter (Emma-Jane Phillips-Tait)
SQL refresher

Basic commands





SELECT, INSERT, DELETE, UPDATE
Always remember to state the table(s) you
are selecting data from
Join tables by primary and foreign keys
Filter data using WHERE clause
SELECT ... FROM ... [WHERE ...]
SQL scripts



Set of commands to run in sequence.
Stored in a ‘note pad’ not data dictionary
and accessed by file name
Executed by @ command
Script called:
Create_lecturer_copy.sql
Executed by:
SQL> @create_lecturer_copy.sql
The SQL Procedure







Block of SQL statements stored in the Data dictionary and
called by applications
Satisfies frequently-used or critical application logic
When called all code within the procedure is executed
(unlike packages)
Action takes place on server not client
Does not return value to calling program
Not available in Oracle 6 or older
Aids security as DBA may grant access to procedures not
tables, therefore some users cannot access tables except
through a procedure
Building a procedure: contents
1.
2.
3.
4.
5.
6.
7.
Create or replace command
Object to be created
Name of object
Any variables accessed or imported
Local variables declared
Code
End procedure declaration
1.
Create or replace
command
2.
Object to be
created
3.
Name of object
4.
Any variables
accessed or
imported
5.
Declared local
variables
6.
Code
7.
End procedure
declaration
This procedure is called inflation_rise and uses a
variable accessed as inf_rate which is a number,
this is passed in when the procedure is used. It
simply updates the salary by the rate of inflation.
Create or replace procedure inflation_rise (inf_rate in number)
Begin
update employee
set salary = salary + (salary * inf_rate / 100);
commit;
End;
Compiling and executing
procedures


Like any program the code needs to be compiled.
@inflation_rise





compiles the procedure in a file with this name
makes it available to the database
Execute inflation_rise executes the procedure.
Remember to compile a procedure again once it
has been amended.
For ease of use, it is best to write procedures in
notepad and then run them, this means that they
can be easily edited and you have a backup copy
Example – Counting Entries
Create or replace procedure validate_customer (v_cust in varchar2(10)) as
Count
number;
Begin
Local variables used
by procedure
count = select count(*)
from customer
SQLwhere cust_code = v_cust;
if count > 0 then
dbms.output ‘customer valid’;
else
dbms.output ‘customer not recognised’;
end if;
End;
Any variables
passed into
procedure
Cursors in SQL




Enable users to loop around a set of data.
Store data selected from a query in a temp
area for use when opened.
Useful in complex actions which would not
be feasible in standard SQL selection
queries
Cursor attributes - %notfound, %rowcount,
%found & %isopen
Syntax for Cursors




Declared as a variable in the same way as
standard variables
Identified as cursor type
SQL retrieval set is included
Cursor cur_emp is
e.g.
Select emp_id, surname ‘name’, grade, salary
From employee
Where regrade is true;
Cursor Population


The data is populated when the cursor is opened.
Once opened the data must be moved from the
temp area to a local variable to be used by the
program.


These variables must be populated in the same order
that the data is held in the cursor.
The cursor data is looped around until an exit
clause is reached.
THE JELLY BABY DEMO!

Data has been selected from the employee table.
This data needs to be amended in the following
way:




Each member of staff is to be increased one grade.
Each member of staff is to have a £500 pay rise
If the pay rise does not take them to the minimum for
their new grade they are to be increased to the
minimum for that grade
If the pay rise moves them above the maximum for a
grade they are to be increased to the maximum only
25463
Create or replace procedure proc_test as
v_empid number;
Cursor cur_sample is
Select empid from employee
where grade > 4;
Begin
open cur_sample;
loop
fetch cur_sample into v_empid;
op whenexit
no when cur_sample%notfound
ore records
update employee
e found
set salary = salary + 500
where empid = v_empid;
end loop;
End;
Data
returned
by cursor
12245
55983
12524
Declare
Cursor
98543
Open cursor for use
Loops round each value
returned by the cursor
Places the value from the
cursor into the variable
v_empid
While loops and cursors
While - loop
Declare
V_empid number;
Cursor cur_sample is
Select empid from employee
where grade > 4;
Begin
open cur_sample;
while cur_sample%found loop
fetch cur_sample into v_empid;
update employee
set salary = salary + 500
where empid = v_empid;
end loop;
End;
Will loop around as
long as %found
returns a true value
For loops and cursors
for - loop
Declare
V_empid number;
V_count number :=1;
V_max
number;
Cursor cur_sample is
Select empid from employee
where grade > 4;
Begin
open cur_sample;
v_max :=cur_sample%rowcount
for v_count in 1 .. V_max loop
fetch cur_sample into v_empid;
update employee
set salary = salary + 500
where empid = v_empid;
v_count := v_count + 1;
end loop;
End;
Declare the additional
variables required
Will loop around as long as
v_count is not greater than
the number of rows
returned
Remember to increment
the value of v_count
Placing cursors into procedures
Creation command
Variable
declarations
Create or replace procedure sample1 as
v_deptname varchar2(10);
v_deptid
number
cursor cur_deptchange is
select distinct deptid, deptname from dept;
BEGIN
open cur_deptchange;
Body of code
loop
fetch cur_dept into v_deptid, v_deptname;
exit when cur_deptchange%notfound
update employee
set emp_deptname = v_deptname
where emp_deptid = v_deptid;
commit;
end loop;
END;
Use of conditions

If statements can be used
If <condition> then
…
Else
…...
End if;
Remember to end the if statement
Use of indented code will make the code easier to
debug!
Notepad file called:
Create_procedures.sql
1) Open SQL*Plus and logon
2) At the prompt enter:
@create_procedures
You will get a prompt which should say ‘procedure created’
3) To run the procedure enter:
Execute proc_test
4) If you check your data you should now find that the procedure has run successfully
Advanced Databases CG096
Lecture 2: Active Databases
• Procedural Extension of DBMS
• using Triggers
Nick Rossiter [Emma-Jane Phillips-Tait]
Content
1 Limitations of Relational Data Model
for performing Information Processing
2 Database Triggers in SQL
3 Using Database Triggers for
Information Processing within DBMS
4 Restrictions for Database Triggers
Limitations of Relational Data Model

Database vs. Information Systems



DBMS manages data regardless of its usage
IS processes information with respect to its usage
Data model vs. system architecture

data model does not give interpretation in terms of
the application domain


e.g. relational model, hierarchical model, set model
IS architecture is developed so, that the data can be
interpreted as information about a particular
applied domain

e.g. HR information, financial information, sales
information
ECA

Event occurs in database


Condition is checked


e.g. addition of new row, deletion of row
e.g. is batch complete? Has student passed?
Actions are executed if condition is satisfied

e.g. send batch to supplier, congratulate student
Extending Information Processing
Capabilities of DBMS using Triggers

Processing of database content, performed by the DBMS
engine itself, not by the application client


Initiated by certain specified condition, depending on the
type of the trigger


execution of the trigger (E)
firing of the trigger (C)
All data actions performed by the trigger execute within the
same transaction in which the trigger fires, but in a separate
session (A)
 Triggers are checked for different privileges as
necessary for the processed data
 Cannot contain transaction control statements
(COMMIT, SAVEPOINT, ROLLBACK not allowed)
Database Triggers in SQL


Not specified in SQL-92, but standardized in SQL3 (SQL1999)
Available in most enterprise DBMS (Oracle, IBM DB2, MS
SQL server) and some public domain DBMS (Postgres)


Some vendor DBMS permit native extensions to SQL for
specifying the triggers


e.g. PL/SQL in Oracle, Transact SQL in MS SQL Server
Some DBMS also allow use of general purpose
programming language instead of SQL


but not present in smaller desktop (Oracle Lite) and public domain
DBMS (MySQL)
e.g. C/C++ in Poet, Java in Oracle, VB in MS Access
Some DBMS extend the triggers beyond tables

for example also to views as in Oracle
Types of SQL Triggers


How many times should the trigger body execute when
the triggering event takes place?
 Per statement: the trigger body executes once for the
triggering event. This is the default.
 For each row: the trigger body executes once for each
row affected by the triggering event.
When the trigger can be fired
 Relative to the execution of an SQL DML statement
(before or after or instead of it)
 Exactly in a situation depending on specific system
resources (e.g. signal from the system clock, expiring
timer, exhausting memory)
Statement and Row Triggers
Example 1: Monitoring Statement Events
SQL> INSERT INTO dept (deptno, dname, loc)
2 VALUES (50, 'EDUCATION', 'NEW YORK');
Execute only once even if multiple rows affected
Example 2: Monitoring Row Events
SQL> UPDATE emp
2 SET sal = sal * 1.1
3 WHERE deptno = 30;
Execute for each row of the table affected by the
event
Firing Sequence of Database
Triggers on a Single Row
BEFORE statement trigger
DEPT table
DEPTNO DNAME
10
20
30
40
ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON
BEFORE row trigger
AFTER row trigger
AFTER statement trigger
Firing Sequence of Database
Triggers on Multiple Rows
EMP table
EMPNO
ENAME
BEFORE statement trigger
DEPTNO
7839
KING
30
7698
BLAKE
30
7788
SMITH
30
BEFORE row trigger
AFTER row trigger
BEFORE row trigger
AFTER row trigger
BEFORE row trigger
AFTER row trigger
AFTER statement trigger
Syntax for creating triggers in SQL






Trigger name - unique within one database schema
Timing - depends on the order of controlled events (before
or after or instead of)
Triggering event - event which fires the trigger (E)
Filtering condition - checked when the triggering event
occurs (C)
Target - table (or view) against which the trigger is fired;
they should be both created within the same schema
Trigger Parameters - parameters used to denote the record
columns; preceded by colon


:new, :old for new and old versions of the values respectively
Trigger action - SQL statements, executed when the trigger
fires; surrounded by begin ... End (A)
Syntax for Creating Statement Triggers
CREATE [OR REPLACE] TRIGGER
trigger_name
timing event1 [OR event2 OR
event3]
ON table_name
BEGIN
SQL statements;
END
The trigger body consisting of SQL statements will be executed
only once according to the prescribed timing, when the event1
(event2, event3) occurs against the monitored table in question
table_name
Example: Registering Operations
SQL> CREATE TRIGGER increase_salary_trg
2
BEFORE UPDATE
3
ON emp
4 BEGIN
5
INSERT INTO sal_hist (increased,
t)
6
VALUES (YES, SYSDATE);
7 END;
8 /
Trigger name: increase_salary_trg
Timing:
BEFORE executing the statement
Triggering event:
UPDATE of table
Target:
emp table
Trigger action: INSERT values INTO sal_hist table
Can stop code
being wrongly
executed more
than once
Syntax for Creating Row Triggers
CREATE [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN condition]
BEGIN
SQL statements;
END
The trigger body consisting of SQL statements will be executed
once for each row affected by event1 (event2, event3) in the
table named table_name subject to the additional condition.
Example: Calculating Derived Columns
SQL>CREATE OR REPLACE TRIGGER derive_commission_trg
2 BEFORE UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN (new.job = 'SALESMAN')
5 BEGIN
6
:new.comm := :old.comm * (:new.sal/:old.sal);
7 END;
8 /
Trigger name:
Timing:
Triggering event:
Filtering condition:
Target:
Trigger parameters:
Trigger action:
derive_commission_trg
BEFORE executing the statement
UPDATE of sal column
Note: no (colon):
before new in WHEN
job = ‘SALESMAN’
emp table
old, new
calculate the new commission
to be updated
Trigger Execution order
1. Execute all BEFORE STATEMENT triggers
2. Disable temporarily all integrity constraints recorded
against the table
3. Loop for each row in the table
 Execute all BEFORE ROW triggers
 Execute the SQL statement against the row and
perform integrity constraint checking of the data
 Execute all AFTER ROW triggers
4. Complete deferred integrity constraint checking against
the table
5. Execute all AFTER STATEMENT triggers
Controlling Triggers using SQL

Disable or Re-enable a database trigger
ALTER TRIGGER trigger_name

Disable or Re-enable all triggers for a
table
ALTER TABLE table_name

DISABLE | ENABLE
DISABLE | ENABLE
ALL TRIGGERS
Removing a trigger from the database
DROP TRIGGER trigger_name
Using Database Triggers for Information
Processing

Auditing Table Operations


Tracking Record Value Changes


e.g. when the factory is closed, all employees should become unemployed
Storing Derived Data


referential integrity must be maintained
Maintenance of Semantic Integrity


each time a record value is changed the previous value is recorded
Protecting Database Referential Integrity: if foreign key
points to changing records


each time a table is accessed auditing information is recorded against it
e.g. the number of items in the trolley should correspond to the current
session selection
Security Access Control

e.g. checking user privileges when accessing sensitive information
Auditing Table Operations
USER_NAME
SCOTT
SCOTT
TABLE_NAME COLUMN_NAME
EMP
EMP
SAL
INS
1
UPD
1
1
DEL
1
JONES
EMP
0
0
0
… continuation
MAX_INS
5
MAX_UPD
5
5
MAX_DEL
5
5
0
0
Example: Counting Statement Execution
SQL>CREATE OR REPLACE TRIGGER audit_emp
2 AFTER DELETE ON emp
3 FOR EACH ROW
4 BEGIN
5
UPDATE audit_table SET del = del + 1
6
WHERE user_name = USER
7
AND table_name = 'EMP’;
7 END;
8 /
Whenever an employee record is deleted from the database,
the counter in an audit table registering the number of deleted
rows for the current user in system variable USER is
incremented.
Example: Tracing Record Value Changes
USER_NAME
EGRAVINA
TIMESTAMP
12-NOV-97
ID OLD_LAST_NAME NEW_LAST_NAME
7950 NULL
HUTTON
NGREENBE
10-DEC-97
7844 MAGEE
TURNER
… continuation
OLD_TITL
E
NULL
CLERK
NEW_TITLE OLD_SALARY
NULL
ANALYST
NEW_SALARY
3500
1100
1100
SALESMAN
Example:
Recording
Changes
SQL>CREATE OR REPLACE TRIGGER audit_emp_values
2
3
4
5
6
7
8
9
10
11
12
AFTER DELETE OR UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_values (user_name,
timestamp, id, old_last_name, new_last_name,
old_title, new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :old.empno, :old.ename,
:new.ename, :old.job, :new.job,
:old.sal, :new.sal);
END;
/
Whenever some details for an employee are deleted or updated,
both the previous and new details are recorded in an audit table
to allow tracing the history of changes. An insert operation
cannot be recorded with this trigger as old.empno has no value.
Example: Protecting Referential Integrity
SQL>CREATE OR REPLACE TRIGGER
cascade_updates
2 AFTER UPDATE OF deptno ON dept
3 FOR EACH ROW
4 BEGIN
5
UPDATE emp
6
SET
emp.deptno = :new.deptno
7
WHERE emp.deptno = :old.deptno;
8 END
9 /
Whenever the department number changes, all employee records
for this department will automatically be changed as well, so that
the employees will continue to work for the same department.
Restrictions for Database Triggers



Problem: impossible to determine certain values during
execution of a sequence of operations belonging to one and
the same transaction
Mutating tables: contain rows which change their values
after certain operation and which are used again before the
current transaction commits
Preventing table mutation:
 Should not contain rows which are constrained by rows
from other changing tables
 Should not contain rows which are updated and read in
one and the same operation
 Should not contain rows which are updated and read via
other operations during the same transaction
Changing Data in a Constraining Table
Example: Reenumeration of the departments
Triggering event
SQL> UPDATE dept
2 SET deptno = 1
3 WHERE deptno = 30;
Trigger action
EMP table
EMPNO ENAME
7698 BLAKE
7654 MARTIN
7499 ALLEN
Constraining
table
Referential integrity
DEPTNO
DEPTNO
30
10
30
20
30
30
40
Failure
DEPT table
DNAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
AFTER UPDATE
Triggered
row
table
xxxxxxxxxxxxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxxxxxxxxxxxx
vvvvvvvvvvvvvvvvvvvvvvvvvvvv
xxxxxxxxxxxxxxxxxxxxxxxxxxxx
CASCADE_UPDATES
trigger
Example: Constraining Table
SQL>
2
3
4
5
6
7
8
9
10
CREATE TRIGGER cascade_updates
AFTER UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
UPDATE emp
SET
emp.deptno = :new.deptno
WHERE emp.deptno = :old.deptno;
END
/
SQL> UPDATE dept
Under the bar is code entered in SQL-PLUS
2 SET
deptno = 1
which triggers cascade_updates in this case.
3 WHERE deptno = 30;
Triggers are not executed directly.
*
ERROR at line 1:
ORA-04091: table DEPT is mutating, trigger/function
may not see it
Rules for Good Practice





Rule 1: Do not change data in the primary key,
foreign key, or unique key columns of any table
Rule 2: Do not update records in the same table
you read during the same transaction
Rule 3: Do not aggregate over the same table you
are updating
Rule 4: Do not read data from a table which is
updated during the same transaction
Rule 5: Do not use SQL DCL (Data Control
Language) statements in triggers
Additional Literature



P. Atzeni, S. Ceri, S.Paraboschi and R.
Torlone. Database Systems, Chapter 12
“Active Databases”. McGraw-Hill (1999)
Oracle Database Server Documentation.
Oracle9i Database Concepts, Chapter 17
“Triggers”.

Application Developer's guide link:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/toc.htm
Oracle Database Server Documentation.
Oracle8i Application Developer's Guide –
Fundaments, Chapter 15 “Using Triggers”.
Advanced Databases CG096
Lecture 3: Transactions and
Recovery

Transactions (ACID)
 Recovery
Nick Rossiter [Emma-Jane Phillips-Tait]
Content
 What is a Transaction?
 ACID properties
 Transaction Processing
 Database Recovery
1. What is a Transaction?
Definition
 The sequence of logically linked actions that access a common
database

often used in online or live systems
Examples
 Airlines ticketing


ATM Cash operation


Check credentials. Check money. Withdraw amount from account.
Pay cash.
Credit card sale


Select an airline route, date and time. Reserve an airline seat. Pay
for seat.
Log on with card. Verify credit card details. Check money. Deliver
goods. Issue withdrawal.
Internet sale
Origin and Needs for Transactions in DB
Automated Teller Machines (ATM)
2. A.C.I.D. properties

Transactions have 4 main properties
 Atomicity - all or nothing
 Consistency - preserve database integrity
 Isolation - execute as if they were run alone
 Durability - results are not lost by a failure
2.1 Atomicity



All-or-nothing, no partial results. An event either happens and
is committed or fails and is rolled back.
 e.g. in a money transfer, debit one account, credit the other.
Either both debiting and crediting operations succeed, or
neither of them do.
 Transaction failure is called Abort
Commit and abort are irrevocable actions. There is no undo for
these actions.
An Abort undoes operations that have already been executed
 For database operations, restore the data’s previous value
from before the transaction (Rollback-it); a Rollback
command will undo all actions taken since the last commit
for that user.
 But some real world operations are not undoable.
2.2 Consistency

Every transaction should maintain DB consistency
 Referential integrity - e.g. each order references an existing
customer number and existing part numbers
 The books balance (debits = credits, assets = liabilities)

Consistency preservation is a property of a transaction, not of
the database mechanisms for controlling it (unlike the A, I,
and D of ACID)

If each transaction maintains consistency,
then a serial execution of transactions does also
2.3 Isolation
Intuitively, the effect of a set of transactions should
be the same as if they ran independently.

Formally, an interleaved execution of transactions is
serializable if its effect is equivalent to a serial one.

Implies a user view where the system runs each user’s
transaction stand-alone.
Of course, transactions in fact run with lots of
concurrency, to use device parallelism – this will be
covered later.

Transactions can use common data (shared data)

They can use the same data processing mechanisms
(time sharing)
2.4 Durability

When a transaction commits, its results will survive failures
(e.g. of the application, OS, DB system … even of the disk).

Makes it possible for a transaction to be a legal contract.

Implementation is usually via a log
 DB system writes all transaction updates to a log file
 to commit, it adds a record “commit(Ti)” to the log
 when the commit record is on disk, the transaction is
committed.
 system waits for disk ack before acknowledging to user
3. Transaction Processing
Can be automatic (controlled by the RDBMS) or
programmatic (programmed using SQL or other
supported programming languages, like PL/SQL)

Identifying critical points for database changes


Preparation for control over transaction progress


through set of database states
using labels of transaction states
Management of the transactions

using explicit manipulation of transaction states and enforcing
transaction operations
Example PL/SQL Procedure handling a Transaction
procedure cashWithdrawal (cardID INTEGER) is
PINcode, PINkeyed, accNo integer;
requestedSum, availSum money;
error, deficit exception;
begin
PINkeyed := inputInteger(‘Please, enter Your PIN’); -- input pin code
SELECT c.pincode, c.accno INTO PINcode, accNo
-- read card pin
FROM cards c
WHERE c.cardid = cardID;
if not (PINcode = PINkeyed) then raise error;
-- authentication
requestedSum := inputMoney(‘Enter cash amount’);
-- input request
SELECT a.balance INTO availSum
-- read account balance
FROM accounts a
WHERE a.accno = accNo;
if (requestedSum > availableSum) then raise deficit;-- authorization
UPDATE accounts a
-- write balance
SET a.balance = a.balance - requestedSum
WHERE a.accno = accNo;
outputString(‘Operation successful’);
COMMIT;
-- commit transaction
exception
outputString(‘Operation cancelled’);
-- rollback transaction
when error or deficit then ROLLBACK;
-- logical problems
when others then ROLLBACK;
-- physical problems
end;
Notes on Procedure


Items in red are transaction-related
Variables are declared of type exception



Similar to Logical/Boolean – set on or off
Exceptions are raised when problems occur
At end either:


commit if no problems or
rollback if exceptions raised

Including others including e.g. disk errors
3.1 Database State and Changes
D1, D2 - Logically consistent states of the database data
TTransaction for changing the database
t1, t2 - Absolute time before and after the transaction
Transaction Parameters

diff D = D2 - D1 can have different scale:



single data item in one memory area
many items across several files and databases
structural changes such as new database schema

 t = t2 - t1 is the time for executing T

T occupies real physical resources


between D1 and D2 there may be intermediate states D11, D12 …;

some of them can be inconsistent

the final state D2 could be unreachable
When T fails
 first come back to D1 (recovery)
 then try again to reach D2 (redo)
Transaction Operations 1
 For recovery purposes the system needs to keep track of
when a transaction starts, terminates and commits.

begin: marks the beginning of a transaction execution

end: specifies that the read and write operations have
ended


marks the end limit of transaction execution
commit: signals a successful end of the transaction

Any updates executed by the transaction can be safely
committed to the database and will not be undone
Transaction Operations 2

rollback: signals that the transaction has ended
unsuccessfully


undo: similar to rollback


Any changes that the transaction may have applied to the
database must be undone
but it applies to a single operation rather than to a whole
transaction
redo: specifies that certain transaction operations must be
redone

to ensure that all the operations of a committed transaction
have been applied successfully to the database
Reading and Writing
Specify read or write operations on the database items that are
executed as part of a transaction

read (X): reads a database item named X into a program variable also
named X.
1. find the address of the disk block that contains item X
2. copy that disk block into a buffer in the main memory
3. copy item X from the buffer to the program variable

write (X): writes the value of program variable X into the database
1. find the address of the disk block that contains item X
2. copy that disk block into a buffer in the main memory
3. copy item X from the program variable named X into its
current location in the buffer
4. store the updated block in the buffer back to disk (this step
updates the database on disk)
3.2 Transaction State and Progress
A transaction reaches its commit point when all
operations accessing the database are completed
and the result has been recorded in the log. It then
writes a [commit, <transaction-id>] and terminates.
BEGIN
END
active
READ , WRITE
COMMIT
partially
committed
committed
ROLLBACK
ROLLBACK
aborted
terminated
When a system failure occurs, search the log file for entries
[start, <transaction-id>]
and if there are no logged entries [commit, <transaction-id>]
then undo all operations that have logged entries
[write, <transaction-id>, X, old_value, new_value]
3.3 Controlling Transactions
Logging transaction states



Save the initial database state D1 before starting the transaction
T: D1->D2 (transaction begins)
Save all intermediate states D11, D12 … (checkpoint logs)
In the case of a failure at an intermediate state D1i before
reaching D2



restore D1 (rollback)
the simplest strategy is to apply a series of atomic actions R which
change the state to the initial state R: D1i->D1
In the case of successful reach of the last intermediate state D2,
force-write or flush the log file to disk and change the database
state to it (transaction ends)
Note: if the transactions are controlled in SQL (using COMMIT), the
rollback operation should be initiated explicitly (using
ROLLBACK)
Entries in the log file





[start, <transaction-id>]: the start of the
execution of the transaction identified
by transaction-id
[read, <transaction-id>, X]: the
transaction identified by transaction-id
reads the value of database item X
[write, <transaction-id>, X, old-value,
new-value]: the transaction identified by
transaction-id changes the value of
database item X from old-value to newvalue
[commit, <transaction-id>]: the
transaction identified by transaction-id
has completed all data manipulations
and its effect can be recorded
[rollback, <transaction-id>]: the
transaction identified by transaction-id
Procedure Credit (
trans_id INTEGER,
accno INTEGER,
bcode CHAR(6),
amount NUMBER)
old NUMBER;
new NUMBER;
begin
SELECT balance INTO old
FROM account
WHERE no = accno and
branch = bcode;
new := old + amount;
UPDATE account
SET amount = new
WHERE no = accno and
branch = bcode;
COMMIT;
EXCEPTION
WHEN FAILURE THEN
ROLLBACK;
17
Controlling Subtransactions


All intermediate states of the transaction which are end
states of the defined subtransactions should become
consistent database states
In the case of successful reach of an intermediate state of this
type the actions are




temporary suspension of transaction execution
forced writing of all updated database blocks in main memory
buffers to disk
flush the log file
resume transaction execution
Note: If the transactions are controlled in SQL, the rollback
operation can be made to an intermediate state which is
labeled (using ROLLBACK TO <label>)
Adding checkpoints to the log file

A [checkpoint, <label>] record is created each time a new
checkpoint is encountered

[commit,<transaction-id>] entries for the active subtransactions
are automatically written when the system writes to the
database the effect of write operations of a successful
transaction

In the case of a rollback to a given checkpoint within a
transaction


an entry [commit,<transaction-id>] is logged against this
subtransaction
In the case of a rollback of the global transaction to a given
checkpoint

no subtransactions in the path will be committed either
4. Database Recovery


Need for recovery from failure during transaction

for preventing the loss of data

for avoiding global inconsistency of the database

for analyzing the possible reasons for failure
Factors considered in database recovery

what is the nature of the failure?

when did the problem occur in the transaction?

what do we need to recover?
4.1 Categories of Transactions at Failure
t check
t fail
T1
T2
T3
T4
T5
T1 T2 T3 T4 T5 -
Can be ignored (committed before the previous checkpoint)
Must Redo completely
Must Undo completely and then Redo
Must Redo completely
Must Undo completely and then Redo
After checkpoint tcheck cannot assume any changes saved to disk
4.2 Types of Failure
If an error or hardware/software crash occurs during transaction
processing, the database may be inconsistent
 Catastrophic failure
 Restore a previous copy of the database from archival backup
 Apply transaction log

Perform an incremental dump logging each transaction
Non-catastrophic failure
 Reverse the changes that caused the inconsistency by undoing the
operations and possibly redoing legitimate changes which were
lost
 The entries kept in the system log are consulted during recovery.
 No need to use the complete archival copy of the database.


to reconstruct a more current state by redoing committed transaction
operations up to failure point
4.3 Recovery Strategy

Mirroring


Backup


keep two copies of the database maintained simultaneously
periodically dump the complete state of the database to some
form of tertiary storage
System Logging

keep track of all transaction operations affecting the values of
database items.

the log is kept on disk so that it is not affected by failures except
for disk and catastrophic failures.
Advanced Databases CG096
Lecture 4: Transaction Serialization and
Concurrency Control
Nick Rossiter [Emma-Jane Phillips-Tait]
Content
1
2
3
4
5
Concurrent Transactions and Parallel Execution of
Operations
Problems with Concurrency
Scheduling of Transaction Execution
Locking Techniques for Concurrency Control
Optimistic Strategy for Transaction Management
1. Concurrent Transactions

Transaction resources




Transactions consist of operations, which are executed in a
fixed sequence (serially)
Operations have starting point and end point (duration)
Operations manipulate data (parameters)
Sources of Concurrency during transaction execution


Operations from different transactions can overlap (parallel
execution)
Data can be visible by more then one transaction (shared data)
Example: Flight Reservation
Booking from branch office
seat
seats
seats
Authentication
Checking
2 seets free
seats
Booking
Printing
1 seet reserved Free seets ?
Booking over the Internet
Authentication
seats
seats
seats
Checking
2 seets free
Booking
Printing
2 seets reserved Free seets ?
Time
t0
t1
t2
t3
t4
1.1 Typical situations requiring
concurrency control

Exclusive access to an external device or shared service (e.g.,
managing printer queues)

Coordination of applications which process parallel data (e.g.
parallel DB servers)

Disabling or enabling execution of the client programs in a
specific moment (typically for database administration - e.g.
database backups, enforcing resource occupation, etc.)

Detection of transaction ends when managing multiple
sessions for connection to the database (client/server
architectures, Web access)
1.2 Transaction Properties and Transaction
Management


ACID properties as implemented by DBMS guarantee correct
behaviour for transactions only to certain extent
 operations are independent
 the effect of the operation execution does not change if
operations from other transactions mix with them
In other cases the application should incorporate an explicit
control mechanism for preserving the original logics of
transaction operations
 using DBMS utilities for programming the application (e.g.
Oracle DBMS_TRANSACTION package)
 using specialized transaction servers between the application
and DB (e.g. Microsoft MTS, Java JTS)
2. Problems with Concurrency (in absence of
locking)
• Lost Update problem - losing values due to
intervention of write operation from other overlapping
transactions
• Temporary Update problem - discarding previous
changes made by overlapping transaction after rollback
• Incorrect Summary problem - overwriting of certain
values used for calculation by write operations from
other transactions
2.1 Lost Update Problem
Time
Transaction A
Value
T0
Start A
6
T1
Read Value (6)
6
Start B
T2
Add 2 (6+2=8)
6
Read Value
(6)
T3
Write Value (8)
8
Add 3 (6+3=9)
T4
End A
9
Write Value (9)
9
End B
T5

What should the final Order Value be?

Which Update has been lost?
Transaction B
2.2 Temporary Update Problem
Time
Transaction A
Value
T0
Start A
6
T1
Read Value (6)
6
T2
Add 2 (8)
6
T3
Write Value (8)
8
Start B
T4
Failure: Rollback!
8
Read Value (8)
T5
Write Value (6)
6
Add 3 (8+3=11)
T6
End A
11
Write Value (11)
T5


11
What should the final Order Value be?
Where is the temporary update?
Transaction B
End B
2.3 Incorrect Summary Problem
Time
Values
Transaction B
T0
Read 1st Value (6)
6
3
T1
Add 2 (6+2=8)
6
3
T2
Write 1st Value (8)
8
3
T3
Read 2nd Value (3)
8
3
Read 1st Value (8)
Add 2 (3+2 = 5)
8
3
Read 2nd Value (3)
8
5
Total Sum = 11
T4
T5


Transaction A
Write
2nd
Value (5)
What should the total Order Value be?
Which order was accumulated before update, and which
after?
3. Scheduling of Transaction Execution



A schedule S of n transactions is a sequential ordering of
the operations of the n transactions.
 The transactions are interleaved
A schedule maintains the order of operations within the
individual transaction.
 For each transaction T if operation a is performed in
T before operation b, then operation a will be
performed before operation b also in S.
 The operations are in the same order as they were
before the transactions were interleaved
Two operations conflict if they belong to different
transactions AND access the same data item AND one of
them is a write.
T1
read x
write x
T2
read x
write x
S
read x
read x
write x
write x
3.1 Serial and Non-serial Schedules




A schedule S is serial if, for every transaction T participating
in the schedule, all of T's operations are executed
consecutively in the schedule; otherwise it is called non-serial.
Non-serial schedules mean that transactions are interleaved.
There are many possible orders of operations in alternative
schedules.
A schedule S consisting of n transactions is serialisable if it is
equivalent to some serial schedule of the same n transactions.
The results from serial schedules



always leave the database in a consistent state
never suffer from interference by one transaction with another
vary according to the order in which the transactions are performed
Example of Serial Schedules

Schedule A
T1:
read_item(X);
X:= X - N;
write_item(X);
read_item(Y);
Y:=Y + N;
write_item(Y);

T2:
Schedule B
T1:
read_item(X);
X:= X - N;
write_item(X);
read_item(X); read_item(Y);
Y:=Y + N;
X:= X + M;
write_item(X); write_item(Y);
T2:
read_item(X);
X:= X + M;
write_item(X);
Example of Non-serial Schedules


Schedule C
T1:
read_item(X);
X:= X - N;
T2:
read_item(X);
X:= X + M;
Schedule D
T1:
read_item(X);
X:= X - N;
write_item(X);
read_item(X);
X:= X + M;
write_item(X);
write_item(X);
read_item(Y);
write_item(X);
Y:=Y + N;
write_item(Y);
T2:
read_item(Y);
Y:=Y + N;
write_item(Y);
We have to figure out whether a schedule is equivalent to
a serial schedule, i.e. the reads and writes are in the right
order in the schedule. Do a precedence graph.
Precedence Graphs
T1:
read_item(X);
X:= X - N;
write_item(X);
read_item(Y);
Y:=Y + N;
write_item(Y);
T1:
read_item(X);
X:= X - N;
T1:
T2:
Schedule E
Conflict
serialisable
read_item(X);
X:= X + M;
write_item(X);
T2:
Schedule G
read_item(X);
X:= X + M;
write_item(X);
read_item(Y);
Not conflict
serialisable
write_item(X);
Y:=Y + N;
write_item(Y);
T2:
read_item(X);
X:= X + M;
write_item(X);
read_item(X);
X:= X - N;
write_item(X); Schedule F
Conflict
read_item(Y);
Y:=Y + N;
serialisable
write_item(Y);
T1:
T2:
Schedule H
read_item(X);
X:= X - N;
write_item(X);
read_item(X);
X:= X + M;
write_item(X);
Conflict
read_item(Y);
Y:=Y + N;
serialisable
write_item(Y);
3.2 Transaction Serialisability

The effect on a database of any number of transactions
executing in parallel must be the same as if they were
executed one after another (I-property guaranteed)


Syntactic (View) Serialisability


Equivalence:
 As long as each read operation of a transaction reads the result
of the same write operation in both schedules, the write
operations of each transaction must produce the same results
 The read operations are said to see the same view of data in
both schedules
 The final write operation on each data item is the same in both
schedules, so the database state should be the same at the end
of both schedules
View serialisation
 A schedule S is view serialisable if it is equivalent to a serial
schedule
 Testing for view serialisability is NP-complete: it is is highly
improbable that an efficient algorithm can be found
3.3 Methods for Transaction Serialisation

Timestamps




unique identifiers for each transaction
generated by the system
order transactions by their timestamps to ensure a particular
serialisability
used extensively in databases including mirroring and distributed
application
4. Locking Techniques


The concept of locking data items is one of the main
techniques for controlling the concurrent execution of
transactions.
A lock is a variable associated with a data item in the
database.


A lock describes the status of the data item with respect to
possible operations that can be applied to that item



Generally there is a lock for each data item in the database.
used for synchronising the access by concurrent transactions to the
database items.
A transaction locks an object before using it
When an object is locked by another transaction, the
requesting transaction must wait
4.1 Types of Locks



Binary locks have two possible states:
1. locked (lock_item (X) operation) and
2. unlocked (unlock (X) operation
Multiple-mode locks allow concurrent access to the same item by
several transactions. Three possible states:
1. read locked or shared locked (other transactions are allowed to
read the item)
2. write locked or exclusive locked (a single transaction
exclusively holds the lock on the item) and
3. unlocked.
Locks are held in a lock table.


upgrade lock: read lock to write lock
downgrade lock: write lock to read lock
4.2 Locking Granularity


A database item which can be locked could be
 a database record
 a field value of a database record
 a disk block
 the whole database
Trade-offs
 coarse granularity


the larger the data item size, the lower the degree of concurrency
fine granularity

the smaller the data item size, the more locks to be managed and
stored, and the more lock/unlock operations needed.
Record Locking



Every record has a lock.
The lock may have 3 states:
 Unlocked = U
 Read Locked = R, n
 Write Locked = W
Note: n is the number of transactions which have put
a read lock on the record.
Record Locking Protocol
• The lock must be checked, then set before the
record is accessed.
• Decision Table for Lock Management:
Lock status
Action
requested
Read
Write
U
R, n
W
Set lock to R,
one
lock acquired
Set lock to W.
lock acquired
Set lock to R,
n+1
locks acquired
Deny access.
must wait
Deny access,
must wait
Deny access,
must wait
Example: Prevention of Lost Update
Time
Transaction A
Value
Transaction B
T0
Start A
6
T1
Set Write Lock
6
T2
Read Value (6)
6(W)
Start A
T3
Add 2 (6+2=8)
6(W)
Request Write
T4
Write Value
6(W)
Wait
T5
Release Lock (8)
8(W)
Wait
T6
End B
8
Set Write Lock
8(W)
Read Value
T7
Value 8 appears to other tasks when lock is released
Example: Locking with Lost Update
T1:
T2:
read_lock(X)
read_item(X);
X:= X - 2;
unlock(X)
write_lock(X)
write_item(X);
unlock(X)
write_lock(Y)
read_item(Y);
Y:= Y + X;
write_item(Y);
unlock(Y)
X
Y
4
2
read_lock(X)
read_item(X);
X:= X + 3;
unlock(X)
4
7
2
write_lock(X)
write_item(X);
unlock(X)
8
7
10
10
4.3 Ensuring Serialisability: Two-Phase Locking



All locking operations (read_lock, write_lock) should precede
the first unlock operation in the transactions.
Two phases:
 expanding phase: new locks on items can be acquired but
none can be released
 shrinking phase: existing locks can be released but no new
ones can be acquired
The two phases are completely disjoint, no overlapping
 Record access occurs during or after the expanding phase but
must be complete before the shrinking phase starts.
Example: Prevention of Incorrect Summary
Time
Transaction A
Values
6(R,1)
3
T0
Request Write
record 1
6(R,1)
3(R,1)
T2
Wait
6(R,1)
3(R,1)
T3
Wait
6(R,1)
3(R,1)
T4
Wait
6(R,1)
3(R,1)
T5
Wait
6(R,1)
3(R,1)
Write Lock record 1
6
3
T1
T6
E
x
Read Lock record 1 p
a
n
Read Lock record 2
d
I
Read 1st Value (6)
n
g
Read 2nd Value (3)
S
h
r
Total Value (6+3= 9) i
n
k
Release Locks
i
n
g
Transaction B
4.4 Locking Problems: Deadlock
Time
Transaction A
Values
Transaction B
T0
Write Lock record 1
6
3
T1
Read 1st Value (6)
6(W)
3
T2
Request Write
record 2
6(W)
3(R,1)
Read 2nd Value (3)
T3
Wait
6(W)
3(R,1)
Request Read
record 1
T4
Wait
6(W)
3(R,1)
Wait

Read Lock record 2
Each Process is waiting for the other to release a lock!
Deadlock Prevention

Killing processes: Victim selection
 Explicit Timestamping of the operations
 Enforcing Timeouts of the transactions
 Detection of “Waiting for” loops

Diagrammatically
Waiting
for
Object 1
Process
1
Locked
by
Locked
by
Process
2
Object 2
Waiting
for
5. Optimistic Strategy for Concurrent
Transaction Management




No checking while the transaction is executing.
Check for conflicts after the transaction.
Checks are all made at once, so low transaction
execution overhead
Relies on little interference between transactions
 Updates are not applied until the end of transaction
 Updates are applied to local copies in transaction space
Phases in optimistic strategy
1. read phase: read from the database, but updates
are applied only to local copies
2. validation phase: check to ensure serialisability
will not be validated if the transaction updates
are actually applied to the database
3. write phase: if validation is successful,
transaction updates applied to database;
otherwise updates are discarded and
transaction is aborted and restarted.