Avoiding File Update Anomalies (Collisions) on a Server

[Bob Sneidar]

Before doing any inserts or updates use BEGIN TRANSACTION in an sql statement. When you are done issue COMMIT TRANSACTION, or if things seem to go awry in the process use ROLLBACK TRANSACTION. mySQL will not process two transactions simultaneously. It queue's them. Actually, mySQL queue's ALL writes, but transactions give you the opportunity to check things along the way as you update your tables, and then roll back the transaction if things don't pan out. 

Some things to consider, if the information your users are looking at needs to be up to date before doing any inserts or updates, then right after doing the BEGIN TRANSACTION, check the state of things. Some people use a locking table for each table. They store a logical value for a table before entering an edit mode, so that other users cannot open the table until that user is done. This is problematic though if it is possible that the user can open a table for editing and then walk away, or disconnect leaving the table in an uneditable state. 

If you want to allow viewing of records being edited by another user, you can employ a numerical value that you increment by one each time you edit a table. If prior to updating a table, the stored numerical value is not what it was when you opened the table for editing, then someone else got to it first. 

Alternately you can do this with the records in each table. Let's say you have 2 tables for an invoice. The master table contains information about the whole invoice. The detail table contains information about each line item for the invoice. You would only need a signature value in the master table, not each line item, as any update to the detail or the master table would be followed by a signature increment in the master table. 

If you use this method, make sure you check that the signature is not already maxed, and if it is, reset it to 0. You can get even fancier if you reserve the highest value the signature column can store as the "currently editing" flag. That way a user opening an invoice could be alerted that someone else was already editing the invoice and that the second user could only view but not edit it. 

You can alternately lock the tables in a database, and even lock the database itself. No updates can happen when you do this. It's the simplest way, but also leaves you vulnerable to a state where the database cannot be updated at all because a user crashed or pulled the plug in the middle of an update. If the updates are straight forward and bullet proof and quick, you could get away with this method. 

Bob


On Aug 22, 2011, at 8:05 AM, Gregory Lypny wrote:

> Hello everyone,
> 
> I would appreciate your thoughts on how to avoid collisions on a server on those presumably rare occasions when two or more visitors attempt to write to a file at exactly the same time.  In my case, it’s students writing via FTP to a text log file, but the same situation can occur if visitors to a web site, for example, submitting an entry to a guest book, do it at the same time.  I don’t know what the consequences of this are.
> 
> My crude and imperfect attempt to minimize this possibility is to have a Busy file that acts as a flag when the log file is being written to.  It contains just one line of data:
> 
> 	False, the seconds
> 
> If the first item is False, it means that the log file is not currently being written to.  A user about to write to the file invokes a handler that changes this first item to True, and inserts a timestamp in the form of the seconds.  When they are finished writing to the file, the same handler sets the Busy file to False and inserts a new timestamp.  The purpose of the timestamp is to cover for the possibility of the write process or the handler being aborted, therefore failing to set the Busy file back to False.  If the timestamp is more than, say, four minutes old, the True flag can be overridden.  This method is obviously imperfect because it is possible that two people might be writing to the Busy file at the same time!  I’m curious to know of other handler-based approaches that do not involve setting up separate databases.
> 
> 
> 
> Regards,
> 
> Gregory
> _______________________________________________
> 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