文档章节

10gR2 New Feature: Asynchronous Commit

东皇巴顿
 东皇巴顿
发布于 2017/09/04 11:43
字数 1479
阅读 16
收藏 0
点赞 0
评论 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
博文 67
码字总数 75265
作品 0
海淀
技术主管
Oracle CRS简介

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

技术小甜
2017/11/15
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
30 分钟 git 命令入门到放弃

原文出处:tutorialzine 译文出处:w3ctrain 这是一篇给像我这样的新手或者是熟悉图形工具的老鸟看的。仅作为快速入门的教程。 git 现在的火爆程度非同一般,它被广泛地用在大型开源项目,团...

tutorialzine
2016/06/27
0
0
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
RethinkDB 2.0 发布,分布式数据库

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

oschina
2015/04/15
1K
5
Servlet 3.0 正式草案发布

In my December 2008 blog, I strongly criticised the Servlet 3.0 JSR-315 process and the resulting Public Review Draft, describing it as a: "poor document and the product of a di......

红薯
2009/05/22
2.2K
1
Git 指令收集

git默认只在branch工作,如果想要在其他的版本上做些实验性的功能开发可以用到多个branch. 切换到master : git checkout master merge branch: git merge new_branch...

嗨椒爆炒小小鱼
2015/11/23
47
2
Netty NIO transport && OIO transport

Netty NIO transport && OIO transport OIO transport The OIO transport is a compromise(妥协) in Netty. It builds on the known unified(统一) API but isn t asynchronous(异步) ......

秋风醉了
2014/06/20
0
0
Github 使用基本流程

首先fork一个项目 把fork过去的项目也就是你的项目clone到你的本地 在命令行运行 git branch develop 来创建一个新分支 运行 git checkout develop 来切换到新分支 运行 git remote add ups...

swingcoder
2016/07/07
309
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

如何把你的Linux系统变的更加安全

做为一个小白,以为自己懂了点Linux知识,会搭建Linux各种服务就觉得自己牛的不要要的。在我们团队里面,我将使用了一台破电脑搭建Linux服务器,上面跑着Ftp服务存放着资源,ssh服务可以远程...

问题终结者
3分钟前
0
0
lombok的使用和原理

一、项目背景 在写Java程序的时候经常会遇到如下情形: 新建了一个Class类,然后在其中设置了几个字段,最后还需要花费很多时间来建立getter和setter方法 lombok项目的产生就是为了省去我们手...

颖辉小居
3分钟前
0
0
rsync至服务同步-系统日志-screen

rsync: 服务同步;配置文件:/etc/rsyncd.conf 默认端口:873 服务启动:rsync --daemon rsync -av /root/1.txt 192.168.1.2::test/2.txt (test为模块名称) /etc/rsync.conf配置样例: #指定...

ZHENG-JY
5分钟前
0
0
读取文件中内容转换成字符串

package com.lieni.ruyu.api.xmlTool; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.Unsuppo......

newdeng
5分钟前
0
0
《PHP和MySQL Web 开发》 第8章 设计Web数据库

LCL WARNING 这是我学习《PHP和MySQL Web 开发》的读书笔记,一些重要的知识点我会记录下来,当然只会写我觉得重要的。 如果有幸有人看到这个学习笔记了,你要结合着书看,不要光看这个笔记。...

十万猛虎下画山
12分钟前
0
0
Spring+jpaNo transactional EntityManager available

TransactionRequiredException: No transactional EntityManager availableEntityManager执行以下方法(refresh, persist, flush, joinTransaction, remove, merge) 都需要需要事务i......

wpfc
13分钟前
0
0
八幅漫画理解使用JSON Web Token设计单点登录系统

八幅漫画理解使用JSON Web Token设计单点登录系统 Sep 07, 2015 in Engineering 上次在《JSON Web Token - 在Web应用间安全地传递信息》中我提到了JSON Web Token可以用来设计单点登录系统。...

祖冲之
15分钟前
0
0
Spring框架中的设计模式(三)

Spring框架中的设计模式(三) 原创: 瑞查德-Jack 在之前的两篇文章中,我们看到了一些在Spring框架中实现的设计模式。这一次我们会发现这个流行框架使用的3种新模式。 本文将从描述两个创意...

瑞查德-Jack
18分钟前
1
0
[MicroPython]TPYBoard智能小车“飞奔的TPYBoard装甲一号”

智能小车作为现代的新发明,是以后的发展方向,他可以按照预先设定的模式在一个环境里自动的运作,不需要人为的管理,可应用于科学勘探等等的用途。智能小车能够实时显示时间、速度、里程,具...

bodasisiter
20分钟前
0
0
桌面虚拟化VDI(Virtual Desktop Infrastructure)

为了保证员工(客户)不把公司的资料复制、传输给别人。可以把员工平时办公放在服务器上做。所以使用桌面虚拟化。就是把一个服务器虚拟出很多桌面系统(如:windows)。 桌面虚拟化最大的优势...

王坤charlie
27分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部