using delayed commit

[luc_vanderveurst]

Hello,

We are running a weblogic application that opens connection pools on ASE, all connections have the same default database.

The connections run their update queries from the default database (they don't execute use database commands to go to the database in which the majority of the tables that are updated by the transaction resides), so all transactions are cross-database transactions.

This is causing some contention on the transaction log.

The database doesn't contain any data/tables (in the past we created this database to avoid that weblogic's default database is a database that is also replicated).

We want to set 'delayed commit' to true for this database, but I'm wondering if this will be limited to the default database and will handle cross database transactions correctly.

Any experience with this ?

Thanks,

Luc.

[c_baker]

How are you identifying contention on the transaction log?

Are you using the latest jConnect EBF/SP?  And have applied the sp's that come with it to the ASE?

What is the isolation level you are opening connections on?  What is the locking mechanism on the underlying tables?  How many locks are configured?  What is the performance on the log devices?  Are you using a separate log cache for your 'login' database and the other database?  How about 'user log cache' size?  Other caches?  Memory and engines?  Are statistics up-to-date?

There are a lot of other tuning issues and possibly ASE configuration issues that probably need to be investigated on the ASE first.  Just using 'delayed commit' is probably not the solution here.

Chris

[Former Member]

Hi Luc,

So I guess you use monOpenDatabases and attribute  AppendLogRequests,  AppendLogWaits to see this ?

Do you also look on the waits for this spid with monProcessWaits or overall waits monSysWaits.

I would think the start point should be here to have log cache for log pages, and check the size of user log cache size and then how the  IO performance is in the operating system for the logs devices.

Niclas

[luc_vanderveurst]

Hi Chris,

> How are you identifying contention on the transaction log?

Weblogic checks the connections in its pools and issues a rollback on those connections.

So what I often see is 50 and more processes executing a ROLLBACK command and blocked by a process that is doing a rollback, i.e. waiting for each other to complete.

> Are you using the latest jConnect EBF/SP?  And have applied the sp's that come with it to the ASE?

How to find out what is the latest ?

The latest I know  is what comes with ASE 15.7 SP 132.

jConnect (TM) for JDBC(TM)/7.07 SP130 (Build 27074)/P/EBF23283/JDK 1.6.0/jdbcmain/OPT/Mon Jul 21 12:39:01 PDT 2014

> What is the isolation level you are opening connections on?

Default

>  What is the locking mechanism on the underlying tables?  How many locks are configured?  What is the performance on the log devices?  Are you using a separate log cache for your 'login' database and the other database?  How about 'user log cache' size?  Other caches?  Memory and engines?  Are statistics up-to-date?

The transactions are only doing a rollback. There are no tables involved.

Edited and added: This is the situation in which I see blocking locks with sp_who. There are also the regular transactions that do updates in multiple databases and commit in the default database, but I don't see often commit waits on these transactions with sp_who.

Thanks,

Luc.

[luc_vanderveurst]

Hi Niclas,

> So I guess you use monOpenDatabases and attribute  AppendLogRequests,  AppendLogWaits to see this ?

> Do you also look on the waits for this spid with monProcessWaits or overall waits monSysWaits.

It's my colleague who's using asemon that saw the contention on the transaction log.

I'm not quite sure what mda tables are used to come to this conclusion.

I'll have to do more investigation.

> I would think the start point should be here to have log cache for log pages, and check the size of user log cache size and then how the  IO performance is in the operating system for the logs devices.

User log cache size is 8K, and IO performanc on OS level seems normal.

As I said in my reply to Chris's comment, when I notice blocking locks in sp_who,  it's not about large transactions that need to be committed.

Weblogic executes a rollback on an idle connection (so normally there is nothing to rollback)  in its default  database that doesn't contain tables and that has trunc log on checkpoint on.

My main question is: can it harm to put delayed commit on in that database.

Thanks,

Luc.

[former_member187136]

Hello Luc Van der Veurst,

We have both options:

Option 1: Database Level

sp_dboption mydb, 'delayed commit', true | false

sp_dboption has been extended to support a new db option 'delayed commit'. This can be set similar to other db options. If this is set to 'true' then delayed commit will be effective at the database level.

sp_helpdb has been extended to report the status of this option in the output.

Option 2: Session Level

set delayed_commit on | off | default

A new set option 'delayed_commit' has been added that can be set to on, off or default. A setting of 'default' will indicate that the database level setting takes effect. A setting of 'on' or 'off' will mean the session level setting will be effective irrespective  of the database level setting.

Regards

Kiran K Adharapuram