本文轉貼自 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
Writers block writers trying to update the same row.
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
SYNTAXSELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT];
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
SYNTAXLOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];
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-00054: resource busy and acquire with NOWAIT specified"Occurs when you attempt to lock NOWAIT and the resource is already locked.
"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
DRMT believes developers should design applications to avoid locking records explicitly.
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
Isolation Levels control the point-in-time used for the consistent image that each statement sees.
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
Read-only and Serializable mode could be useful for reporting tasks. Consult your DBA.
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
Two transactions updating the same row
Session 2's update waits until Session 1's commit release the row lock.
Time |
|
|
|
|
|
|
|
V
| Session 1 | Session 2 |
|
-------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--------------
|
|
SELECT FOR UPDATE
Session 2's update waits until Session 1's rollback releases the row lock.
Time |
|
|
|
|
|
|
|
V
| Session 1 | Session 2 |
|
-------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--------------
|
|
SELECT FOR UPDATE OF
Time |
|
|
|
|
|
|
|
V
| Session 1 | Session 2 |
|
-------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--------------
|
|
SELECT FOR UPDATE NOWAIT
Time |
|
|
|
|
|
|
|
V
| Session 1 | Session 2 |
|
-------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--------------
|
|
LOCK TABLE Time |
|
|
|
|
|
|
|
V
| Session 1 | Session 2 |
|
-------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--------------
|
