Jerry Held

1
(No Transcript)
2
What well be doing

• Back to Basics
• Read Consistency
• Write Consistency

3
Multiversioning -- Just talk about it for a bit

• In my opinion the fundamental difference between
  Oracle and most of the rest
• It can be the best feature
• It can be the worst feature (if you dont get it)
• Non blocking reads
• Writes only block writes
• However unless you understand it, youre
  probably doing some transactions wrong in your
  system! (DIY RI is almost always wrong)

4
What does it do
5
Why do you care

• Suppose you were pulling changes.

T-1 update some data at 1159, do not commit,
time recorded though
T0 12 noon, start initial pull, remember this
time (for refreshing later).
T.5 commit the T-1 transaction. It was not
visible at T0, it is at T1
T1 Later on, go pull everything updated since
12 noon
T1.5 But we will not see the T-1 update!!
T2 After committing T1s work, you have all of
the data right?
6
Why do you care

• I am trying to enforce the business rule that
• if an employee has one or more addresses,
• then one and at least one address must be
  "preferred", that is, preferred 'Y'.
• All other addresses for the employee must have
  preferred 'N'.
• An address does not have to exist for an employee
  (but if one does, at least/most one must be
  preferred)
• Seems simple enough, but cr1.sql

7
Why do you care

• It is either virtually impossible or so
  inefficient to enforce integrity
• ACROSS objects
• ACROSS rows in an object
• By yourself
• Without locking tables or large sets of rows

8
Could it be part of your performance problem

• Long running queries against volatile tables
• Queries against small hot table
• Cr2.sql

9
Write consistency

• So, what happens when.
• You start updating lots of rows using a full scan
• You hit a locked row near the end (you block)
• The blocker commits
• What happens there??

10
Write consistency

• So, what happens when.
• You start updating lots of rows using a full scan
• You hit a locked row near the end (you block)
• The blocker commits
• What happens there??
• And what happens if some other transaction comes
  along and re-locks that row?
• Cr3.sql
• (remember to go forward a slide to recap
  observations)

11
Write consistency

• Observations
• We updated row 5,000 and kept it locked
• Started bulk update it made 4,999 entries in
  the audit table and then blocked. Updated ids
  1..4,999
• We committed the change this caused the bulk
  update to rollback.
• While it was busy rolling back, we locked row
  10,000 in the other session
• The bulk update blocked much faster this time
  it was in select for update mode now. Did not
  really do the updates, just getting locks
• It got blocked again (but had rows 1..9,999 all
  to itself)
• We committed the blocking session
• Bulk update finally went back and updated all of
  the rows.
• End up with 4,999 2 10,000 trigger fires

12
Write consistency

• Real question (from the groups and asktom)

Setup create table accounts (
acc int primary key, amount int,
pending int, cleared date )
Initially the table contains the following
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -2 2
0 2 3 0
0 26-NOV-03
13
Write consistency

• Implied Business Rules (that no one is enforcing)
• Sum(pending) where cleared is null 0
• Pending 0 when cleared is NOT NULL

Initially
the table contains the following ACC
AMOUNT PENDING CLEARED ----------
---------- ---------- --------- 1
10 -2 2 0
2 3 0 0
26-NOV-03
14
Write consistency
So, there is a committed database state with a
pending funds transfer of 2 dollars from acc 1 to
acc 2. Let's submit another transfer of 1 dollar
from acc 1 to acc 3 but do not commit it yet in
SQLPlus Session 1 update accounts set
pending pending - 1, cleared null where acc
1 update accounts set pending
pending 1, cleared null where acc 3
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -3 2
0 2 3 0
1
Initially the table contains the following
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -2 2
0 2 3 0
0 26-NOV-03
15
Write consistency
And now let's clear all the pending transfers in
SQLPlus Session 2 in a single-statement
read-committed transaction update accounts
set amount amount pending, pending 0,
cleared sysdate where cleared is null
Session 2 naturally blocks immediately on row
1 But Session 2s SCN for reading was fixed at
this point in time, the time the update started.
It reads the table as of the time the update
began Session 2 sees this view of the data
two rows where cleared is null
Initially the table contains the following
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -2 2
0 2 3 0
0 26-NOV-03
16
Write consistency
update accounts set amount amount
pending, pending 0, cleared sysdate
where cleared is null Now commit the
transaction in session 1. Session 2 readily
unblocks and the answer is ACC
AMOUNT PENDING CLEARED ----------
---------- ---------- --------- 1
7 0 26-NOV-03 2
2 0 26-NOV-03 3
0 1
Initially the table contains the following
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -2 2
0 2 3 0
0 26-NOV-03
Session 1 did this ACC AMOUNT
PENDING CLEARED ---------- ---------- ----------
--------- 1 10 -3
2 0 2 3
0 1
17
Write consistency
Here we go - the results produced by a
single-statement transaction read committed
transaction in session 2, are inconsistent -- the
second funds transfer has not completed in full.
Session 2 should have produced the following
instead ACC AMOUNT PENDING
CLEARED ---------- ---------- ----------
--------- 1 7 0
26-NOV-03 2 2 0
26-NOV-03 3 1 0
26-NOV-03 And not this ACC
AMOUNT PENDING CLEARED ----------
---------- ---------- --------- 1
7 0 26-NOV-03 2
2 0 26-NOV-03 3
0 1
18
Write consistency

• What well discover is the set of rows to be
  updated (as defined by the predicate) is defined
  99.99 by the predicate.
• The rows to be modified were identified as of
  the update submission time in this case (only
  the rows that had CLEARED is NULL were updated)
• That the observer observing can muck things up
  became readily apparent! (triggers are taken into
  consideration)
• Cr4.sql it works as he expected it, why.
• Cr5.sql it works as he observed, why..
• If a trigger references old.pending (but not
  new). Cr6.sql toggle new/old
• http//asktom.oracle.com/tkyte/wc.html

19
QA
Questions
and
Answers
20
(No Transcript)