1. Undo a. Undo is recorded in the Undo Segment b. Used for undo uncommited operations. c. When we do undo, we actually run the opposite SQLs. 2. Redo a. Redo = Redo Log i. Log as Cache in SGA ii. Log flused to Disk b. Used only …
Notes on ‘Expert Oracle’ — No.9.1: Redo&Undo — Overview Read More »
1. Redo log is made for a. Data Blocks b. Indexes c. Undo (That’s intersting) 2. The Redo info is first made in Cache, and it will be flushed into disk when a. 3 seconds passed b. The Redo Cache is 1/3 full or is over 1MB c. Commit …
Notes on ‘Expert Oracle’ — No.9.2: Redo&Undo — How Redo works? Read More »
1. What happens if a transaction is commited? a. The Redo Log is flushed b. Is the data blocks in SGA flushed also? No. We don’t need to do that with Redo Log. 2. Is the time of commitment a function of the volumn of manipulated data? No. There is not …
Notes on ‘Expert Oracle’ — No.9.3: Redo&Undo — Commit & Rollback Read More »
1. The large your Redo log is, the more time-consuming your SQL operation is. 2. Can we turn off Redo Log? a. Not Recommeded b. You can use "NOLOGGING" in SQLs to reduce much of the log. c. You can also use "NOLOGGING" for INDEX. This is actually acceptable. 3. How to …
Notes on ‘Expert Oracle’ — No.9.4: Redo&Undo — More about Redo Log Read More »
a.When data blocks are involved in a transaction, there are locking info created in the block. b.This info should be cleaned out when the transaction ends. c.They can be cleaned out immediately when the transaction ends, but not definitely. —- There are cleaned out on commitment, only if the blocks needed to be cleaned …
Notes on ‘Expert Oracle’ — No.9.5: Redo&Undo — Block Cleanout Read More »
1. Transaction: … 2. Transaction in Loop We prefer "Transaction after Loop" to "Transaction in Loop". a. "Transaction in Loop" => Too many seperated transactions => Not Atomic b. "Transaction in Loop" commits frequently => Undo Log more easily erased, since transaction already commited => When "restart" happens, you may see "Snap …
1. You have already known about "Read Consistency" — 如果数据在语句/事务开始时没变化，就返回这行数据；否则，从回滚段里读它的Snapshot. 这叫做"Consistent Read" 2. 与之相对的是 “Current Read” — 不去读回滚段，而是从当前数据文件里去读。 a. When does it happen? 在准备修改数据时进行Current Read b. 举例说明， Update t set y = 1 where x = 5 i.首先会执行 "select .. from t where x = 5"，这个过程使用的是"Consistent Read"，即找到回滚段里满足 x = 5的记录 …
Notes on ‘Expert Oracle’ — No.7.2: Concurrency–Write Consistency Read More »
1. Oracle除了用锁实现并发控制，还利用了“多版本机制”— 一条数据除了会记在表文件里，还会记在“回滚段”里。 2. 具体的并发控制策略 a.ANSI标准定义的并发控制级别（从低到高） i. Read Uncommited (可以读到脏数据) ii. Read Commited (不能读到脏数据) iii. Repeatable Read (在同一个事务里两次读某条数据，读到的值一定是一样的。一个典型的反面例子是“丢失更新”) iv. Serializable (不但可以重复读，而且还可以保证没有 幻读) b.Oracle支持哪些级别？ i.Read Uncommited — 在Oracle中想脏读都读不到！ ii. Read Commited — 支持，是Oracle的默认级别。Oralce不仅支持这个级别， 而且还有所增强:可以保证 “读一致性”（见下文） iii. Repeatable Read — 不直接支持。需自己编程实现。 iv. Serializable — 支持。但是由于未使用锁机制，因此 不能真正地实现“串行” …
Notes on ‘Expert Oracle’ — No.7.1: Concurrency–Concurrency with Multi-version Read More »
1. DML Locks a. TX Lock i. Preventing other transactions from updating the same row ii. Unlike other databases, it’s not costy for Oracle to have many Locks b. TM Lock: Preventing others from modifying the table structure while I am updating its data 2. DDL Locks a. Exclusive DDL …
Notes on ‘Expert Oracle’ — No.6.3: Locking — Locking Types Read More »
1. The problem: Lost Update Example: Increase Count Flow: a. I query the count. It is 5 b. You increase the count immediately. It is 6 now. c. I don’t know it is 6 now. I still assume it is 5. So I increase it by set it as 6. …
Notes on ‘Expert Oracle’ — No.6.1: Locking — Optimistic & Pessimistic Read More »