New topic: Simulating Nested Transactions Using Multiple Connections
<http://forums.realsoftware.com/viewtopic.php?t=45701> Page 1 of 1 [ 8 posts ] Previous topic | Next topic Author Message moe Post subject: Simulating Nested Transactions Using Multiple ConnectionsPosted: Wed Oct 24, 2012 11:23 am Joined: Mon Nov 24, 2008 12:25 am Posts: 152 Hi, My app begins a transaction on a REALSQLdatabse upon the user opening a window. The user selects drop-down boxes of name-value pairs to add to a listbox, which also inserts into a database table. On the window, there is an "OK", "Cancel" and "Check For Updates" button. Not surprisingly, the OK button commits the transaction and the Cancel button cancels the transaction. The "Check For Updates" button goes out to the internet and checks for updated name-value pairs for the user. It needs to save those new values to the database and update the drop-down boxes. However, since the the app is already in a transaction, it cannot update the database if there are updated name-value pairs for the user. So I thought I would use a separate temporary connection to the database because I thought each database connection handles their own transactions, but this is not the case. When committing inside the second database connection, I get "Database Locked" errors because I'm already inside an open transaction. Is there any other way around this? Many Thanks! Top Jym Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 1:31 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3170 I'm sort of lost. Changing the updates to the database would be part of the transaction, so you can do it but it would be lost if the user does a rollback. If you don't want them to be able to do both at the same time, don't give them that option. keep "Check For Updates" enabled = false until they click either OK or Cancel, and give them a close window button. Top npalardy Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 2:07 pm Real Software Engineer Joined: Sat Dec 24, 2005 8:18 pm Posts: 7503 Location: Canada, Alberta, Near Red Deer moe wrote:Hi, My app begins a transaction on a REALSQLdatabse upon the user opening a window. The user selects drop-down boxes of name-value pairs to add to a listbox, which also inserts into a database table. On the window, there is an "OK", "Cancel" and "Check For Updates" button. Not surprisingly, the OK button commits the transaction and the Cancel button cancels the transaction. The "Check For Updates" button goes out to the internet and checks for updated name-value pairs for the user. It needs to save those new values to the database and update the drop-down boxes. However, since the the app is already in a transaction, it cannot update the database if there are updated name-value pairs for the user. So I thought I would use a separate temporary connection to the database because I thought each database connection handles their own transactions, but this is not the case. When committing inside the second database connection, I get "Database Locked" errors because I'm already inside an open transaction. Is there any other way around this? Many Thanks! Yeah Don't start a transaction when you open the window This is a great example of how to lock up a database because if you do this & the person goes to the washroom, goes for lunch etc then you lock up the database for anyone else. Start a transaction only when the person presses OK, keep it short & sweet, and commit very shortly after that and you wont have these problems. _________________ My web site Great White Software RBLibrary.com REALbasic learning Top moe Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 2:29 pm Joined: Mon Nov 24, 2008 12:25 am Posts: 152 I'm probably not explaining myself correctly. It's not and shouldn't be part of the same transaction. The user opens a window which basically contains a listbox representing a table in the database. The Check For Updates button checks for new values of another table. I want to commit the updates to the second table if there are any, but I don't want to commit whatever the user may have entered in the listbox, until the user says its OK, by clicking the OK button on that window. It's not a multi-user database so I don't mind locking up the database when the window is opened. It seemed like the best way at the time. Instead of keeping track of when the user adds or removes items in the listbox using arrays and then doing all the SQL when they click on OK, the SQL is done when as the user clicks and then it's all either committed or canceled at the end. Is this not the best method? Many Thanks! Top Jym Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 2:38 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3170 Not really, it's a SQLite database so the Lock is controlled by the first write statement not by beginning the transaction. You can have all your connections beginning a transaction but it won't be locked until one of them writes to the db file. But that said, it is 1,000 times better to control it in the OK button and not worry about Rolling back and 'locking' the database. If the User clicks Cancel then you simply reload the table Use the RowTag to track the Update, Delete and Inserts Top moe Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 3:06 pm Joined: Mon Nov 24, 2008 12:25 am Posts: 152 Jym wrote:Not really, it's a SQLite database so the Lock is controlled by the first write statement not by beginning the transaction. You can have all your connections beginning a transaction but it won't be locked until one of them writes to the db file. But that said, it is 1,000 times better to control it in the OK button and not worry about Rolling back and 'locking' the database. So I should be doing all the SQL and Beginning and Ending the transaction in the OK button? Jym wrote:Use the RowTag to track the Update, Delete and Inserts Can you elaborate on this? This is where it seems to be more trouble than what it's worth. Many Thanks! Top timhare Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 3:52 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 11746 Location: Portland, OR USA Why not just add the Check For Updates data to the transaction. If the user cancels, then cancel the whole thing and re-add the remote updates. Top Jym Post subject: Re: Simulating Nested Transactions Using Multiple ConnectionPosted: Wed Oct 24, 2012 5:46 pm Joined: Sat Oct 01, 2005 5:19 pm Posts: 3170 psychic powers aren't that strong When you open the window do you populate the listbox with information or is this window strictly for New Information? What is it populated with? Give the whole SQL statement How are you moving the info to the listbox from the drop downs? Give Code How are you saving it to the db? Give SQL Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 8 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
