低調

本文轉貼自 http://www.wisc.edu/drmt/oratips/sess004.html

Oracle Tips Session #4
2/11/98
Dave Ruhde

Table of Contents

• Terms

• Review Read Consistency/Concurrency in Oracle

• Row Locks

• Select For Update

• Table Locks

• Lock Failures

• Application Usage of Locking

• Isolation Levels

• Examples

Terms

Concurrency

How well can multiple sessions access the same data simultaneously

Consistency

How consistent is the view of the data between and within multiple sessions, transactions or statements

Statement

A SQL command that reads/changes data in the database. Changes made may or may not be committed (made permanent).
ex. SELECT, SELECT...FOR UPDATE, INSERT, UPDATE, DELETE

Session

A connection (as a single Oracle database user) to the database by an external program. The program that opens the Oracle session may be purely interactive--SQL statements are explicitly submitted by a person, or the program could be a developed application program using any number of languages

A database user may have mulitple concurrent sessions. All sessions, whether by multiple users or the same user, are independent.

Transaction

All changes to the database are made through transactions. A transaction consists of any number of SQL statements.

A transaction ends with a commit or a rollback. If the transaction is committed, ALL CHANGES by SQL statements in the transaction are saved. If the transaction is rolled back, ALL CHANGES by SQL statements in the transaction are undone.

A new transaction begins when a session is opened and after each rollback or commit.

Commit

Makes permanent all changes made in a transaction. Changes are not made part of the database until the transaction is commited. A commit is made when:

• a 'COMMIT' statement is issued
• the application program ends and disconnects its Oracle session normally.

Rollback

Undo any uncommitted changes made by a transaction. The transaction is ended. A rollback occurs when:

• a 'ROLLBACK' statement is issued
• Oracle terminates a session because
  • a application program or database exception is not handled and the application program terminates abnormally
  • a hardware or system failure causes the application program to terminate
  • a network failure causes the network connection between the connecting program and Oracle to be interrupted.
  • Oracle fails

Review Read consistency/concurrency

Consistency

All Oracle SQL statements ALWAYS work with a image of data that is consistent to a single point-in-time. The consistent image consists of all changes and only changes COMMITTED before the point-in-time of the image.

The consistent image nevers shows changes by other sessions that have not been committed to the database. Oracle never performs a "dirty read"

SQL statements do see changes, not yet committed, made by previous statements within the transaction the statement is part of.

By default, consistency is at the statement level not the transaction level. SQL statements return data using a consistent image based on the point-in-time the STATEMENT began, not when the transaction began. Each statement in a transaction works with its own consistent image.
Session 1, Example 1
Session 1, Example 2
Session 1, Example 3

Concurrency

Readers don't block readers.

Readers don't block writers.
Note: SELECT ... FOR UDPATE behaves as a writer

Writers don't block readers.

Row Locks

SELECT..FOR UPDATE, UPDATE, DELETE and INSERT statements always lock the row(s) that the statement is modifying.

Only one transaction can lock a given row at a time.

Other transactions can not update or lock a row that is locked.

Locks are NOT released when a statement completes. Locks are released when the transaction is committed or rolled-back.

Example 1

SELECT...FOR UPDATE

Locks row(s) from all table(s) that are selected by the query.

The row locks are released when the transaction that contains the SELECT...FOR UPDATE is committed or rolled-back.

OF cols: Lock only rows from tables which have a column listed in the OF clause. Rows are not locked in tables that don't have a column appearing in the OF clause. If no OF clause is listed, rows from all tables are locked.

NOWAIT: If NOWAIT is NOT specified and a row to be locked is locked by another transaction, SELECT...FOR UPDATE will wait indefinitely until the lock is released. If NOWAIT is specified and a row to be selected is locked by another transaction, the SELECT...FOR UPDATE will return immediately with a "ORA-00054: resource busy and acquire with NOWAIT specified" error.

Example 2
Example 3 - FOR UPDATE OF
Example 4 - NOWAIT

TABLE LOCKS

Successful completion of this statement means no other transaction can update or lock a row in the locked table(s) or lock the locked table(s) until the lock are released.

The table locks are released when the transaction that contains the LOCK TABLE is committed or rolled-back.

NOWAIT: If NOWAIT is NOT specified, the LOCK TABLE waits indefinitely until all existing row or table locks on the table(s) to be locked are released. If NOWAIT is specified, LOCK TABLE returns immediately with a "ORA-00054: resource busy and acquire with NOWAIT specified" error.

Table locks do not block readers.

Example 5

Lock Failures

"ORA-00060 deadlock detected while waiting for resource"
Transaction 1 locks row A, then Transaction 2 locks row B.

Then Transaction 1 tries to lock Row B and transaction 2 tries to lock row A.

Each transaction is waiting on the other.

Oracle automatically detects deadlocks. When a deadlock is detected the last statement fails with a ORA-00060.

Application Usage of Locking

Simple techniques like the following will not hold records locked and will likely meet true operational needs.

SELECT * INTO editrec FROM emp WHERE empno=:empnum
/* Record is not locked */
 User Edits row....
UPDATE emp .... WHERE empno = :empnum

Isolation Levels

Read Committed (Default)
Statements see changes as of the point-in-time of the start of the statement.

Read Only
Statements see changes committed as of the point-in-time of the start of the transaction. No updates allowed in transaction.

Serializable
Statements see changes committed as of the point-in-time of the start of the transaction. Updates are allowed, but:

A row that has been changed and committed by another transaction after the serialized transaction began will cause the serialized transaction to fail with a ORA-08177: can't serialize access for this transaction" error.

Serializable mode can only be used after consultation with DBA. Tables must be specially initiated to participate in a serializable transaction.

Usage

To set for a single transaction, first statement in transaction is:

SET TRANSACTION READ ONLY
SET TRANSACTION ISOLATION LEVEL READ COMMITTED (default)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

To set for all subsequent transactions within session:

ALTER SESSION ISOLATION LEVEL READ COMMITTED
ALTER SESSION ISOLATION LEVEL SERIALIZABLE

EXAMPLES

Example 1

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
update cust set color='blue';






-----------COMMIT--------------

-------BEGIN TRANSACTION-------
select color from cust
where id=500;
 color
 -----
 red


update cust set color=green
where id=500;

(Waiting...)

1 row updated.


select color from cust
where id=500;
 color
 -----
 green
-----------COMMIT--------------

Example 2

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select color from cust where
id=500 for update;
 color
 -----
 red



update cust set color='blue'
where id=500;



----------ROLLBACK--------------

-------BEGIN TRANSACTION-------
select color from cust
where id=500;
 color
 -----
 red


update cust set color=green
where id=500;

(Waiting...)



(Still waiting...)



1 row updated.

-----------COMMIT--------------

Example 3

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select c.color, t.typedesc
from cust c, custtype t where
cust.id=500 and c.type=t.type
for update nowait;

 color  typedesc
 -----  --------
 red    CASH ONLY

ROLLBACK;


select c.color, t.typedesc
from cust c, custtype t where
c.id=500 and c.type=t.type
for update of c.id nowait;

 color  typedesc
 -----  --------
 red    CASH ONLY
----------COMMIT--------------

-------BEGIN TRANSACTION-------

update custtype
set status='VALID';




(Waiting...)


100 rows updated.
-----------COMMIT--------------



update custtype
set status='INVALID';



100 rows updated.

-----------COMMIT--------------

Example 4

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select color from cust
where id=500 for update nowait;
 color
 -----
 red

update cust set color='blue'
where id=500;

1 row updated.
-----------COMMIT--------------

-------BEGIN TRANSACTION-------

select color from cust
where id=500 for update nowait;


ORA-00054





select color from cust
where id=500 for update nowait;

update cust set color='blue'
where id=500;

1 row updated.

-----------COMMIT--------------

Example 5

  |
 |
 |
 |
 |
 |
 |
 |
 V

-------BEGIN TRANSACTION-------
select color from cust
where id=500 for update nowait;
 color
 -----
 red
-----------ROLLBACK--------------



-------BEGIN TRANSACTION-------
select color from cust
where id=500 for update nowait;

ORA-00054
-----------COMMIT--------------

-------BEGIN TRANSACTION-------

LOCK TABLE cust in
exclusive mode nowait;

ORA-00054



LOCK TABLE cust in
exclusive mode nowait;

update cust set color='blue'

100 row updated.

-----------COMMIT--------------