Using SQLite as a multi-user database?

Tue Nov 23 14:35:14 EST 2010

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 time. 

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. 

Bob

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 seems
> like Valentina would be a good choice for this, but, as silly as it sounds,
> I don't have a practical funding mechanism to pay for the $200 expense.
> 
> I might try doing it with SQLite, but this conversation, and a few previous
> 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 sqlite
>> 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 special
>> 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 database
>>>> 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:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> 
> 
> 
> -- 
> 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:
> http://lists.runrev.com/mailman/listinfo/use-livecode