--- In firebird-support@yahoogroups.com, Dimitry Sibiryakov <sd@...> wrote:

>
> > For inserts and updates, for example, a window representing a row from the database opens, a transaction starts, a row is read, the transaction commits. The user does what they want with the contents of the row, hits "OK" and if the data has changed, a transaction starts, the row is inserted or updated, and the transaction is committed. If the user hits "Cancel", the transaction doesn't even start.
> >
> > Now I'd like to provide a window where the user can make repeated update operations to the data, query the data and, at the end of the operation, hit the "OK" button to commit, or "Cancel" to roll back the transaction.
> >
> > I believe this would involve starting a transaction when they open the window, and leave the transaction open for possibly a long time.
>
> What prevent you from using the same pattern? Start transaction when
> necessary and stop it when the action is done.

Thanks Dimitry:

The window I'm thinking about offering will have child windows, where they can alter records that have several unique varchar columns. They might alter a varchar column that will cause a colision. Not a problem, the app will warn them of the conflict and ask if they wish to proceed. They can repeat this on other rows and do a lot of updates inserts and deletes. And when they are finished they might decide they don't like the results of what they've done and hit "Cancel".

If I start and commit transactions each time the user updates the database, I wont be able to roll them back if the user changes their mind, and hits "Cancel" when quitting the parent window of this activity.

> > I wouldn't be surprised if people go to lunch, or even go home for the weekend, while the window, and transaction, are open. They could even turn off their PC or the server could go down for maintenance and never properly quit the window and commit or roll back the transaction.

> >
> > Is this a problem?
>
> Yes, it is. If active transaction is left for weekends and other
> activity still continues, the difference between Oldest Transaction and
> Next Transaction will grow, so server will run autosweep immediately
> after old transaction is released. It can drop server performance for
> awhile.
> On the other hand, if there is no big parallel activity (say, 20000
> transaction during this transaction lifetime), long transaction is
> almost harmless.

I value your opinion. If I do offer this window I might just inform the user that the changes they make are not reversible once they hit "OK" on a child of the parent window. This will allow me to use my normal pattern of a transaction to load the contents of a window, and a short lived transaction to respond to the "OK" button.

If they make too big a mess? I hope they follow my admonition about using the "Backup The Database" button.

Thank you Dimitry.

Larry