实习日志第17天---学习笔记---第十章：data concurrency and consistency

Data Concurrency and Consistency

Introduction to Data Concurrency and Consistency

a multiuser database must provide the following:

Data concurrency

Data consistency

Oracle Database maintains data consistency by using a multiversion consistency model and various types of locks and transactions.

       different versions of data blocks can exist simultaneously, transactions can read the version of data committed at the point in time required by a query and return results that are consistent to a single point in time.

Multiversion Read Consistency

multiversion read consistency: Read-consistent queries

Nonblocking queries

Statement-Level Read Consistency

Transaction-Level Read Consistency

Read Consistency and Undo Segments   SCN

Locking Mechanisms

ANSI/ISO Transaction Isolation Levels

defines four levels of transaction isolation

 

Overview of Oracle Database Transaction Isolation Levels

Oracle提供了read committed和serializable两种SQ92标准中的隔离级别，和非SQ标准的read-only

可参考http://www.blogjava.net/fhtdy2004/archive/2009/08/01/292542.html 及concept里的例子

SQL> set transaction isolation level read committed/serializable;

SQL> set transaction read-only

Read Committed Isolation Level

Serializable Isolation Level    ORA-08177：can't serialize access for this transaction

Read-Only Isolation Level

snapshot too old error.  Setting an undo retention period, which is the minimum amount of time that the database attempts to retain old undo before overwriting it, appropriately avoids this problem.

 

Overview of the Oracle Database Locking Mechanism

Summary of Locking Behavior

In general, the database uses two types of locks: exclusive locks and share locks

·A row is locked only when modified by a writer.

·A writer of a row blocks a concurrent writer of the same row.

·A reader never blocks a writer.   The only exception is a SELECT ... FOR UPDATE statement

·A writer never blocks a reader.

Use of Locks

Lock Conversion and Escalation

Lock Duration

Locks and Deadlocks

A deadlock is a situation in which two or more users are waiting for data locked by each other.

 

Overview of Automatic Locks

DML Locks

Row Locks (TX)

Table Locks (TM)

Row Locks (TX)

A transaction

acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement.

Storage of Row Locks: Oracle Database stores lock information in the data block that contains the locked row

Each row modified by this transaction points to a copy of the transaction ID stored in the block header

Table Locks (TM)

transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24005010/viewspace-687721/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24005010/viewspace-687721/