Transcript Transactions -in- Oracle -Evgenya-Kotzeva

Transaction control
and isolation levels
in Oracle
Evgeniya Kotzeva
Vereo Technologies
www.vereo.bg
academy.devbg.org
www.devbg.org
Contents
•
Transaction control
•
Data Concurrency and Consistency
in a Multiuser Environment
•
Locking
Database Transaction
A database transaction consists of one of
the following:
• DML statements which constitute one
consistent change to the data
• One DDL statement
• One DCL statement
Oracle Transaction Types
Type
Data manipulation
language (DML)
Data definition
language
Description
Consists of any number of DML
statements that the Oracle
server treats as a single entity
or a logical unit of work
Consists of only one DDL
statement
(DDL)
Data control language Consists of only one DCL
(DCL)
statement
Transaction boundaries
A transaction begins with
the first executable SQL
statement.
A transaction ends with
one of the following
events:
• A COMMIT or
ROLLBACK statement is
issued
• A DDL or DCL statement
executes (automatic
commit)
• The user exits iSQL*Plus
• The system crashes
Advantages of COMMIT
and ROLLBACK
With COMMIT and ROLLBACK statements, you
can:
• Ensure data consistency
• Preview data changes before making changes
permanent
• Group logically related operations
Controlling transaction
COMMIT transaction
Before COMMIT
After COMMIT
• generated rollback
segment records in buffers
in the SGA
• The internal transaction
table for the associated
rollback segment records
updated with SCN
• generated redo log entries
in the redo log buffer of the
SGA.
• The changes have been
made to the database
buffers of the SGA.
• LGWR writes SGA redo
log entries to the online
redo log file
• Oracle releases locks
• Oracle marks the
transaction complete.
ROLLBACK transaction
ROLLBACK
ROLLBACK to SAVEPOINT
• Oracle undoes all
transaction changes using
the undo tablespace or
rollback segments
• Oracle rolls back only the
statements run after the
savepoint.
• Oracle releases all the
transaction’s locks of data
• The transaction ends
• Oracle preserves the
specified savepoint, but all
savepoints that were
established after the
specified one are lost
• Oracle releases all table
and row locks acquired
since that savepoint
State of the Data
Before COMMIT or ROLLBACK
• The previous state of the data can be recovered.
• The current user can review the results of the
DML operations by using the SELECT statement.
• Other users can not view the results of the DML
statements by the current user.
• The affected rows are locked
• Other users cannot change the data within the
affected rows.
State of the Data after COMMIT
• Data changes are made permanent in the
database.
• The previous state of the data is permanently lost.
• All users can view the results.
• Locks on the affected rows are released; those
rows are available for other users to manipulate.
• All savepoints are erased.
Distributed database
• Distributed transaction is a transaction that
includes one or more statements that update data
on two or more distinct nodes of a distributed
database
• A two-phase commit mechanism guarantees the
data consistent in all nodes.
Autonomous transactions
• Autonomous transactions are independent
transactions that can be called from within another
transaction
• An autonomous transaction lets you leave the
context of the calling transaction
• You can call autonomous transactions from within
a PL/SQL block by using the pragma
AUTONOMOUS_TRANSACTION.
Data Concurrency and
Consistency
• Data concurrency means that many users can
access data at the same time.
• Data consistency means that each user sees a
consistent view of the data, including visible
changes made by the user’s own transactions and
transactions of other users.
The isolation models prevents
• Dirty reads
• Nonrepeatable (fuzzy) reads
• Phantom reads
Isolation levels (SQL92) controls
Isolation
Level
Dirty
Read
Nonrepeatable Phantom
Read
Read
Read
uncommitted
Y
Y
Y
Read committed
N
Y
Y
Repeatable read
N
N
Y
Serializable
N
N
N
Oracle isolation levels
Read committed
Each query executed by a transaction
sees only data that was committed
before the query began (Oracle default
isolation level)
Serializable
Serializable transactions see only those
changes that were committed at the
time the transaction began, plus its own
changes
Read-only
The transaction sees only those
changes that were committed at the
time the transaction began and do not
allow any DML statement
Multiversion Concurrency Control
• Statement-level read consistency
The data returned by a single query comes from a
single point in time — the time that the query
began
• Transaction-level read consistency
When a transaction executes in serializable mode,
all data accesses reflect the state of the database
as of the time the transaction began
Transactions and Read
Consistency
“Snapshot too old”
When commit or rollback has been executed,
the pre-images can be overwritten even if
they are needed to provide a readconsistent view to another query.
"Snapshot too old" simply means that preimages which the query needs to maintain a
read-consistent view have been overwritten.
Common recommendations
Common recommendations to reduce the possibility of
"snapshot too old" are:
• Keep transactions as fast as possible
• Increase the size/number of rollback segments
• Do not specify an OPTIMAL size for your rollback
segments.
• Increase the size of UNDO_RETENTION parameter
(amount of committed undo information to retain in the database)
• Avoid executing long-running queries when transactions
which update the table are also executing.
Set the Isolation Level
You can set the isolation level of a transaction by
using one of these statements at the beginning of
a transaction:
• SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;
• SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;
• SET TRANSACTION ISOLATION LEVEL
READ ONLY;
Serializable Transaction Failure
Modes of Locking
• Exclusive lock
The mode prevents the associates resource from
being shared
• Share lock
The mode allows the associated resource to be
shared, depending on the operations involved
Deadlock
Types of Locks
Lock
DML locks (data locks)
Description
DML locks protect data
For example, table locks lock
entire tables, rowlocks lock
selected rows.
DDL locks (dictionary locks) DDL locks protect the
structure of schema objects
Internal locks and latches
Internal locks and latches
protect internal database
structures such as datafiles
Table Locks
• RS: row share
• RX: row exclusive
• S: share
• SRX: share row
exclusive
• X: exclusive
Data Lock Escalation
• A transaction holds exclusive row locks for
all rows inserted, updated, or deleted within
the transaction. Because row locks are
acquired at the highest degree of
restrictiveness, no lock conversion is
required or performed.
• Oracle automatically converts a table lock of
lower restrictiveness to one of higher
restrictiveness as appropriate
MS SQL Isolation Levels
Microsoft SQL Server 2005
Oracle
SELECT ... WITH (UPDLOCK)
SELECT... FOR UPDATE
READ COMMITTED with
snapshots
READ COMMITTED
SNAPSHOT
SERIALIZABLE
SNAPSHOT
READ ONLY
READ UNCOMMITTED
No Equivalent
READ COMMITTED with locking
No Equivalent
REPEATABLE READ
No Equivalent
SERIALIZABLE
No Equivalent
DB2 Isolation Levels
Isolation levels
• Repeatable Read (RR)
• Read Stability (RS)
• Cursor Stability (CS)
• Uncommitted Read (UR)
Levels of locking
• Tablespace
• Table
• Row
Transactions in Oracle
Въпроси?