×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Open transactions

Open transactions

Open transactions

(OP)
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.

RE: Open transactions

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

RE: Open transactions

(OP)
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.

RE: Open transactions

(OP)
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 ?
 
 

RE: Open transactions

(OP)
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 ?

RE: Open transactions

"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

RE: Open transactions

(OP)
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 ?

RE: Open transactions

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close