Chapter 10 Managing Data and Concurrency

Manage Data Using DML
row and table locking mechanisms: ensure data integrity
one or more DML statements grouped into transactions

Database Transactions
transactional integrity:undo segments and redo log files.
ACID where ACI require generation of undo, D require generation of redo
Atomicity: either all parts of transaction must complete or note of them complete
Consistency: changed values are not seen by the query when the query started
Isolation: incomplete transaction (uncommitted) must be invisible to the rest of the world, only seen by the session executing the DML statements
Durability:Once the transaction completed, impossible for the database to lose it, the changes never lost.

Executing SQL Statements
SELECT
INSERT
UPDATE
DELETE

Redo Generation: server process writes to the log buffer the change vectors that are going to be
applied both to the data blocks (table/index) and undo blocks.
(Change vectors to be applied to data and undo blocks are written to log buffer, the apply the change vector to database buffer cache)

The redo log stream includes all changes: those applied to data segments and to undo segments, for both committed and uncommitted transactions.

Having generated the redo, the update is carried out in the database buffer cache, both in block of table data (updated) with new version of the changed column, and in the block of undo segment (written) with old version of the changed column.

redo protect all the block changes including undo block.
undo segement: store old data, protect the change, to be used by rollback.

COMMIT: LGWR flush log buffer (with COMMIT record) to disk, redo log file. DBWn does absolutely nothing.

In orderly shutdown (clean, consistent state), checkpoint is issued.
PMON will rollback incomplete transactions for IMMEDIATE SHUTDOWN.
CKPT instructs DBWn to write ALL dirty blocks to datafiles.
LGWR also flushes any change vectors still in memory to redo log files.

In principle, database on disk is corrupted: datafiles storing uncommitted work, and be missing committed changes.

In the event of crash, the redo log file always has enough information to reconstructure the database to the consistent state.

DDL commands contain COMMIT implicitly.

Identify and Administer PL/SQL Objects
Stored PL/SQL in database dictionary.

PL/SQL Objects: Anything function can do, a procedure could do also.
Procedure: IN, OUT, IN-OUT argument, EXECUTE
Function: IN arguement, RETURN, for small simple operation
Package (Specification) groups procedures and functions together.
Package body contains actual procedures and functions code.
Trigger: cannot be packaged, cannot be invoked manually, fire once for each affected row or once per statement execution, can be before/after an event.
Type body

Event for trigger:
DML (INSERT,UPDATE,DELETE):before/after
DDL (CREATE,ALTER,DROP,TRUNCATE):before/after
Database operation: LOGOFF,SHUTDOWN (before), OTHERS (after)

Monitor and Resolving Locking Conflicts
access same data at the same time: serialize it! (queue up, take turns)

row locking: write must wait, read permitted.
prevent modification. till txn complete (commit/rollback)

exclusive lock for row (for DML), exclusive lock for table (for DDL)
shared lock for table with exclusive lock for row together to prevent DDL on the table.

Enqueue Mechanism: session requests are keep track in sequence and service in order.

Lock Contention: session cannot get lock, it will hang.

Deadlock: session will roll back the statement that caused the problem.