Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Transaction rules... |
Thu, Jul 26 2012 2:50 AM | Permanent Link |
IQA | Hi,
Just wanted to confirm the rules with a transaction / rollback / commit... As I've had my doubts in the past with DBISAM that this was 100% solid. Is it safe for code to call other functions that in turn access tables via queries and will these also fall under the transaction if any changes where made to them? For example say I start a transaction.... make some edits to data, then call a function which uses a query to make changes to a record, then comes back and then the user makes a decision via a YES NO box, which then rollsback or commits. Thanks, Phil. |
Fri, Jul 27 2012 2:11 AM | Permanent Link |
IQA | Just to clarify, this is an EDB question and just want to know when
using...StartTransaction / Commit / Rollback are there any big no, no's that should be adhered to after the StartTransaction and prior to committing or rollingback ? Database->StartTransaction() what should I NOT do in here AND what can I do??? Databasee->Commit() OR Database->Rollback() Thanks |
Fri, Jul 27 2012 3:40 AM | Permanent Link |
Malcolm Taylor | Phil wrote:
> Hi, > > Just wanted to confirm the rules with a transaction / rollback / > commit... As I've had my doubts in the past with DBISAM that this was > 100% solid. > > Is it safe for code to call other functions that in turn access > tables via queries and will these also fall under the transaction if > any changes where made to them? > > For example say I start a transaction.... make some edits to data, > then call a function which uses a query to make changes to a record, > then comes back and then the user makes a decision via a YES NO box, > which then rollsback or commits. > > Thanks, > > Phil. Since nobody else has replied, here's my comment. I never used Transactions in DBISAM, but my understanding is that a Transaction has to obtain an exclusive lock on the tables involved. So I would expect that any attempt from *outside* the transaction script to modify the contents of the locked tables prior to the commit/roll-back, would fail. |
Fri, Jul 27 2012 6:02 AM | Permanent Link |
Fernando Dias Team Elevate | Phil,
Yes, its safe. Any changes made after StartTransaction, no matter how they are made, are part of the transaction thus not physically written to the database. That includes TEDBTable, TEDBQuery, TEDBScript and TEDBProcedure method calls as well as changes made by SQL code in Triggers, no matter if they are called directly or not. The only exception is DDL statements that are ingeneral not allowed in transactions. When a transaction starts, the engine acquires a table transaction lock on all tables (or some of them if it is a restricted transaction), that prevents any other sessions to write to the database (still others can read). Write operations to the database, from other sessions, while there is an active transaction will be kept on hold until the end of the transaction, or fail after a timeout (cant remember how long). That’s the reason why its *very important* to keep transactions short. A few things to keep in mind: -Keep transactions short -EDB doesn't implement nested transaction (for now) so you can have only one transaction active at a time on a database. -Always rollback the transaction if an exception of any kind occurs during the transaction, to ensure that any pending row or table locks are released. That’s all -- Fernando Dias [Team Elevate] |
Fri, Jul 27 2012 6:09 AM | Permanent Link |
Fernando Dias Team Elevate | Phil,
<<For example say I start a transaction.... make some edits to data, then call a function which uses a query to make changes to a record, then comes back and then the user makes a decision via a YES NO box, which then rollsback or commits.>> This is a no no As you cant predict for how long is the user going to take before pressing Yes or No, keeping the database locked for writes, in the limit forever. Perhaps "Cached Updates" is what you are looking for. -- Fernando Dias [Team Elevate] |
Fri, Jul 27 2012 5:53 PM | Permanent Link |
IQA | > This is a no no
> As you cant predict for how long is the user going to take before > pressing Yes or No, keeping the database locked for writes, in the limit > forever. > Perhaps "Cached Updates" is what you are looking for. Thank you very much for the detailed responses Fernando! The transaction approach will work perfectly for this, as the program does a pre-check to make sure no other sessions exist and also sets a flag in the database so that no other sessions can log in while this daily function is running. It's for an end of day procedure that gets carried out by the staff. Cheers! |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |