Open transactions

[Kocky]

Hello there,

I am researching a problem in a program of a client of ours where the database keeps growing bigger while there is no data added to the database. I have learned that this occurs when there are a lot of open transactions that stay resident and prevent new transactions from cleaning up carbage data. So somewhere in the program there has to be a part where a transaction stays open.

The program runs 24/7 in a production environment so it grows very fast to around 12 mb and then the IBServer hangs. Only after performing a restore/backup the db file goes back to around 280kb and the IBServer runs again.

My question is. When do open transactions occur. I'm using Delphi as development tool. How can I trace a open transaction.

Also in IBConsole I use Maintenance->Statistics to get a list with info about the current transcations.
Currently it shows me:

Oldest transaction 19
Oldest active 20
Oldest snapshot 20
Next transaction 8261

What does this mean ? The difference between Oldest and Next transaction ?

Greetings,

Pascal.

 

[MartijnTonies]

Well, the Oldest and Next should be as close together as possible.

Do you use the BDE?

Do you use AutoCommit or CommitRetaining on your transactions?

Do you use client-dataset components?



Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[Kocky]

No I do not use BDE, I don't autocommit, I don't use CommitRetaining and I don't use client-dataset components.
I'm using Interbase Express components.

 

[Kocky]

I have had the service running all night.
When it started yesterday evening the database file was 256 kb. This morning when I checked the database had grown to 510 kb and no records were added to the database only updates have been processed. When I checked the maintenance section in IBConsolt I got the following:

Oldest transaction 32
Oldest active 33
Oldest snapshot 345
Next transaction 10205

I had set the sweep interval to 10000 transactions so at a certain point this morning my cpu went sky rocket to 100% and I think the sweep kicked in. After that:

Oldest transaction 10292
Oldest active 10293
Oldest snapshot 10293
Next transaction 10294

But the database file remains 510 kb's ?

Any idea's ?

 

[Kocky]

Hello there,

I have found the cause of my problems.
There is a windows service application and a pc desktop application that access the same database. They are both installed on a system that runs in a production area and are working 24/7 day and night. The problem was that in the pc desktop application a screen was constantly open which had a dbgrid on it and also a dataset that was constantly open.

So when you open a dataset, Interbase starts a transaction for you and as long as the dataset is open the transcation is not commited. As soon as I close this screen and also the dataset you can see that the "next transaction" and the "oldest active" transaction line up again nicely.
So problem discovered and now I have to think of a way to overcome this.

But how do you ever accomplish this in Interbase ? When I want a dataset to be permanently open in a 24/7 environment ? Will I always have a open transaction ?
My transaction component is set up the following way:

read_committed
rec_version
nowait

I thought that read_commited caused a transaction to be closed after the sql was performed ?

 

[MartijnTonies]

read_committed" is a sort of isolation. It means that it reads committed rows. Another one, for example, is "snapshot" - this will start a transaction and reads only records as available at the start of the transaction. If another transaction commits records, these will not be read by the snapshot transaction as such. A snapshot isolation is very useful for running long reports.

You can open a dataset and use a client-dataset component. After that, close (commit) the transaction and the grid will stay "filled" with data. Alternatively, refresh the grid dataset every x minutes (x being a bit small) and close the transaction between refreshed. This will have the benefit of refreshing the grid with the latest committed data in the database, while still allowing the transaction counters to more forward.

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[Kocky]

Thank you Martijn,

I am beginning to understand the whole idea of transactions under Interbase but still I encountered something strange.

When I start my application I check all my datasets and queryies if they are closed and if my transaction component in not "in transaction".
After that I create a new screen and I open a dataset in the constructor of the screen. When I'm done showing the data from one dataset I close this dataset in the destructor using IBDataset.Close.
But when I check my transaction component after that it says that it is "in transaction" although I closed the dataset ?

Any idea's ?

 

[MartijnTonies]

Hi,

Transactions aren't controlled by datasets. However, your IBX Dataset component(s) may do so. You might want to check the AutoStopAction property of your IBX Transaction component.

Most probably ( I don't use IBX myself ), the dataset will automatically start a transaction when opened, but you need to specify what to do when it closes.

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com