文档章节

10gR2 New Feature: Asynchronous Commit

东皇巴顿
 东皇巴顿
发布于 2017/09/04 11:43
字数 1515
阅读 17
收藏 0

One of the primary tests for DBMS reliability is what's known as the ACID test. ACID-compliant systems are as close as you can get to guaranteed not to lose your data. Essentially, as long as your database files are intact, you are guaranteed that your data is consistent. This is not true for non-ACID compliant systems. Non-ACID-compliant systems are vulnerable to data inconsistency, and generally aren't taken seriously for any application where data integrity is important. Now, in 10gR2, Oracle offers us the option to breakits ACID compliance.

ACID stands for Atomicity, Consistency, Isolation and Durability. 
Atomicity, the first property, doesn't refer to the tiny particles that make up matter, but to the other meaning of the word "atomic": indivisible. Atomicity is a short-hand way of saying that when a transaction that changes the data occurs, either the whole transaction occurs, or none of the transaction occurs. In other words, each transaction is "atomic".

Atomicity is implemented in Oracle (and most other DBMSs) via commit-rollback. If I issue several update statements as part of a transaction, then commit that transaction, I am guaranteed that all of that transaction has gone through. Oracle does not return from a COMMIT statement until the redo log changes corresponding to that commit have been physically written to disk, not just to buffer. Similarly, if I issue a ROLLBACK, then none of the transaction goes through.

Durability, the final property in the ACID test, means that any changes made by committed transactions are persistent: they are permanent, and if a failure occurs, the information in the database will reflect all transactions committed before the failure. A durable DBMS must be atomic, but not all atomic DBMSs are durable.

In Oracle 10gR2, you can now choose to break this fourth property. Oracle's new asynchronous commit feature essentially allows you to trade durability for speed on a transaction-by-transaction basis. You can choose to break ACID compliance in this way by default, at the instance level; or you can choose to break it at the individual transaction level.

Asynchronous Commit

By default, Oracle's commits are durable. Oracle writes your changes to disk and doesn't return control of the session to you until it's done. The normal commit process is as follows:

  1. User begins transaction.
  2. While user is issuing DML, Oracle generates redo entries corresponding to the data changes. These redo entries are buffered in memory while the transaction is occurring.
  3. When the user issues a COMMIT, Oracle immediately writes this buffered redo to disk, along with redo for the commit. Ie, a disk I/O is forced.
  4. Oracle does not return from the commit until the redo has been completely written to disk (to the online redo log).

There are two important aspects to note here: the redo information is written to disk immediately, and the session waits for the process to complete before returning.

Oracle now lets you change both of these aspects. You can let the log writer write the redo information to disk in its own time, instead of immediately; and you can have the commit return to you before it's completed, instead of waiting.

Default COMMIT behavior New option
IMMEDIATE The redo information is written to disk immediately (forcing a disk I/O for each commit). BATCH Oracle buffers the redo information. The log writer will write this redo information to disk, but in its own time. Thus, several I/Os can be "batched".
WAIT Oracle does not return from the commit until the commit has completed successfully. NOWAIT Oracle doesn't wait for the commit to complete, but returns right away.

Syntax

You can change Oracle's commit behavior at the statement level by simply specifying one or both of the new options above with your commit. Use the new WRITE clause of the commit statement for this purpose. For example:

COMMIT WRITE BATCH NOWAIT ;

will commit your transaction without forcing a disk I/O, and will return control to you without waiting for the commit operation to complete.

The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

By default, if no WRITE clause is specified, a naked COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

COMMIT_WRITE initialization parameter

You can even change the default behaviour of COMMIT at the instance level, so that the default behaviour of a naked COMMIT statement is not COMMIT WRITE IMMEDIATE WAIT. A new initialization parameter, COMMIT_WRITE, lets you set default commit immediacy to IMMEDIATE or BATCH, and default commit wait to WAIT or NOWAIT:

COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'

Benefits

Compromising the "D" in your database's ACID compliance gets you one thing: speed. A normal commit forces a disk I/O and does not return until the commit is complete. You'll save some time if you batch your commits (not forcing the disk I/O) and don't wait until the commits actually complete. If you don't actually care if the data you're writing is permanent, but you do care that your writes be rapid, then you might find this feature useful.

It's worth noting that this new feature is not the only way to reduce the amount of time your application spends committing data. A properly designed application should normally batch its commits by design, ie. your application should not be continually committing small amounts of data. If your application is continually issuing small commits, you should look at your code to see if this design is really necessary, or if you might be able to batch your commits in the code. For example, you might elect to save web-entered data to the database at the end of several screens instead of at each screen. Or, if your commits are taking a long time because of complex indexes and triggers, you might consider saving entered data to an intermediate, raw-data table and batching writes from that table to the schema tables, rather than writing directly to the schema tables while the user waits on the commit.

Dangers

The obvious danger of asynchronous commits is that your DBMS is no longer ACID complient. Specifically, in using an asynchronous commit, you have traded durability for speed. An asynchronous commit returns before the data has actually been written to disk, so if the database crashes before some buffered redo is written to the logs, or a file I/O problem prevents the log writer from writing buffered redo to a non-multiplexed log, then the commit is lost.

There is also a danger which applies only to Real Applications Clusters. The COMMIT_WRITE initialization parameter is documented as being able to hold different values for different instances in a RAC. This could be useful if you have services defined on the cluster and have one service for which durability is not important. However, I would advise extreme caution, because if COMMIT_WRITE has different values for different instances in your cluster, load balancing or failover of your services could have unexpected and disastrous results.

Potential drawbacks

Besides the above dangers, there's one potentially serious drawback to this new feature. As far as I can determine, there is no initialization parameter to let the DBA disable asynchronous commit. Nor does there seem to a way to keep any given database user from executing an asynchronous commit.

This could be a serious data reliability issue for systems that have knowledgeable ad-hoc users modifying data directly. Any user can issue an asynchronous commit for their work, which means that work could potentially disappear in case of system failure -- a real problem if outside processes assume the work has gone through. Fortunately, this kind of setup, with ad-hoc users directly issuing SQL against the database as part of the business workflow, is vanishingly rare today.

New feature -- or not?

There's some evidence that PL/SQL has been using asynchronous commits in a limited way before 10gR2. href="http://www.oracledba.co.uk/tips/lgwr_dilemma.htm">This article by Connor McDonald, at oracledba.co.uk, provides a case study against a pre-10gR2 database in which a series of successive commits within a loop appear to be batched. What prevents this from 
breaking durability, suggests McDonald, seems to be that only the PL/SQL code block knows how many commits are supposed to have been executed at any given point -- as long as PL/SQL waits for the last commit to fully complete before the PL/SQL block returns, durability is, for all intents and purposes, preserved.

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

-- show commit parameter
show parameter commit;

-- oracle 11g
-- modify db instance level

alter system set commit_logging='batch'; 
alter system set commit_wait='nowait';

alter system set commit_logging='immediate'; 
alter system set commit_wait='wait';

-- oracle 10g
-- modify db instance level

alter system set commit_write='immediate,wait';
alter system set commit_write='batch,nowait';

-- modify session level

--在创建数据库连接时增加处理
alter session set commit_wait='nowait';
alter session set commit_logging='batch';

本文转载自:http://www.orafaq.com/node/93

共有 人打赏支持
东皇巴顿
粉丝 3
博文 62
码字总数 73311
作品 0
海淀
技术主管
Oracle CRS简介

Oracle CRS简介 从Oracle 10gR1 RAC 开始,Oracle推出了自身的集群软件,这个软件的名称叫做Oracle Cluster Ready Service(Oracle集群就绪服务),简称CRS。从Oracle 10gR2开始,包括最新的1...

技术小甜
2017/11/15
0
0
RethinkDB 2.0 发布,分布式数据库

RethinkDB 2.0 的候选版本已经发布了,可用于测试,本版本致力于简化创建过程。用户可以操作RethinkDB 来持续更新应用程序汇中的现场查询。同时还可以采用 RethinkDB 的分布式架构优势来扩展...

oschina
2015/04/15
1K
5
Quest for Better Replication in MySQL: Galera vs.

Quest for Better Replication in MySQL: Galera vs. Butterfly2018-01-011 阅读 DatabaseMySQL Original post: https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replicat......

Butterfly
01/01
0
0
TymeacDSE 2.6 发布

TymeacDSE 2.6 ChangeLog: Adds built-in functions for: Sort, Sum, Map/Reduce, and Filter. It adds a new client side feature TyArrays for accessing the built-in functions more e......

oschina
2012/10/05
1K
0
New Features in C# 3.0, 4.0 and 5.0 (英文差的免入)

What’s New in C# 3.0 Language Integrated Query(LINQ) - LINQ enables queries to be written in C# program to query both local collections (such as lists or XML documents) or remo......

master_haku
2013/07/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

AOSP Build System —— Soong

Soong Soong is the replacement for the old Android make-based build system. It replaces Android.mk files with Android.bp files, which are JSON-like simple declarative descriptio......

雪落青山
24分钟前
1
0
Unity C# lock关键字的坑

Unity 5.6 环境下的 lock关键字,在特定的多线程环境下会死锁 崩溃 其中一种情况: 异步socket 操作,由于内部是一个线程池回调的异步回调,操作同一个对象时 lock关键字会概率出现死锁 闪退...

梦想游戏人
35分钟前
1
0
redis-hash

哈希类型是指健值本身又是一个键值对结构 基本命令: hset key field value 设置值 hget(获取),hdel(删除),hlen(计算field个数),hmget(批量设置),hexists(是否存在),hkeys(获取所有的...

拐美人
53分钟前
2
0
简单的svm例子

数据来源:https://github.com/oumiga1314/Coursera-ML-AndrewNg-Notes/blob/master/code/ex6-SVM/data/ex6data1.mat import pandas as pd import numpy as np import scipy.io as sio impor......

南桥北木
57分钟前
1
0
android 关于View的一些整理

1、Button text的值为英文时,会自动转换成大写。如需取消,设置android:textAllCaps="false" 2、控件的可见性 可以在layout的配置文件中,配置android:visibility属性 调用setVisibility()...

西米小娅
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部