Handling Multi Users (issuing invoices)

Question

Handling Multi Users

Requirements: I have an applications (mysql php jquery) where the users can:

1. Review records and update certain fields.
2. Issue invoices by selecting orders.

Issues: The issue is that an invoice should not be issued twice for the same time period. Also, a field should not be updated by two or more users at the same time.

Possible Solutions:

1. Lock the tables when they get updated, and if the user performs an action, notify and reload.
2. Impliment lock system, that when a user performs certain actions, it locks those actions to be performed by other users.
3. ...

Answer 1

Lookup 'optimistic locking' - basically means adding a version attribute and passing it back and incrementing it with updates to make sure nobody else got there first. If N users try same operation based on same version, one wins, others loose. It's fast simple easy for a wide variety of cases.

Answer 2

Don't know if this will help you or not but I'd first read about this in context of .Net's DataTable Adapter which tracks the changes made to the data rows since you read them and send back to db after changing. What it does is send all the fields instead of just the changed ones.

You can use time-stamps for the rows. Read the time stamp with other info and before saving check if the current time-stamp (of rows) is newer than what you have. This way you can minimize locking to just this portion, comparing time-stamps and updating if you are the first one to reach there.

Answer 3

Thank you both. Will look into both options: 1 optimistic locking (http://cwiki.apache.org/CAY/optimistic-locking-explained.html), and the time stamp approach.