|
Tips & Tricks to provide DB concurrency?
Hi,
After checking out how to deploy MySQL, we got to the conclusion that it's overkill for the kind of setups we have (mostly stand-alone apps, at most 3-4 computers writing data during the day.)
So... I'm trying to find ways to reduce the risk of data corruption when using SQLite, which is like Access or dBase, ie. server-less, file-based SQL database.
I was thinking of :
1. The first host to access the data will trigger a copy of the .DB file into .BAK to ensure that we have a good copy in case anything happens during the day. Then, we'll find a way to take snapshots a couple of times during the day (eg. no activity on the server since X minutes, etc.)
2. Make full use of the err info returned by SQLite (eg. BUSY : loop, try again in 2s, up to 5 times before giving upt)
3. Use a file on the server as on/off semaphore
Any other ideas from all you DBA's out there? :-)
Thank you.
Fred
Wednesday, May 26, 2004
Maybe I'm off base here, but I think you're asking the wrong questions:
1) What are the implications of a (multiple) user(s) being blocked from reading/writing data at any given moment?
2) What is the likelihood of a user keeping the file "open" all day, thereby preventing other users' access?
3) What are the implications of data being lost/overwritten by a user?
4) Is it easier implementing (designing, building, testing) a locking mechanism or deploying and managing mysql?
In one of our applications, we allow a pda device to download a database, but we mark the time and we timestamp all entries (modified or new). Then, when they connect again, we grab all entries after their download timestamp. We also archive all of the mini-databases.
KC
Wednesday, May 26, 2004
Thank you for your help.
1) Some of our customers have more than one host that needs read/write access to the central database. The SQLite author says that the lock feature of Windows, either 9x or NT, is not reliable. So it's possible for two hosts to attempt to make changes to the database at the same time. That's what I'm trying to avoid, or at least make reasonably unlikely.
2) What is the likelihood of a user keeping the file "open" all day, thereby preventing other users' access?
We'll go through our code, and make sure we never leave a host hanging with an open connection.
3) Like any unwanted rewriting, that's not something that we want.
4) Implementing some form of protection is a better solution than having to drive around the country installing TCP/IP and MySQL. Especially since we're not talking heavy-duty work either. At most, a few hosts, making a few write's a day. That's why we gave up on the c/s solution.
>> In one of our applications, we allow a pda device to download a database, but we mark the time and we timestamp all entries (modified or new)
Good idea, but this is some kind of replication right? The hosts are located on a LAN, so they'll access the same database.
Fred
Wednesday, May 26, 2004
"The SQLite author says that the lock feature of Windows, either 9x or NT, is not reliable"
The SQLite author is talking through his ass. MS Access and many flavours of DBase have relied upon the built-in locking features of Windows for many years now. You get into problems when you try to roll your own locking that doesn't cooperate with the operating system.
If this is entirely new development, and you still have the ability to choose a database manager, I suggest one of the following:
1. MS Access - if you can manage the install, which is pretty heavy. It scales well up to about 10 concurrent users and a few hundred megabytes in a single db file.
2. CodeBase. This is a very lightweight and fast DBase compatible library built entirely in ANSI C, with source code included. I have deployed shrink-wrap apps using this tool with absolutely no issues related to database bugs or record/file contention. It is a very mature product. Its biggest advantage is that you can build your own DLL or static library, so you never have to worry about someone else's installer killing your database engine.
You can find info on CodeBase at www.sequiter.com
There are plenty of other lightweight database engines around. Some of the free ones are probably great too. I haven't been in the desktop database space for a while.
My number one suggestion for your app is to *expect* concurrent access and design accordingly. It will save you a world of hurt in the end.
Craig
Wednesday, May 26, 2004
Fred, with all due respect, your original post said to me:
"We decided not to use [x] because we didn't need all its features. How can I roll those features by hand?"
Philo
Philo
Wednesday, May 26, 2004
Yup, I have to agree with the above suggestions (craig).
Note that you can with visual studio use the ms-access data engine, and NOT have to bother, or install ms-access.
The JET data engine is part of visual studio, and is free to distribute with your applications that you write in VB, c++, or whatever.
The size, and install of JET is not that large.
Products like simply accounting, or even Joel’s “CityDesk” use the JET engine (which uses the ms-access format).
What this means is that your data can be used by products like excel, or word can merge data. And, even ms-access *can* be installed to do reports on YOUR application if you wish. Or, you can even use the query tools in Excel.
So, the JET engine is a good choice..and you don’t have to have, nor install ms-access to use this product.
I used the ms-access format and JET for years. Some applications have about 5 or 6 users all day doing reservation bookings by phone (very interactive application). There is about 60 tables, and they are HIGHLY RELATED. I use cascade deletes to delete child records (less code to write as I let the JET engine manage that stuff). So, I use the referential integrity options available with the JET engine to not allow stuff like orphaned child records etc. All this runs just fine without any contention problems at all, and no server engine is needed.
In fact, Microsoft is about to release a migration and network scanning tool for ms-access. This tool will scan for mdb files, and allow one to “inventory” where, and how many mdb files are on your system. One company that use the tool found 45,000 mdb files. So, without question the JET database format (ms-access) is really popular…in fact today it is much like what the dbaseIII file format was 10 years ago (winfax, and even products like codebase use the dbase file format). Now you see products like Simply Accounting, or stuff like Joel’s CityDesk use the JET engine.
After all, it is included with Visual studoo..and it don’t cost anything extra to use. And, it is very fast, and the install of jet can be included into your VS application (this works well).
I should note that I am under NDA regarding the announcement of the access scanning tool. While this migration tool “was” under NDA for me, it will be announced and demo at the up-coming tech-net. As far as I can tell..I am allowed to mention this tool now as per my last live meeting with MS.
Anyway…my bets are on using JET.
And, Yes, code base is also a great choice also. Ken Sawyer who founded Sequenter software/codebase was at U of A while I as there. And, he was also president of the Alberta Society software developers while I was membership director. Ken still resides in my City, and is home of his very successful software company.
Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn
Albert D. Kallal
Wednesday, May 26, 2004
Philo hit it right on the head.
If you need concurrency, use a DBMS that has it built in, don't reinvent it. MySQL and Access are not really overkill; you either have a single-user system or you have a multi-user system. And you have all the other administration tools that come with them that you'll need for backup/restore, etc.
Tom H
Wednesday, May 26, 2004
Fred, you're going to spend far more time (read: money) implementing this than you could spend on a dbms with it built in. Just pay it up front.
Thursday, May 27, 2004
Yep, because the moment that a few people can access this thing, they'll all want to. Then you'll start having locking issues and a rip in the space-time continuim will happen.
I saw this at my last job.
So just go for a rdms right off the bat. Then, six months from now when someone says "Can I get to this app offsite" or "Can we sell this thing?", you don't have to hide under your desk and cry.
It seems silly, but everyone here has run into similar things.
Day 1: "We'll never use it for X."
Day 45: "So when can we start using it for X?"
KC
Thursday, May 27, 2004
*Especially* since MySQL is free. What's the reason for not using it? I know there are some stilly licensing issues. You could always use PostgreSQL which is a much better DBMS engine and has a better licensing scheme.
MR
Thursday, May 27, 2004
KC,
in my experience the opposite is also true:
Day 1: X needs to be extensible, portable, distributed, generic, customizable and scalable to about 1.000 concurrent users.
Day 1.831: X still runs on the same single PC, is used by around 15 odd people and all "customization" anyone has ever done was changing a summary table on the default page and adding two extra tables and acompanying CRUD screens.
Just me (Sir to you)
Thursday, May 27, 2004
Thx, that's exactly what I was about to reply :-)
Like I said, we are willing to take the risk of using a file-based DBMS like SQLite (or Access, or Paradox, whatever) because the deployment of a c/s DBMS like MySQL will prove to be too difficult (ie. we'll have to drive to every single location, while SQLite is just one DLL).
So... I'll write a test proggie that access the DB every second for read/write, and run this thing on 3 hosts on our LAN to see if the lock provided by the OS + file acting as on/off semaphore is good enough. In addition to taking a snapshot of the .DB file a couple of times a day.
Thank you :-)
Fred
Thursday, May 27, 2004
Don't want the overhead of managing a server?
Don't want to worry about the nice features that you lose if you eschew a server?
http://www.sleepycat.com/
David Jones
Thursday, May 27, 2004
Thx David. I did look briefly at hashing systems like BerkeleyDB, but unless I'm mistaken, those are just used for key/value stuff like the Registry, right? What about when you have multi-value records?
Fred
Thursday, May 27, 2004
Fred,
these type of issues are notoriously difficult to test for, and the file as semaphore thing raises serious alarm bells.
I'd go with Albert's advice.
Just me (Sir to you)
Thursday, May 27, 2004
MySQL has an embedded version:
http://dev.mysql.com/doc/mysql/en/libmysqld_overview.html
as does SQLAnywhere (which is much better than MySQL):
http://www.ianywhere.com/products/sql_anywhere.html
MR
Thursday, May 27, 2004
Here's a thread on Access record locking/concurrency:
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=47300&ixReplies=10
Anon
Thursday, May 27, 2004
Thx everyone for the feedback. We'll try to build a prototype that is as realistic as possible over here, then find a willing victim among our customers to try it out and see how long it takes before the database blows up :-)
Fred
Thursday, May 27, 2004
Also, i suggest http://firebird.sourceforge.net/, is free, is a REAL sql engine, is low-resource intensive, is equal good for data processing and for report processing. Can be used embebed (only copy 3 files to the dir of the app), the instalation for the server is, copy files, add shorcut to Start menu..thats all. Is reliable, secure and scale if you need to.
Also, i heard good things about http://www.nexusdb.com/.
I don't recomend relly in acces or dbase or paradox or foxpro database for sensitive things (=money things), are simply a problem for your tech support, in a old project we switch from FoxPro (millons of times best than acces) to sql server not because the buzz, but realibity...our support lower 45-60%.
You can relly best in a sql engine..and something like firebird, mysql, postgree, etc... can fit too in small aplications. Also, you get things like more credibility, more performance, more security, backups while users are working and easy to escale later, plus, you not need think so hard how change later the app to other database (change from file base to sql is more hard thatn from a sql engine to other) or if the thing grow to web or want release a "corporate" versions.
ALways, is best minimize the change...
Mario M.
Thursday, May 27, 2004
Pardon me if this is obvious, but why do you need to drive around installing MySQL in multiple places? can't you just install it once on a central server and have the clients connect to that? or at least do the installs over SSH. What exactly kind of a network is this?
I'd second the above, rolling your own DBMS isn't worth the bother. Would probably recommend PostgreSQL over MySQL though. Both are fairly easy to set up, MySQL especially so. It may be overkill but why does that matter, so long as it works?
Matt
Thursday, May 27, 2004
>> Pardon me if this is obvious, but why do you need to drive around installing MySQL in multiple places? can't you just install it once on a central server and have the clients connect to that? or at least do the installs over SSH
No. 50% of our customers do not have the Internet, and don't want it (and considering how much time and $ we are all spending cleaning up the mess, it's not that bad an idea.) And due to this, they usually use NetBeui as their network protocol on their LAN. And since some of them are even challenged by using the right button of their mouse, having them set up TCP/IP and run an installer of the host acting as server to install the MySQL server is science-fiction.
Hence, it's either a very simple deployment like SQLite, or no database.
>> It may be overkill but why does that matter, so long as it works?
Before driving around town with a Sherman tank, I wanted to check out if driving a Chevy with a seat belt might be good enough. Not all shops need 99,999% reliability, especially for small shops and small customers. Thanks anyway for the input.
Fred
Thursday, May 27, 2004
> Before driving around town with a Sherman tank, I wanted to check out if driving a Chevy with a seat belt might be good enough
A tank is easier to park.
*crunch*
Friday, May 28, 2004
Ah, I see. Bummer.
But still, if the tank is available for free, has the seatbelts ready-fitted, and isn't much harder to drive...
I think there's an embeddable version of MySQL if you look on their site. Perhaps for other open source DBMS too.
Matt
Friday, May 28, 2004
SQLite is open source, supports transactions, and only requires a single DLL to work. Hence my question about how to improve concurrency management without going all the way and use a c/s DBMS.
I gather that the oplock and read cache feature is buggy on older versions of Windows. I'll check if this is now working correctly, and we'll see if just relying on a on/off semaphore in the form of a file proves to be good enough for our needs in addition to daily backups.
Thank you
Fred
Friday, May 28, 2004
Older versions if Windows work fine with multi-user filesharing databases that rely to one degree or another on filesystem locks (most extend things beyond that a bit, though, e.g., Jet is based on filesystem locking but through "virtual locking" supports page-level and even record-level locks).
Access isn't the most robust system around, but that isn't just because it uses filesystem locking. Other fileserver databases that depend on filesystem locking are reliable with multiple users, millions of records and gigabytes of data, situations where Access/Jet would be overwhelmed. (See, e.g., the free Advantage Local Server -- http://resolution.extendedsystems.com/ADS/Product+Detail/Advantage+Local+Server/default.htm and DBISAM -- http://www.elevatesoft.com ).
If SQLLite doesn't work well with multiple users on old versions of Windows, then there's something wrong with SQLLite. Other fileserver databases with multiple users are reliable on old Windows versions.
Also, it just seems to me like a bad choice to go with SQL Lite at all if it doesn't already include code to transparently handle locking issues. Sounds to me like a situation where you're creating extra work (and thus spending extra money) just so you can go with an 'open source' solution. Jet sounds plenty robust enough to handle your limited needs, and it's not hard to upgrade JET to MSDE or SQL Server if that unlikely need arises (that's what MS wants so they've made it easy).
Herbert Sitz
Friday, May 28, 2004
Thx Herbert. I'll take a look at Advantage and DBIsam, although the latter apparently requires ODBC is accessed from VB.
It's not that I absolutely want something open-source. It's just that it's the only single-DLL SQL engine I found, hence my willingness to give it a shot.
Looks like one of our customers with 4-5 hosts is willing to act as guinea pig for a couple of weeks :-)
Fred.
Friday, May 28, 2004
I think you're right about DBISAM. Technically, I think the ODBC driver is ALL you would need, since for a fileserver database the DB Engine is actually built in to the ODBC driver. So you would just need to purchase the royalty free ODBC driver, include installation of it as part of your app installer, create DSN's in VB code (not hard I've done it before), and maybe use a tool like Access as a front end to administer your development database. Not sure whether the DBISAM admin tools are free, though if I recall correctly they are. You should check with DBISAM support and/or newsgroups for more info if this looks like a possibility.
I think the ideal solution for your problem would be to use Delphi and compile the DBISAM engine into each of the executables. You could probably have been done with the application in less time than it's taken to research the db issue. ;)
Another Delphi-related option would be to make use of the now-open-source FlashFiler db. That's a pretty capable fileserver db with some SQL capability. Was marketed by TurboPower, a major Delphi tool vendor that dropped their Delphi products and open sourced them. You can find FlashFiler now at SourceForge: http://sourceforge.net/projects/tpflashfiler/
There's a freely available ODBC driver for FlashFiler at the NexusDB website under "Bonuses" on their "Download" page: http://www.nexusdb.com .
If you stick with VB I really think best solution is just to use Jet. It's not as nearly as bad as people seem to have made you think. (Remember, Joel's own City Desk uses Jet as its DB for crying out loud.)
Herbert Sitz
Saturday, May 29, 2004
>> I think you're right about DBISAM. Technically, I think the ODBC driver is ALL you would need, since for a fileserver database the DB Engine is actually built in to the ODBC driver
... but it's already too much work since, if ODBC is involved, we'll more likely have to build an installer, and ask our customers to download and run it on every single PC on their network. Besides, if I'm not mistaken, ODBC = MDAC?
The first requirement is no install besides a couple of binaries that can easily piggy-back on one of our application EXE's thanks to PE Bundle. Besides, our customers have either 98, 98SE, 2K, XP Home/Family, so MDAC is... urgh.
Forget about Delphi. All our apps have been written in VB since it came out back in 1991. We're not going to rewrite them just to take advantage of a couple of good, stand-alone DBMS', especially since .Net came out and will require a rewrite in a 3-4 years anyway.
>> If you stick with VB I really think best solution is just to use Jet
But how to do you go from a bare PC to one that can connect to a Jet/Access database? Is MDAC installed and configured by default on all Windows hosts since 98? If not, it's no go.
I look forward to this mini-test at one of our customers', and see how our crude solution works out. Or not ;-) Thanks for the input.
Fred
Sunday, May 30, 2004
Building an installer for ODBC/MDAC and your .exe app using a simple tool like Innosetup would be a snap. Building concurrency/locking support into a db that doesn't have it isn't. If your locking needs are so crude that it's not much work at all to add them to SQLLite you may be fine. If not, Jet or another back end would be much better.
One other option could be to do the whole thing in Access/VBA and to build a runtime install with Office Developer that would include everything necesary, including the (royalty-free) runtime of MS Access. It results in a big install (maybe 60MB or so), but it sounds like you're deploying via CD anyway so maybe that wouldn't be a problem. Just another possibility.
Yeah, I didn't think switching to Delphi was a real possibility. Just wanted to get that little plug in. ;)
Herbert Sitz
Sunday, May 30, 2004
Try features of Daffodil DB
http://www.daffodildb.com
Naresh
Tuesday, July 20, 2004
 Recent Topics
Fog Creek Home
|
