A bit OT: handling multiple users in DB

[Bob Sneidar]

I use a technique I learned from a well known dBase application called  
SBT. I add a numeric signature field (usually 2 digits) to every  
master table (defined as any table that is always updated whenever a  
transaction occurs). When a user opens a record for the purposes of  
editing, I get the value of the signature field for that record. If  
it's the highest value the field can contain, then someone else is in  
the middle of saving a transaction and has it locked for editing.  
(More on that later) But if it is NOT the highest value the field can  
hold, then I read the data for that record into variables. (Variables  
are nice because you can compare the values in the fields to their  
original values to see what changed).

Now when the user tries to save the transaction, I get the value of  
the signature field again. If it is the same as the starting value,  
then I know no one has made any changes since I first read the data  
from the record. I then set the signature to 99 (or the highest value  
the field can contain). That is my record lock, so to speak. Because I  
check for this when I go to get the data, I effectively lock the  
record for that user. That way if someone comes along in the middle of  
a transaction update and tries to get the data, they are prevented by  
my own code until the other user's transaction is finished. I then  
update the record, and set the signature to the starting value + 1,  
freeing it up for future reads/updates.

BUT if the initial signature and the present value is NOT the same  
then someone has updated the record in while I was editing it. At that  
point, depending on your application, you can present the user with a  
choice to cancel, or update their current information with the saved  
information and proceed as normal.

A few caveats here. First, you may ask, why not just lock the record  
when the user reads the data in and keep it that way until the user  
saves or cancels the update? Okay, now the user leaves for the day, or  
crashes to desktop in the middle of an edit. The record remains locked  
forever, unless you run some maintenance script to unlock all records.  
Boo!

Next, if you use loops to retry record access in the event the record  
is currently locked, you will not want to use endless loops as you can  
conceivably attain deadlock. Set a maximum number of times the loop  
will retry before it gives up and alerts the user.

Consider also, that from the time the user clicks Save until the time  
the transaction is saved in all your tables, the application cannot  
for any reason stop, as in getting input from the user, because again,  
you can attain deadlock if the user fails to respond, or something  
crashes.

Also, if there are many kinds of transactions which involve different  
sets of master tables, it is highly advisable to write a function  
which rechecks the signatures in all the affected tables and attempts  
to lock the records, before proceeding with any updates to any of the  
records. You don't want to be in the middle of saving a transaction  
only to find halfway in that someone posted changes to one of the  
records you needed to update. Lockdown every record that needs to be  
updated before proceeding with the transaction.

Finally, a maintenance script to "unlock" all records in all tables is  
a must, because eventually something will go wrong in the middle of a  
transaction and strand records, preventing them from being updated.

This all sounds like a lot of trouble I know, and for simple  
applications, one table to keep track of records that are allocated to  
a particular user is better, assuming you have a way of uniquely  
defining each connection. (Remember you may be able to login to your  
app with the same credentials from 2 or more workstations but they are  
separate connections nonetheless). But for a complex application, like  
for an integrated accounting solution, I think the signature method  
really works best. There is no need to track logged in users and deal  
with unexpected disruptions or duplicate credentials from different  
workstations.

Also I would avoid any automatic record locking methods provided by  
the database mechanism you use, as these are not always reliable, and  
do not really deal with the problem of simultaneous multi-table  
transactions from multiple users. They only prevent data corruption  
from occurring by preventing simultaneous writes to a single table or  
database.

Sorry for the lengthy post.

Bob Sneidar
IT Manager
Logos Management
Calvary Chapel CM

On Sep 22, 2008, at 2:46 AM, Klaus Major wrote:

> Hi all,
>
> this is a bit off topic, but maybe someone can give me some helpful
> hints.
>
> Is someone of you working with databases with multiple users?
> If yes, how do you handle "concurrent transactions"?
>
> I mean how do you solve the possible overwriting of data when both
> users work with
> cursors and are allowed to  update data and how do you update a cursor
> (on the fly?)
> when its data may have been updated?
>
> Know what I mean?
>
> I searched the net and found some hints, but maybe you have some
> really clever
> solutions that you are willing to share :-)
>
> Thanks in advance!
>
>
> Best
>
> Klaus Major
> klaus at major-k.de
> http://www.major-k.de
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution