Transaction usage - best practices

[mreis1]

Transactions when used incorrectly can result in undesirable side-effects such as performance loss.

Recently I had a discussion with a friend about firebird transactions (and transactions in general).

My friend says it's ok to keep readonly transactions open for days. Generally, he prepares a statement and leaves it open until the on-premise software is closed by the user which might never happen or happen after a few long weeks if the user keeps his machine constantly active.

I think this is not a good strategy, specially because the on-premise software shares the same database with a web server that receives some important traffic and we quickly start noticing an important distance between oldest active and newest active.

What do you think about this? Is it ok to keep the read-only transaction open ? Or should read-only transactions be started when the need to query the data occurs?

[aafemt]

This is not a place for discussion. If you want one - go to firebird-support mail list.
Transactions are dictated by business-logic and there used to be a very little room for choice.
Your friend refers to transactions with Read-Only Read Committed attributes. They work but only as a workaround for the most stupid design of Delphi DB VCL components.

[mrotteveel]

@aafemt This was posted under "Discussions". If we don't want discussions on GitHub, this GitHub feature should not have been enabled.

[aafemt]

I see "mreis1 asked this question in [Q&A]" which looks somehow closer to SO format than a discussion forum.

[mreis1]

@aafemt thanks for your answer. I put my discussion topic into Q&A because among existing options, I thought it was the most suitable section. Also, Q&A is a mean to start a discussion around a subject and that was my intention. So i apologize if did it wrong.

[omachtandras]

Hi Reis, I have experience up to FB3.0 If you are starting read only READ COMMITED transaction, it is marked asap as commited and won’t stuck the garbage collection and will show you all later commited data too. But be careful, read only SNAPSHOT won’t work, because that is a snapshot… FB4.0 may changed. The global commit number appears there, which may affects this operation, but I have no experience with it. Maybe someone else has. András p.s. we used long running (for weeks) RORC transaction at least over 10 years From: Reis ***@***.***> Sent: Friday, July 8, 2022 11:15 AM To: FirebirdSQL/firebird ***@***.***> Cc: Subscribed ***@***.***> Subject: [FirebirdSQL/firebird] Transaction usage - best practices (Discussion #7228) Transactions when used incorrectly can result in undesirable side-effects such as performance loss. Recently I had a discussion with a friend about firebird transactions (and transactions in general). My friend says it's ok to keep readonly transactions open for days. Generally, he prepares a statement and leaves it open until the on-premise software is closed by the user which might never happen or happen after a few long weeks if the user keeps his machine constantly active. I think this is not a good strategy, specially because the on-premise software shares the same database with a web server that receives some important traffic and we quickly start noticing an important distance between oldest active and newest active. What do you think about this? Is it ok to keep the read-only transaction open ? Or should read-only transactions be started when the need to query the data occurs? — Reply to this email directly, view it on GitHub<#7228>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AOZGLFASA5PGRMG75NHEB7DVS7WRXANCNFSM53AKATKA>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[mreis1]

@omachtandras thank for your clarification. I think the usage here is Read-Only > Read Committed because my friend is actually using Delphi and probably those VLC components @aafemt mentioned above.
I see now that this has no performance impact as I initially thought, but considering what @mrotteveel said below, this is no longer a good solution.

[aafemt]

Performance in this case is not issue, the problem is data consistency which is required for application purposes. Also in some marginal cases there is possibility for resources (such as temporary BLOBs and result sets) to be kept until end of transaction overflowing server memory.

[omachtandras]

Performance in this case is not issue, the problem is data consistency which is required for application purposes. Also in some marginal cases there is possibility for resources (such as temporary BLOBs and result sets) to be kept until end of transaction overflowing server memory.

If you are sure you read some mostly unchanging data (e.g. master data) you can save a lot of transactions which can saves a lot of disk writing (TIP), which can be counted on servers with hundreds/thousands of databases.

But yes, this is also something that should be used wisely.

[mrotteveel]

Keeping read-only transactions open for hours is especially not a good idea since Firebird 4.0, as the behaviour of READ COMMITTED transactions changed if you use the default settings. See also section Compatibility Issues, Read Consistency for READ COMMITTED transactions Used By Default in the Firebird 4.0 Release Notes.

[mreis1]

Thank you for pointing me to that article. It has some precious information there around this topic, specially the reason why FB4 introduced READ consistency and how things worked prior to that implementation. 👍