Using SQLite as a multi-user database?

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.

Pete Haworth

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  
> 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
>
>
> _______________________________________________
> 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
>