Hi Ann, >Just curious, why do you say both "COMMIT" and "end transaction" - Commit ends the transaction.
Yes you are right. I shouldn't have said "end transaction" but I will answer your question why. I use the IBPP library for C++ and you create a pointer to the transaction. Immediately after either a COMMIT or a ROLLBACK, the transaction pointer is immediately destroyed. In my mind, I saw the destruction of the transaction pointer as the end of the transaction's lifetime. On Tue, Dec 22, 2015 at 2:39 PM, Ann Harrison aharri...@ibphoenix.com [firebird-support] <firebird-support@yahoogroups.com> wrote: > > > On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran > caroline.d.belt...@gmail.com [firebird-support] < > firebird-support@yahoogroups.com> wrote: > >> >> >> Since I began using Firebird, I have kept my transactions (type >> concurrency) very short and then call COMMIT immediately afterward. This >> has worked very well. >> >> I recently had the need to perform more complex processing and what I did >> was to keep everything short and modular. But I am now seeing that my >> design in this specific case was flawed. >> >> I am updating different parts of the same record repeatedly and I believe >> that this is causing multiple back versions which causing excessive disk >> write I/O and slowing things down terribly: >> >> a) begin a transaction, update FIELD_1 of MYTABLE, COMMIT, and end >> transaction. >> > > Just curious, why do you say both "COMMIT" and "end transaction" - Commit > ends the > transaction. > > Good luck, > > Ann > >> b) begin a transaction, update FIELD_2 of MYTABLE, COMMIT, and end >> transaction. >> c) begin a transaction, update FIELD_3 of MYTABLE, COMMIT, and end >> transaction. >> d) begin a transaction, update FIELD_4 of MYTABLE, COMMIT, and end >> transaction. >> e) begin a transaction, update FIELD_5 of MYTABLE, COMMIT, and end >> transaction. >> Note: other tables are inserted and updated during during transactions >> a-e but those tables are not presenting any problems. The problem is with >> MYTABLE. >> >> Of course, I normally update all fields in one transaction but in this >> particular case, determining the contents of each field is a complex >> process that requires manipulation and analysis of the the data provided by >> a number of other Firebird SELECT queries to the database. >> >> I am averaging about 300 transactions per minute during this process that >> may last 12 hours and during that time, things get terribly slow. >> >> So can someone confirm my suspicions, will each of the 5 transactions >> above to the same row of data cause 5 new 'back versions'? >> >> Like I said, I have always kept transactions very short. I am thinking >> of something like this instead: >> >> a) begin a transaction, update FIELD_1 of MYTABLE. >> b) update FIELD_2 of MYTABLE. >> c) update FIELD_3 of MYTABLE. >> d) update FIELD_4 of MYTABLE. >> e) update FIELD_5 of MYTABLE, COMMIT, and end transaction. >> If something fails anywhere in between, I would ROLLBACK this single >> transaction. >> >> Keeping my transactions shorter and more modular as above is easier from >> a development point of view but I have the back version performance issue. >> Although the second method means a much longer transaction, I won't have >> back versions to deal with. Do you think that this approach would be >> better? >> >> Thank you >> >> P.S. Sweeping the database does not help with the performance problem, >> the only temporary solution to regain performance is to backup using GBAK >> and restore. >> >> >> > >
