ITPub博客

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

Oracle also offers the option of enforcing transaction-level read consistency. When atransaction runs in serializable mode, all data accesses reflect the state of the databaseas of the time the transaction began. This means that the data seen by all querieswithin the same transaction is consistent with respect to a single point in time, exceptthat queries made by a serializable transaction do see changes made by the transactionitself. Transaction-level read consistency produces repeatable reads

The DRIVING_SITE hint forces query execution to be done at a different site than thatselected by Oracle. This hint can be used with either rule-based or cost-basedoptimization. Table is the name or alias for the table at which site the execution shouldtake place.SELECT /*+ DRIVING_SITE(DEPT) */ * FROM EMP, DEPT@RSITE WHERE EMP.DEPTNO = DEPT.DEPTNO;If this query is executed without the hint, rows from DEPT will be sent to the local siteand the join will be executed there. With the hint, the rows

Oracle automatically provides read consistency to a query so that all the data that thequery sees comes from a single point in time (statement-level read consistency).Oracle can also provide read consistency to all of the queries in a transaction(transaction-level read consistency).Oracle uses the information maintained in its rollback segments to provide theseconsistent views. The rollback segments contain the old values of data that have beenchanged by uncommitted or recently committed transac

In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. Resources include two general types of objects: ■ User objects, such as tables and rows (structures and data) ■ System objects not visible to users, such as shared data structures in the memory and data dictionary rows锁机制1. 锁是一种防止多

The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput. These isolation levels are defined in terms of three phenomena that must be prevented between concurrently executing transactions. The three preventable phenomena are: ■ Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet. ■ Nonrepeatable (fuzzy) reads: A transaction rereads data it h

In a single-user database, the user can modify data in the database without concern for other users modifying the same data at the same time. However, in a multiuser database, the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful and consistent results. Therefore, control of data concurrency and data consistency is vital in a multiuser database.■ Data concurrency means that many users can access data