Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 14 total |
Handling Transactions |
Fri, Jul 27 2007 5:49 PM | Permanent Link |
Gordon Turner | I believe I understand the purpose of the table list parameter to the
StartTrans function, but I want to make sure... If I pass a list of tables, then only those tables will be involved in the transaction (as opposed to the whole database). However, I cannot start a second transaction using different tables until the first transaction has completed with either a Rollback or a Commit. Is this correct? And just to help me understand what I can and cannot do... 1. What is the select behavior for tables involved in the transaction for other users (when a list of tables is passed)? Can they also start a transaction on the same table but different records? 2. Are there any restrictions on updating tables in the database not involved in the transaction by other users when a transaction is being processed? 3. Can I update a table not involved in the transaction during the processing of the transaction (if I pass a list of tables)? -- Gordon Turner Mycroft Computing http://www.mycroftcomputing.com |
Fri, Jul 27 2007 6:08 PM | Permanent Link |
Gordon Turner | And to add to the list...
If I start a transaction and make some changes using SQL, are any changes made with the FindKey, Update methods involved in the transaction as well (as long as either the table is included in the list for the StartTrans or I use the EmptyEDBStringsArray parameter)? -- Gordon Turner Mycroft Computing http://www.mycroftcomputing.com |
Sat, Jul 28 2007 5:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Gordon
I'm going to have a go at replying although I suspect my knowledge is about on a par with your own, so someone correcting both of us will be helpful. >If I pass a list of tables, then only those tables will be involved in >the transaction (as opposed to the whole database). However, I cannot >start a second transaction using different tables until the first >transaction has completed with either a Rollback or a Commit. Is this >correct? Not in the same piece of procedural code following the transaction - you have to wait for it to finish. But yes in a separate thread, in response to an event, or as I suspect your question meant, in the body of the transaction itself. IE yes you can nest transactions. >And just to help me understand what I can and cannot do... > >1. What is the select behavior for tables involved in the transaction >for other users (when a list of tables is passed)? Can they also start >a transaction on the same table but different records? They can start it - but it will have to wait for the other transaction to finish before it starts. At it current stage of development ElevateDB locks the table not a record or set of records. Think in terms of setting Exclusive to True (but just for editing) for the tables involved in the transaction and you're nearly there. >2. Are there any restrictions on updating tables in the database not >involved in the transaction by other users when a transaction is being >processed? Only ones you impose. I'm not sure what happens to any RI or other constraints you've set though. >3. Can I update a table not involved in the transaction during the >processing of the transaction (if I pass a list of tables)? Yup. But I'm not sure if its subject to commit/rollback or just treated as a normal table edit. >If I start a transaction and make some changes using SQL, are any >changes made with the FindKey, Update methods involved in the >transaction as well (as long as either the table is included in the list >for the StartTrans or I use the EmptyEDBStringsArray parameter)? No idea Roy Lambert |
Sat, Jul 28 2007 9:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Gordon,
<< If I pass a list of tables, then only those tables will be involved in the transaction (as opposed to the whole database). However, I cannot start a second transaction using different tables until the first transaction has completed with either a Rollback or a Commit. Is this correct? >> Correct. << 1. What is the select behavior for tables involved in the transaction for other users (when a list of tables is passed)? Can they also start a transaction on the same table but different records? >> No, only one transaction can be active on a given table at one time. << 2. Are there any restrictions on updating tables in the database not involved in the transaction by other users when a transaction is being processed? >> No. << 3. Can I update a table not involved in the transaction during the processing of the transaction (if I pass a list of tables)? >> Sure, no problem. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Jul 28 2007 9:12 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Gordon,
<< If I start a transaction and make some changes using SQL, are any changes made with the FindKey, Update methods involved in the transaction as well (as long as either the table is included in the list for the StartTrans or I use the EmptyEDBStringsArray parameter)? >> Yes. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Jul 28 2007 10:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< If I pass a list of tables, then only those tables will be involved in >the transaction (as opposed to the whole database). However, I cannot start >a second transaction using different tables until the first transaction has >completed with either a Rollback or a Commit. Is this correct? >> >Correct. What about other threads or nesting? Roy Lambert |
Sat, Jul 28 2007 12:19 PM | Permanent Link |
Gordon Turner | I've been trying out a new architecture for my application. All of the
database functionality is contained in a COM object. I pass SQL strings or other record specific variables to the COM object where the tables are accessed, data fetched, records updated, etc. Data results are passed back in variant arrays which are handled by the classes, so the program has no access whatsoever to the tables directly. The COM object is created once at the beginning of the application and nowhere else. Here is the behavior I'm seeing. I call the COM object to start a transaction using the EmptyEDBStringsArray parameter... if not AppDBDataMod.AppDB.InTransaction then begin AppDBDataMod.AppDB.StartTransaction(EmptyEDBStringsArray); and then add a master record to Table A and detail records to Table B (through separate calls to the COM object for each record). Before adding the detail records to Table B (because they can be added in more than one way) I check to see if a transaction has already been started by calling an InTransaction function in the COM object... Result := AppDBDataMod.AppDB.InTransaction; and if not I call the COM object to start one. At the end of the updates I check where the transaction was started and call the COM object from the appropriate place to perform the Commit... if AppDBDataMod.AppDB.InTransaction then AppDBDataMod.AppDB.Commit(True); As I'm debugging my code, my expectation was that if I cancelled execution of the program before the Commit then all the changes would be rolled back. But what happens is that each table change passed to the COM object is performed immediately and nothing is rolled back. As soon as the table change is executed from within the COM object, the results are visible within EDBManager, even though I "know" a transaction has been started as the InTransaction method returns True as I continue with the processing. So I'm guessing I'm missing something important here or I am misunderstanding how EDB works in the context of a COM object. Any help with this would be appreciated. -- Gordon Turner Mycroft Computing http://www.mycroftcomputing.com |
Mon, Jul 30 2007 8:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< What about other threads or nesting? >> Threads, yes. Nesting, no. Nested transactions will come with the enterprise server that can do proper versioning. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Jul 30 2007 8:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Gordon,
<< and then add a master record to Table A and detail records to Table B (through separate calls to the COM object for each record). Before adding the detail records to Table B (because they can be added in more than one way) I check to see if a transaction has already been started by calling an InTransaction function in the COM object... Result := AppDBDataMod.AppDB.InTransaction; and if not I call the COM object to start one. At the end of the updates I check where the transaction was started and call the COM object from the appropriate place to perform the Commit... if AppDBDataMod.AppDB.InTransaction then AppDBDataMod.AppDB.Commit(True); As I'm debugging my code, my expectation was that if I cancelled execution of the program before the Commit then all the changes would be rolled back. >> What do you mean by "cancelled" ? Do you mean killing the process via the IDE ? << But what happens is that each table change passed to the COM object is performed immediately and nothing is rolled back. As soon as the table change is executed from within the COM object, the results are visible within EDBManager, even though I "know" a transaction has been started as the InTransaction method returns True as I continue with the processing. >> Are you saying that this is occurring *after* the expected rollback, or before ? -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Jul 31 2007 10:43 AM | Permanent Link |
Gordon Turner | Tim Young [Elevate Software] wrote:
> > What do you mean by "cancelled" ? Do you mean killing the process via the > IDE ? Yes. My expectation is that no changes in a transaction would be saved until the actual Commit, whether that is an exception that occurs within the executable, or the process being killed via the IDE or termination of the process via Windows. > Are you saying that this is occurring *after* the expected rollback, or > before ? Before the rollback or commit. I pause the executable in the IDE to step through the code. While stepping through the code I complete the update to the first table and then pause code execution - refresh my view of the data in the EDBManager - and see the change that was made. -- Gordon Turner Mycroft Computing http://www.mycroftcomputing.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |