Oracle 9i & 10g programming art Reading Notes-Transactions

1,OracleConcurrency control is based on multiple versions.

2,OracleEnsure read consistency and never dirty read(That is, read the uncommitted data of other transactions.).OracleThe query result set must be the current result set at a certain time point:

I.Cursor(Cursor)The start time.

II.The time when the statement starts to be executed.OracleStatement-level read consistency is always guaranteed.

III.Query the start time of the transaction. When the transaction isolation level isSerializableOrRead Only.

3,OracleIs not blocked by writing,OracleNo read share lock. AndOracleNon-blocking reading of, not at the cost of dirty reading:OracleNever dirty read(The2Entries).

4,OracleProvidedRead committed,SerializableAndRead OnlyThree transaction isolation levels.

I. Oracle not required Read uncommited level, Oracle dirty reads are not allowed. Because Oracle you can get the advantages of dirty reads without dirty reads (that is, no blocking reads ).

II. Read committed is the default isolation level for Oracle and is also the most common isolation level. SET transaction isolation level read committed;

III.OracleNot providedRead repeatableIsolation level.

1.OracleRead consistency and concurrency are ensured without using the shared read lock.

2. Oracle in non serializable and Read Only at the isolation level, no loss update is guaranteed. Lock is required. ( NO. 5 entries )

IV. Oracle Implementation serializable isolation level extends the read consistency obtained at the statement level to the transaction level. Oracle adopts an optimistic method to implement serializable isolation level, which considers that the data to be updated in this transaction will not be updated by other firms. If updated, Oracle generates ORA-08117 error.

V.Read OnlyThe isolation level is read-only.SerializableIsolation level. Used to support REPORT query requirements. InSerializableAndRead OnlyAt the isolation level, ifUndoIf the information already existsORA-1555Error.

5,OracleFor multi-version read consistency, locking is required to solve the sequential read problem.

I. pessimistic locking: only used in stateful or connected environments. select... For update [Nowait] . If Nowait is specified, if another transaction is updating or has already updated this row, an ORA-00054 error occurs. If you do not specify Nowait , it will keep waiting.

II.Optimistic Locking:

1.Optimistic Locking. If you find that the data has been modified, you must take measures based on your actual business needs:

A)Get new value and start again.

B)Update conflicts are resolved based on business rules, and two updated values are merged.

2.Method To implement Optimistic Locking: When an update is made, a condition is added except the primary key as the condition. Determines the number of updated rows. If yes0The data has changed.

A)Save the old values of all fields as the update condition. Note:NullValue judgment. The simplest method is to add columns.

B)Add a version column.1. Applicable to non-10g.

C)Add a timestamp Column(Timestamp)Is the last update time. If the timestamp has no business significance, we recommend that you use stored procedures to encapsulate updates. Applicable to non-10gAnd get the additional benefits of the last update time.

D)Use the old value of all field checksum. You do not need to add extra columns, but the performance is low when you calculate large fields.

E)UseOracle 10gNewly providedOra_rowscn. To avoid false alarmsRowdependenciesRecreate the database table. No additional columns, lightweight.

6,OralceMulti-version read consistency may cause a large number of small "Hot tables"Io. The method is to reduce the transaction execution time. The longer the running time, the moreIoTo readUndoThis is a vicious circle.

7,Write blocking:

I. insert (insert) a row with the same primary key or column values with unique constraints. Use sequence (sequence) to generate a primary key, avoiding half of the problem.

II.IfUpdateOrDeleteBlocking occurs, which meansProgramProblems may be lostBug.

8,Write consistency: To ensure write consistency,OracleUpdates may be rolled back and restarted. This causesBeforeThe trigger is executed twice. The method is notBeforeUse autonomous transactions in triggers and execute non-transactional statements.

9,Deadlock:OracleDeadlock rarely occurs. A deadlock is detected.ORA-00060Error.

10,OracleCan be setDeferrable. In this way, you can set the constraint to the delay state.(Set constraintConstraint nameDeferred/immediate)To allow temporary violation of constraints during the transaction process. For example, cascade update of Foreign keys.

11,InOracle. The transaction size depends on your needs. Locking, blocking, and other issues are not the key to determining the transaction size. Data integrity is the foundation for determining the transaction size.

12,RollbackWill do a lot of work, andRollbackCompared,CommitFew tasks are completed. You do not want to roll back unless you have. Common sense: Since you don't wantCommitAnd how hard it is to do all these jobs.