Please enable JavaScript.
Coggle requires JavaScript to display documents.
Chapter 10. Managing Data and Concurrency (Manage Data Using DML (Database…
Chapter 10. Managing Data and Concurrency
Manage Data Using DML
Database Transactions
Atomicity
Either all
parts of transaction must complete,
or none
of them complete
Guarantee through use of
undo segments
Consistency
Result
of a query must be
consistent with the state
of the database at the time the query started
Guarantee through use of
undo segments
Isolation
Incomplete
(Uncommitted) transaction must be
invisible
to the rest of the world
Guarantee through use of
undo segments
Durable
Once a
transaction completes
, it must be
impossible
for the database to
lose
it
Guarantee through use of
log files
Online redo log files
Archive redo log files
Executing SQL Statements
Executing a SELECT Statement
Process
CHECK blocks contain
required data
are already in
database buffer cache
If exist, PROCEED
immediately
It not exist, LOCATE them on disk, COPY them into database buffer cache
Process is CARRIED OUT in the
PGA
of the session
Guarantee the ACID
Encounter the block has been
changed
AFTER the query
started
ROLLBACK the change (for current query only) by
undo segment
Data needed for rollback NOT in the undo segments => get the "
snapshot too old
" error
Executing a UPDATE Statement
Work on DATA blocks and UNDO blocks
Process
Required DATA block and UNDO block must be
found in the database buffer cache
(or copy)
LOCK any
rows
and associated
index keys
to be
affected
by the operation
GENERATE redo logs
WRITE to the
log buffer
the
change vectors
that are
going to be applied
to the data blocks
APPLIED both to
table block changes
(the new value) and to
undo block changes
(the old value)
If PART of an index key,
changes
to be applied to the
index
are written to the
log buffer
+ a
change
to be applied to an
undo block
Until the update is committed, all queries from
other sessions
addressing the
changed row
will be REDIRECTED to the
undo data
Executing INSERT and DELETE Statements
Process is same fashion as an UPDATE
Difference between INSERT and DELETE is
amount
of undo generated
INSERT: rowid
DELETE: whole row
Transaction Control
Executing a ROLLBACK
If anything
goes wrong
, ROLLBACK is carried out
automatically
by
background processes
Manual
rollback
Use
undo data
to
construct and execute
another statement that will
reverse
the effect of the first statement
UPDATE: Construct another
UPDATE
using the
undo block
to set back to the
original
values
INSERT: Construct
DELETE
with
rowid
from
undo block
DELETE: Construct
INSERT
from
data
in the
undo block
Executing a COMMIT
LGWR
flushes the
log buffer to disk
.
DBWn
does absolutely
nothing
FLAG the transaction as
complete
DDL and Transaction Control
CANNOT
rollback
a DDL statement
A transaction against some
data dictionary tables
The So-Called "Autocommit"
There is a perfectly normal COMMIT
included
in the source code that implements the DDL command.
SQL *Plus DML follow by an EXIT
Windows terminal
COMMIT
MS WIndows
ROLLBACK
SET AUTOCOMMIT ON:
append
a COMMIT to
every
DML statement
Identify and Administer PL/SQL Objects
SQL and Procedural Languages
SQL is set-oriented language - manipulate
rows at once
Procedural Languages can manipulate
individual rows
+
flow control structures
Stored and Anonymous PL/SQL
Definition
Stored PL/SQL
Stored within the data dictionary as named PL/SQL object
It is COMPILED: Check
syntactical
errors and pick up errors relating the
data objects
and the
code addresses
Anonymous PL/SQL
Compiled
dynamically
=> impact on
performance
and raise
unexpected errors
PL/SQL Objects
PROCEDUREs and FUNCTIONs
Procedures
Used to
divide code into modules
and contain
long and complex processes
Arguments
IN-arguments
OUT-arguments
IN-OUT arguments
CALL from within a
PL/SQL block
or use the
interactive
EXECUTE command
Functions
SIMILAR in concept to a procedure BUT
not
have OUT-arguments
Cannot
be invoked with EXECUTE
Return a single value by RETURN statement
Used for relatively simple operations: small code blocks
Packages (PACKAGEs and PACKAGE BODYs)
Group related
procedures
and
functions
Have 2 parts
Specification - PACKAGE
Body - PACKAGE BODY
Invoke a package procedure: <Package_name>.<Procedure_name>
TRIGGERs
Used for
auditing
user actions +
implementing
security
Execution is completely
beyond
the control of the user who caused the triggering event
Run
automatically
, when a particular action is carried out
Commonly used triggering events
DML
(INSERT - UPDATE - DELETE) -
Before
and/or
after
DDL
(CREATE - ALTER - DROP - TRUNCATE) -
Before
and/or
after
Database operations
SERVERERROR - After
LOGON - After
LOGOFF - Before
STARTUP - After
SHUTDOWN - Before
SUSPEND - After
TYPE BODYs (out of scope)
Monitor and Resolve Locking Conflicts
Shared and Exclusive Locks
Exclusive lock
The
first session
to request the
lock on the row
for
update
,
any other session
must
wait
Read
access is
permitted
Shared lock
Take on
whole tables
Many
sessions
can have a shared lock on the
same table
To execute DML on rows, a session already has a
exclusive lock
on the
rows
and
shared locks
on the
tables
containing the rows