You've got to be careful here. Some things to ponder:
In a multi-user environment, you don't want user A to be blocked because user B is trying to do something. If they are blocked, it should be for no longer than a second. That means, for example, you can't have things locked for the entire time that a dialog is open.
Another thing to keep in mind is the availability of features. MySQL at one time didn't have transaction support. Now it does. Other database systems you might be thinking of supporting may or may not have this or that feature.
I suggest a database abstraction. Have the ability to write database-specific code that is optimized for the way things are done in a particular database. That'll give you the speed that is both desired and deserved. But if you have the ability to drop a different piece of code in for a different database for a particular action, then you can design it in such a way that you don't have to do 'least-common-denomenator' and all the performance penalties that doing so will impose on you. Have an architecture that doesn't care what the data-store device is, and doesn't have any knowledge of that store, it just says "this is what I need done." Figure out what kinds of things you will need done, and then find an OPTIMAL way of doing that in each database.
That MAY mean that setting up the tables will be different for each back-end. That's not as bad as you might think. Design it such that doing so won't fundamentally break the system. Then, you could have an XML back-end. You could have a CSV back-end. You could have a dbase back-end. Or Oracle. Or MySQL. Or whatever you want. And the limitations of the back-end could be completely transparent to the front-end. The front-end doesn't suffer from the limitations of the back-end. For example, some systems may require a file-based lock. Others may require a row-based lock. Others, a table-lock. Design it in the abstract, and then take advantage of the available features of each back-end in order to make it work.
Or, pick one database and say "this is it, folks! it works here, no where else." But we both know that there is a reason you don't want to do that...
Now, you may have already planned on doing all of that. Don't know. But on the off chance that you hadn't, I figured I'd toss it out there! :)
From: Derek Atkins <[EMAIL PROTECTED]> To: Benjamin Carlyle <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: QOF-to-SQL Proof of Concept Date: Sat, 29 May 2004 22:59:53 -0400
Benjamin Carlyle <[EMAIL PROTECTED]> writes:
> The database handles it for you. > > Whenever you say "BEGIN TRANSACTION;" a exclusive lock will be placed on > the whole database. When you commit or rollback your transaction the > lock is removed. Any insert, update, or other operation that modifies > the database will implicitly begin and commit a transaction during its > execution. Every select statement locks the whole database with a shared > lock and unlocks when the query is done.
Good. This is exactly what I expected. So long as this works across multiple applications connecting to the same database, then it's just fine. I think we'll still need to do some sort of internal locking to stop the race condition of having two apps trying to change the same piece of data..
I guess we could have the internal "begin edit" actually perform a database lock, but that's not really a viable option because the app could hold that lock for a long time waiting on user input. So we just need to be careful to:
lock db do { check status if (status changed) break; update record } while (0); unlock db
I'm not sure a BEGIN TRANSACTION / COMMIT TRANSACTION is sufficient to do this, unless we can actually perform a SELECT and get back real data in the middle of a transaction?
> Benjamin.
-derek
-- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/ PP-ASEL-IA N1NWH [EMAIL PROTECTED] PGP key available _______________________________________________ gnucash-devel mailing list [EMAIL PROTECTED] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
_______________________________________________ gnucash-devel mailing list [EMAIL PROTECTED] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
