Re: RFC: Preventing double-updates

["Chris Travers" <..hidden..>]

On 7/13/07, Ed W <..hidden..> wrote:
> > Any session based system I've used has the issue of stale sessions,
> > multiple sessions and browsers discovering 'old' sessions and using
> > them.
> >
>
> I think it should be much less prevelant than that if the system is
> implemented well.
>
> > The issue of timing out sessions is a tough one. There's one widely used
> > application still in use at VW and Toyota that logged everyone out at
> > 12am every day when it cleared it's session table.
>
> The error here is that the query should have been more like "DELETE from
> sessions WHERE last_updated < (NOW() - 1 hour)" - or whatever timeout
> you want to set.  I should imagine it's no different to the current
> login expiry and could presumably be tied to that anyway?

The idea here is that the query would be executed on page load and be
something like:
delete from sessions where last_updated < NOW() - (timeout || '
seconds')::interval;

> Also "locking" is usually a pain.  Much better to go for "optimistic
> locking" which means that two uses are allowed to have a go at updating
> or printing something, but one of them wins and the other gets returned
> an error.

In most cases, I would agree with you.  However, for longer
operations/workflows, the potential for lost productivity becomes
higher.  In particular, I am thinking of large batch processing, which
could hit, say, hundreds of vendors and thousands of invoices.

In particular, I know that Sage 500 implements a similar locking
system so that operators can run large jobs without being told at the
end "Sorry, somebody else beat you through the workflow."  Instead
they throw errors like "So and so has locked this transaction."
Unfortunately Sage can do this because the client application knows
which screen is currently open while we cannot without some issues
because web apps are stateless.

>  There are no locks to prevent either of them trying right up
> to the point one of the transactions fails.  This simplifies the
> interface by not needing an unlock interface for cases of lost sessions
> (my CC payment processor does this - you can get locked out of the
> system for 15 mins if you press links in the wrong order and hence loose
> your session, but the system still thinks you are logged in - grrr)

In our system, also your previous session (and hence all locks) are
destroyed when you log in again.  Though certainly session unlocking
is an issue.  Part of the problem is that there is not sufficient
information in the application to do this perfectly every time.  The
best we can do is time out sessions and take a damage mitigation
approach.  Trouble cases include:

I:  Session valid but locks are stale
 1)  User A starts to do a payment run
 2)  User A gets side tracked and starts to issue invoices
 3)  User B tries to take over the payment run.  Can't because the
locks are still in place and user A is still logged in with the same
valid session.

I beleive that the above case cannot be solved but could be mitigated
using a number of techniques ncluding:

1)  Provide user A an option to "cancel workflow" and hence release all locks.
2)  Provide user B an informative message that tells him or her to
contact user A.


II:  Session stale but not yet timed out
 A)  User A starts to do a payment run
 B)  User A steps away from desk.  Has family emergency, etc.  has to
leave and does not properly log out.
 C)  User B cannot do the payment run until user A's session times out.

This could be mitigated by:
1)  Removing user A's control over timeout period.
2)  Allowing an administrator to forceably log users out of the system.

In all cases, however, I believe that lost productivity is likely to
be lower than if 10-20 minutes go by before the error is raised (as
optimistic locking would allow).

Best Wishes,
Chris Travers