Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

DB2 LUW V9.7 CUR_COMMIT database configuration option – buyer beware

While working on a deadlocking problem, I came across the cur_commit optoin that is now available in version 9.7.  This option is part of the ongoing efforts to make DB2 more Oracle compliant. 

After reading about the cur_commit database option, it seemed to be a fair solution to the problem.  We installed/upgraded our databases to v9.7 and enabled the option – our deadlocking problems were over.  Case closed.

Well, maybe not.  Here’s the rub.  If you read about what little information there is about cur_commit, you will find that the option works by reading logs to get the currently committed value if an update has been done.  This works great in theory and even in practice, but I found that during our performance tests, we suffered greatly.  Reading the logs does not seem to be especially efficient for these cases and our system suffered greatly. 

In analyzing system counters, I noted that ‘Log pages read’ (db snapshot) values were very high.  In response to this, I increased LOGBUFSZ value to something very large.  I started off at 4096 and this make no difference what-so-ever.  I am up to 20480 which is 80Mb  and things have mostly stabilized, but I am still seeing a lot of ‘log pages read’ from time to time and when that value goes up, our performance goes down.

So the use of ‘CUR_COMMIT’ option in 9.7 is not a cure-all for deadlocks and should be used with care.  I don’t think it should be turned on without some forethought and a good amount of performance testing.  The CUR_COMMIT option is really a bandaid option.  Most deadlocking problems are caused by bad application design and should be fix from that end if possible.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.config.doc/doc/r0053556.html

1 comment to DB2 LUW V9.7 CUR_COMMIT database configuration option – buyer beware

  • Were there many number of log read IOs or just log pages read (logical, still in buffer)? Increasing the LOGBUFSZ is the recommended best practice when using currently committed because the log buffer is utilized to resolve conflicts. From my perspective it is efficient, but the question is regarding the application and the database design: What is the structure of your transactions? How much data needs to be logged (grouping of columns)? How do you lock/access data, in which order?

    I consider the new mode cur_commit one of many ways to deal with deadlocks. But it is only for the symptoms, not the real cause behind deadlocks.

    Henrik

Leave a Reply

Buy Generic Viagra USA
Where TO Buy Viagra Online
Buy Viagra Where
Cheap Cheap Viagra
Line Viagra
Buy Discount Viagra
Cheapest viagra online Pills
Buy Viagra Sample
Cheap Viagra Online
Cheap Viagra FAST Shipping
Viagra for Sale
Buy Viagra New York
Cheap Websites For Viagra
Viagra FOR Sales Online
Cheapest Viagra Prices
Cheap Viagra Pills