Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


Oracle SQL intro #9


Rodion Mironov

on 7 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #9

ACID properties;
Isolation levels;
UNDO and redo;
Transaction control. Agenda Introduction to Oracle SQL.
Transactions. Transactions and
transaction control language
(TCL) Transaction starts:
from the first DML statement;
from SET TRANSACTION statement;
from the SELECT FOR UPDATE statement;
from distributed SELECT statement. Transaction ends:
with COMMIT or ROLLBACK (without savepoint) statement;
before and after each DDL statement;
when session is ended. Transaction control language:
SET TRANSACTION. ` select * from regions;
insert into regions(region_id, region_name)
values (5, 'Australia');
select * from regions;
savepoint sp_australia;
insert into regions(region_id, region_name) values (6, 'Antarctica');
select * from regions;
/* business is going bad in Antarctica */
rollback to savepoint sp_australia;
select * from regions;
commit; - ends transaction and "saves" all changes. - "remembers" current state to be able to rollback here later. - ends transaction and reject all changes. - reject all changes made since savepoint. - changes isolation level, sets transaction read-only or read-write. Useful links http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=211821&pg=1 http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm#i6564 Transactions. Concepts. Когда начинается транзакция? Practice TODO Atomicity Consistency Isolation Durability All or nothing Transactions are not atomic in real life, changes are done one by one.
UNDO tablespace specific storage structure to hold past images of data;
is read when Oracle needs to rollback transaction (due to error or user ROLLBACK command). Good enough for single-user system. But in case of multi-user system... transaction 1 is in process and already modified part of data;
transaction 2 reads data modified by transaction 1.
In fact, transaction 2 would see part of transaction 1 changes. It breaks atomicity! Can be achieved with:
locking - nobody can read data modified by unfinished transaction.
versioning - reader can "reconstruct" data version before modification. Oracle approach. Atomic transaction should be "invisible or complete" (c) J. Lewis Oracle read consistency each statement can see only changes committed before statement start. Fact: for performance reasons data are modified in memory, not on disk. Committed transaction should
survive any failure. In-memory changes might be lost due to power outage, RDBMS failure etc. Solution:
redo log. Stores all changes in performance-friendly format.
all transaction's redo log entries are flushed to disk on COMMIT. In case of error we need to "undone" previously implemented changes; Concurrent transactions should be isolated.
Somehow. Problems
(aka read phenomena) Dirty read - read of uncommitted data. Already discussed.
Non-repeatable read:
Transaction 1 reads the row.
Transaction 2 modifies the row and commits.
Transaction 1 reads the row again. With different result.
Phantom read:
Transaction 1 reads rows according to some criteria (predicate);
Transaction 2 adds new row(s) that meets criteria and commits.
Transaction 1 repeats the same query and gets more rows. Solutions
(aka ANSI Isolation Levels) Oracle Isolation Levels We need data "before-changes-image". Multi-user system Transaction should "move" database from one consistent state to another
Full transcript