Using SQLite as a multi-user database?
pete at mollysrevenge.com
Tue Nov 23 16:07:03 EST 2010
SQLite has pretty powerful transaction locking capabilities that seem
like they should handle the circumstances you mentioned but I believe
you;re right that only one person can write to an SQLite database at a
a time - that's because it locks the whole database file not
individual tables or rows within tables.
On Nov 23, 2010, at 11:35 AM, Bob Sneidar wrote:
> I think for a small number of users, and a simple data structure, it
> is not much of a problem. The minimum requirement here is avoiding
> concurrent sessions into the same records. For instance, suppose
> user a has a customer record open for editing. You need to flag is
> so that if customer B comes along and tries to also edit it, he is
> locked out. Otherwise you risk user b overwriting anything user a
> saved without user a or user b knowing it happened. Trust me, our
> web guy got calls saying, "I know I edited that record, but now
> everything has reverted! Something is wrong with the database!"
> The simple way is to flag the record in such a way that it belongs
> to the first user who accessed it for write, and code to check for
> that before letting someone access a record for writing. Optionally
> you could have a "Last Edited By" and a date time stamp in your
> forms. For simple structures, that should be enough.
> But now what happens if user a opens a record for write, then blue
> screens or force quits your app? You have a stranded lock. You can
> flag the record with a date/time stamp and then ignore the lock
> after a certain period of time, and also time out the original user
> so that he cannot save his changes after a certain period of idle
> These are the things you ought to consider in a multi-user
> environment. A lot of people do not even bother with such things,
> thinking that the chances of any conflict is so rare that it is
> effectively moot. But in a complex app where a single session may
> update multiple tables containing data critical to your operations,
> it gets dicey. Let's say in an integrated accounting application,
> shipping has an order open and is processing it. Since you keep
> running totals in the customer record, that means you have to also
> update the customer record, your shipments file, the receivables
> files, the GL files etc.
> So in the middle of processing the order the shipping guy decides to
> go to lunch, leaving the shipping process open. If you simply use a
> lockout on all the effected files, no one else can modify those
> records. But now someone in Accounting wants to add to the order or
> cancel it because the customer called back, and cannot because the
> guy in Shipping locked it out. The point is, in a complex system,
> you cannot allow user interaction during a write session to the
> database, or you risk lockouts or even deadlocks. So simple record
> lockout mechanisms during edit sessions become problematic here.
> An old accounting app I used to work with used an incrementing 2
> digit column called Signature in all of it's critical tables. When
> an invoice was opened for editing, the signatures for the affected
> records in the master tables were stored, and nothing in any of the
> tables were modified yet. Another user could ALSO open it for
> editing, and he would also store the signatures. Now if user a
> cancels his edit, there is no problem, because nothing got updated.
> User b will never know, because just prior to the update where the
> files actually get changed, user b checks to make sure that the
> saved signature matches the stored signature.
> Now the first person to actually update the record sets the
> signature to the max value (as a flag to let other users know the
> record is locked for update), writes his data, then sets the
> signature to the saved value + 1 or if the saved signature is one
> less than the max resets to 0. So now, if user b tries to update
> anything, having checked the state of the signature column and found
> that not all the signatures were equal to their stored values, you
> have the choice of notifying them, then dumping them or else
> offering to update them with the current information.
> For shipping and invoice creation, there was a master OE and a
> master AR table. There were also detail tables, but those didn't
> need signatures, because you had to get past the master record to
> get to the detail. So all the shipping process had to do was set the
> signature to the max for the OE master, the AR master, and the
> customer record, only because they kept running totals in the
> customer file instead of calculating them on the fly.
> And the big thing was that during the update process, NO USER
> INTERACTION was allowed, because of the lockout issue. If an error
> occured, and left the records in an unfinished state, they had a
> maintenance application that would go back and clean things up. With
> SQL transactions, you can do rollbacks I suppose, but I don't know
> what the effect of a system hang or power outage would be if the
> changes were never committed.
> Seems complicated, but if you don't consider all of this in a
> complex system you risk data corruption. So in summary, it just
> depends on how complex your application is and how important the
> data is, how far you want to take all this. Sorry for the verbiage.
> On Nov 23, 2010, at 10:41 AM, Jonathan Lynch wrote:
>> I am in the same sort of boat.
>> I am making an application that would use a database file on a
>> shared drive.
>> Maybe 10-20 people would be writing to it during any given day. It
>> like Valentina would be a good choice for this, but, as silly as it
>> I don't have a practical funding mechanism to pay for the $200
>> I might try doing it with SQLite, but this conversation, and a few
>> conversations related to this topic, are making me nervous about
>> doing that.
>> 2010/11/23 Björnke von Gierke <bvg at mac.com>
>>> I think fredrik just uses installed programs and a sqlite file on
>>> a network
>>> share. Which is a smart way to circumvent "no server" rules, and
>>> seems to be able to do that.
>>> As for rev not being able to react to multiple clients on sockets,
>>> that is
>>> not how it works. if you do not use blocking wait, and/or have
>>> huge and long
>>> calculations, everything should work, for quite a few clients.
>>> the most chatrev-clients ever where about 25 (over the internet,
>>> not a
>>> private network), and it didn't even slow down at all, running
>>> from a 400
>>> mhz pentium II with 700 mb (or similar) of ram.
>>> I'd guess you can go into the low hundreds before needing to do
>>> things, as long as everyone can be served within a few
>>> milliseconds or so.
>>> On 23 Nov 2010, at 19:04, Ruslan Zasukhin wrote:
>>>>> Oh, and the reason I have to use SQLite is that I can't run any
>>>>> servers (the IT department would be.... annoyed to say the least).
>>>> Once again:
>>>> Fredrick thinks that IT department will not allow
>>>> ANY db server.
>>> official ChatRev page:
>>> http://bjoernke.com?target=chatrev <http://bjoernke.com/?target=chatrev
>>> Chat with other RunRev developers:
>>> go stack URL "http://bjoernke.com/chatrev/chatrev1.3b3.rev"
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>> Do all things with love
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
More information about the Use-livecode