Re: Apache::Session::Store::Postgres FOR UPDATE problems [OT]

[Michael Schout <ms...@gkg.net>]

Jeffrey W. Baker wrote:

> It is possible that the FOR UPDATE is spurious.  It signals to the
> database system that this transaction intends to write that row.  With
> PostgreSQL's MVCC transaction isolation system, it's probably not
> necessary and may be causing problems.

It definately *is* necessary if you want to ensure that only one process
has access to your session data at a time.  MVCC will prevent a second
client from WRITING to the same row, but it will not block it from
reading the row.

In other words, MVCC will not prevent the following scenario:

client 1: SELECT * FROM sessions WHERE id='1';
client 2: SELECT * FROM sessions WHERE id='1';

# at this point, client 1 and client 2 both have copies of the session.
 suppose client 1 makes changes to the session data and saves it:

client 1: UPDATE sessions SET data='...' WHERE id='1';

now suppose client 2 makes changes:

client 2: UPDATE sessions SET data='...' WHERE id='1';

Whoops, you just wiped out the changes that client 1 made!

FOR UPDATE prevents this because it tells the database that you intend
to change the row.  The database will not let anyone else select that
row FOR UPDATE until you either issue a COMMIT or ROLLBACK.  So in
otherwords:

client 1: SELECT * FROM sessions WHERE id='1' FOR UPDATE;
client 2: SELECT * FROM sessions WHERE id='1' FOR UPDATE;

at this point, client 2 will block until client 1 either does COMMIT or
ROLLBACK.

So if you want to ensure that only one client has the session data at a
time, you need FOR UPDATE.

Regards,
Michael Schout

Re: Apache::Session::Store::Postgres FOR UPDATE problems [OT]

[Kjetil Kjernsmo <kj...@kjernsmo.net>]

On tirsdag 03 mai 2005, 00:40, Michael Schout wrote:
> Jeffrey W. Baker wrote:
> > It is possible that the FOR UPDATE is spurious.  It signals to the
> > database system that this transaction intends to write that row.
> >  With PostgreSQL's MVCC transaction isolation system, it's probably
> > not necessary and may be causing problems.
>
> It definately *is* necessary if you want to ensure that only one
> process has access to your session data at a time.  MVCC will prevent
> a second client from WRITING to the same row, but it will not block
> it from reading the row.

Thank you all for the follow-up! 

Indeed the problem here is that we're not destroying the object 
properly. It is the classical untie trap that we're in, described in 
the Camel Book. However, neither of us has sufficient understanding of 
the code, and of tied hashes, I suppose, to really track down the 
issue. There really isn't any $foo = \%session in there, but we're 
calling the handler from the invalidate method, which is most likely 
the source of the problem. 

It sort of works, with a few quirks, with DB_File as backend store, but 
unsurprisingly, it doesn't with Pg. So, for now, I have filed a bug 
against my own package, so that at least my users will have a fair 
chance to understand what's going on: 
http://rt.cpan.org/NoAuth/Bug.html?id=12473

If anyone wants to have a look, it is certainly appreciated, otherwise, 
we'll just have to return to it $later. 

Cheers,

Kjetil
-- 
Kjetil Kjernsmo
Programmer/Astrophysicist/Skeptic/Ski-orienteer/Orienteer/Mountaineer
kjetil@kjernsmo.net  webmaster@skepsis.no  editor@learn-orienteering.org
Homepage: http://www.kjetil.kjernsmo.net/        OpenPGP KeyID: 6A6A0BBC