1 / 20

What we’ll be doing

What we’ll be doing. Back to Basics Read Consistency Write Consistency. 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 don’t get it)

kaiyo
Download Presentation

What we’ll be doing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. What we’ll be doing • Back to Basics • Read Consistency • Write Consistency

  2. 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 don’t get it) • Non blocking reads • Writes only block writes • However… unless you understand it, you’re probably doing some transactions wrong in your system! (DIY RI is almost always wrong)

  3. Query Continues, SCN=200 This block is OK Query Continues, SCN=200 This block is not OK Query Continues, SCN=200 This block is OK Query Continues, SCN=200 This block is OK Query Begins, SCN=200 This block is OK Update happens This block is modified Block 1 scn:123 Block 3 scn:100 Block 4 scn:187 Block 3 scn:210 Block 2 scn:55 Block 3 scn:<=200 What does it do…

  4. Why do you care • Suppose you were pulling changes…. T-1: update some data at 11:59, 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 T1’s work, you have all of the data right?

  5. 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

  6. 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

  7. Could it be part of your performance problem… • Long running queries against volatile tables • Queries against small “hot” table • Cr2.sql

  8. 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??

  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?? • 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)

  10. 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

  11. 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

  12. 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

  13. 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 SQL*Plus 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

  14. Write consistency And now let's clear all the pending transfers in SQL*Plus 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 2’s 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

  15. 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 Session 1 did this: 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

  16. 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

  17. Write consistency • What we’ll 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

  18. Q&A Questions and Answers

More Related