ITPub博客

Oracle provides data concurrency and integrity between transactions using its lockingmechanisms. Because the locking mechanisms of Oracle are tied closely to transactioncontrol, application designers need only define transactions properly, and Oracleautomatically manages locking.Keep in mind that Oracle locking is fully automatic and requires no user action.Implicit locking occurs for all SQL statements so that database users never need to lockany resource explicitly. Oracle’s default locking m

The database administrator uses the ALTER SYSTEM QUIESCE RESTRICTED statement to quiescethe database. Only users SYS and SYSTEM can issue the ALTER SYSTEM QUIESCE RESTRICTEDstatement. For all instances with the database open, issuing this statement has thefollowing effect:■ Oracle instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than SYS and SYSTEM) from becoming active. No user other than SYS and SYSTEM can start a new transaction, a new query,

You can put the system into quiesced state. The system is in quiesced state if there areno active sessions, other than SYS and SYSTEM. An active session is defined as asession that is currently inside a transaction, a query, a fetch or a PL/SQL procedure,or a session that is currently holding any shared resources (for example,enqueues--enqueues are shared memory structures that serialize access to databaseresources and are associated with a session or transaction). Database administratorsare the

Oracle’s serializable isolation is suitable for environments where there is a relativelylow chance that two concurrent transactions will modify the same rows and thelong-running transactions are primarily read only. It is most suitable for environmentswith large databases and short transactions that update only a few rows.Serializable isolation mode provides somewhat more consistency by protecting againstphantoms and nonrepeatable reads and can be important where a read/writetransaction runs a

Coding serializable transactions requires extra work by the application developer tocheck for the Cannot serialize access error and to undo and retry thetransaction. Similar extra coding is needed in other database management systems tomanage deadlocks. For adherence to corporate standards or for applications that arerun on multiple database management systems, it may be necessary to designtransactions for serializable mode. Transactions that check for serializability failuresand retry can be us

For many applications, read committed is the most appropriate isolation level. Readcommitted isolation can provide considerably more concurrency with a somewhatincreased risk of inconsistent results due to phantoms and non-repeatable reads forsome transactions.Many high-performance environments with high transaction arrival rates require morethroughput and faster response times than can be achieved with serializable isolation.Other environments that supports users with a very low transaction arr

Application designers and developers should choose an isolation level based onapplication performance and consistency needs as well as application codingrequirements.For environments with many concurrent users rapidly submitting transactions,designers must assess transaction performance requirements in terms of the expectedtransaction arrival rate and response time demands. Frequently, for high-performanceenvironments, the choice of isolation levels involves a trade-off between consistencyand co

In a distributed database environment, a given transaction updates data in multiplephysical databases protected by two-phase commit to ensure all nodes or nonecommit. In such an environment, all servers, whether Oracle or non-Oracle, thatparticipate in a serializable transaction are required to support serializable isolationmode.If a serializable transaction tries to update data in a database managed by a server thatdoes not support serializable transactions, the transaction receives an error. T

Because Oracle does not use read locks in either read-consistent or serializabletransactions, data read by one transaction can be overwritten by another. Transactionsthat perform database consistency checks at the application level cannot assume thatthe data they read will remain unchanged during the execution of the transaction eventhough such changes are not visible to the transaction. Database inconsistencies canresult unless such application-level consistency checks are coded with this in mi

Both read committed and serializable transactions use row-level locking, and both willwait if they try to change a row updated by an uncommitted concurrent transaction.The second transaction that tries to update a given row waits for the other transactionto commit or undo and release its lock. If that other transaction rolls back, the waitingtransaction, regardless of its isolation mode, can proceed to change the previouslylocked row as if the other transaction had not existed.However, if the ot

A useful way to view the read committed and serializable isolation levels in Oracle isto consider the following scenario: Assume you have a collection of database tables (orany set of data), a particular sequence of reads of rows in those tables, and the set oftransactions committed at any particular time. An operation (a query or a transaction)is transaction set consistent if all its reads return data written by the same set ofcommitted transactions. An operation is not transaction set consiste

Oracle gives the application developer a choice of two transaction isolation levels withdifferent characteristics. Both the read committed and serializable isolation levelsprovide a high degree of consistency and concurrency. Both levels provide thecontention-reducing benefits of Oracle’s read consistency multiversion concurrencycontrol model and exclusive row-level locking implementation and are designed forreal-world application deployment.已提交读取隔离与串行化隔离的比较1. 两者都提供了高

Serializable isolation is suitable for environments:■ With large databases and short transactions that update only a few rows■ Where the chance that two concurrent transactions will modify the same rows isrelatively low■ Where relatively long-running transactions are primarily read onlySerializable isolation permits concurrent transactions to make only those databasechanges they could have made if the transactions had been scheduled to run one afteranother. Specifically, Oracle permits a seri

The default isolation level for Oracle is read committed. This degree of isolation isappropriate for environments where few transactions are likely to conflict. Oraclecauses each query to run with respect to its own materialized view time, therebypermitting nonrepeatable reads and phantoms for multiple executions of a query, butproviding higher potential throughput. Read committed isolation is the appropriatelevel of isolation for environments where few transactions are likely to conflict.已提交

Read-only transactions see only those changes that werecommitted at the time the transaction began and do not allowINSERT, UPDATE, and DELETE statements.Oracle隔离级别 : 只读模式只读事务只能看到事务执行前就已经提交的数据，且事务中不能执行DML语句[@more@]

Serializable transactions see only those changes that werecommitted at the time the transaction began, plus those changesmade by the transaction itself through INSERT, UPDATE, andDELETE statements. Serializable transactions do not experiencenonrepeatable reads or phantoms.Oracle隔离级别 : 串行化1. 串行化隔离的事务只能看到事务开始时提交的数据 , 除了事务内部DML语句更改的数据2. 串行化隔离的事务不会出现不可重复读取或幻象读取的现象[@more@]

Application designers, application developers, and database administrators canchoose appropriate isolation levels for different transactions, depending on theapplication and workload. You can set the isolation level of a transaction by using oneof these statements at the beginning of a transaction:SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION READ ONLY;To save the networking and processing cost of beginning each transaction with a SET

This is the default transaction isolation level. Each query executedby a transaction sees only data that was committed before thequery (not the transaction) began. An Oracle query never readsdirty (uncommitted) data.Because Oracle does not prevent other transactions frommodifying the data read by a query, that data can be changed byother transactions between two executions of the query. Thus, atransaction that runs a given query twice can experience both nonrepeatable read andphantoms.Oracle隔离

Real Application Clusters (RAC) use a cache-to-cache block transfer mechanismknown as Cache Fusion to transfer read-consistent images of blocks from one instanceto another. RAC does this using high speed, low latency interconnects to satisfy remoterequests for data blocks.RAC下的一致性读1. RAC 系统采用缓存对缓存的数据块传输机制(Cache Fusion)在实例间传输满足读一致性的数据块镜像[@more@]

Oracle always enforces statement-level read consistency. This guarantees that all thedata returned by a single query comes from a single point in time—the time that thequery began. Therefore, a query never sees dirty data or any of the changes made bytransactions that commit during query execution. As query execution proceeds, onlydata committed before the query began is visible to the query. The query does not seechanges committed after statement execution begins.A consistent result set is pro