warning & question about mysql sessions & concurrency

I've been trying to switch to MySQL-based session storage instead of the
native PHP session storage. In doing so, I've run into a lot of code on
the web that exhibits a serious flaw regarding concurrent requests from
the same client. All the code I've seen has glossed over the need to
lock the session for the duration of the request, e.g. not allow
concurrent requests to use it until it has finished. PHP's native
session handler correctly does this, but lots of MySQL-driven session
code does not.

Example timeline:
1. client sends a request
2. session data is loaded from the db
3. same client sends a request before the first one is finished (e.g.
frames, tabbed browsing, slow server response times, etc)
4. session data is again loaded from the db
5. the first request changes some values in $_SESSION
6. the second request changes some values in $_SESSION
7. the first request writes the data to the db and exits
8. the second request writes it's data (over that written by the first
request) and exits

PHP's native handler solves this problem by forcing concurrent requests
to wait for each other. The same thing needs to happen in a
database-driven session handler.

SO, does anyone have some code that uses MySQL to replace PHP's native
session storage that also correctly handles this concurrency problem?
Ideally I'd like to see just a set of functions that can be used with
sessions_set_save_handler() to transparently shift PHP's sessions to a
database, but I'm not going to use the stuff I've found on the web or
even in books (appendix D of O'Reilly's Web Database Applications with
PHP & MySQL publishes code with this problem).

Folks using database sessions who do not deal with this scenario be
warned! I'm surprised so much bad code is going around for this task...

Many thanks in advance,
Josh Whiting

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

Josh Whiting wrote:
> I've been trying to switch to MySQL-based session storage instead of the
> native PHP session storage. In doing so, I've run into a lot of code on
> the web that exhibits a serious flaw regarding concurrent requests from
> the same client. All the code I've seen has glossed over the need to
> lock the session for the duration of the request, e.g. not allow
> concurrent requests to use it until it has finished. PHP's native
> session handler correctly does this, but lots of MySQL-driven session
> code does not.

Neither do.

> Example timeline:
> 1. client sends a request
> 2. session data is loaded from the db
> 3. same client sends a request before the first one is finished (e.g.
> frames, tabbed browsing, slow server response times, etc)
> 4. session data is again loaded from the db
> 5. the first request changes some values in $_SESSION
> 6. the second request changes some values in $_SESSION
> 7. the first request writes the data to the db and exits
> 8. the second request writes it's data (over that written by the first
> request) and exits
>
> PHP's native handler solves this problem by forcing concurrent requests
> to wait for each other. The same thing needs to happen in a
> database-driven session handler.

No request should be blocking (i.e. wait for concurrent processes to
execute). This implies a poor understanding of transactional processing
from both yourself and PHP!

> SO, does anyone have some code that uses MySQL to replace PHP's native
> session storage that also correctly handles this concurrency problem?
> Ideally I'd like to see just a set of functions that can be used with
> sessions_set_save_handler() to transparently shift PHP's sessions to a
> database, but I'm not going to use the stuff I've found on the web or
> even in books (appendix D of O'Reilly's Web Database Applications with
> PHP & MySQL publishes code with this problem).

Forget it - rethink how your application is architected.

> Folks using database sessions who do not deal with this scenario be
> warned! I'm surprised so much bad code is going around for this task...

Agree.

Some guidelines, from my experience of developing time-critical
transactional systems in .Net (100 transactions/second type load).

- You usually only store some kind of identification for a user in the
session - no other data. doing otherwise is dangerous as there are
multiple copies of data floating around uncontrolled. A session-id is
enough information to store. Don't use the session for storing data
willy nilly - it is for identifying the session only - nothing else.
Can't say that enough. Don't use it for shortcutting code.

- If you want a proper transactional system, there are two ways to
handle concurrency:

1. Block until the transaction is committed - no good for performance
and scalability as you spend more time waiting than doing.

2. Fail commit on change. A sample solution: For each row, add an INT
which is incremented every time the data is updated (or use TIMESTAMP in
MySQL if you have to use it). Read the value with the data and send it
every time the data is saved. If the value is the same when it is
recieved, then consistency can be guaranteed so update the row, else
rollback the transaction (and tell the user someone else changed it
before them - reload the view and give them an opportunity to update it
again). This requires a good understanding of the DBMS you are using
and the principles around ACID compliant databases.

I'd personally recomment PostgreSQL over MySQL as MySQL is not truly
atomic and can't do transactions database-side (no server-side
programming) which makes it about as scalable as a brick. Small
updates/reads yeah but nothing much more complicated.

Personally, no-one in the PHP/MySQL arena tends to understand these
concepts as the two technologies are rarely used for pushing data around
on big systems (this is generally Java/Postgres' domain).

I ONLY use PHP/MySQL for knocking up quick web apps to fart out content
- nothing serious because it's simply not suited.

Cheers,

Chris Smith
Ninja Labs
http://www.ninjalabs.co.uk/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

On Sun, Mar 06, 2005 at 02:27:53PM +0000, Chris Smith wrote:
> Josh Whiting wrote:
> >I've been trying to switch to MySQL-based session storage instead of the
> >native PHP session storage. In doing so, I've run into a lot of code on
> >the web that exhibits a serious flaw regarding concurrent requests from
> >the same client. All the code I've seen has glossed over the need to
> >lock the session for the duration of the request, e.g. not allow
> >concurrent requests to use it until it has finished. PHP's native
> >session handler correctly does this, but lots of MySQL-driven session
> >code does not.
>
> Neither do.

PHP absolutely does. Take the following code:
session_start();
if (!isset($_SESSION['count'])) $_SESSION['count'] = 0;
$_SESSION['count'] = $_SESSION['count'] + 1;
sleep(5);
echo "

Current count:";
echo $_SESSION['count'];
echo "

Re: warning & question about mysql sessions & concurrency

Josh Whiting wrote:
> SO, does anyone have some code that uses MySQL to replace PHP's native
> session storage that also correctly handles this concurrency problem?

Create your MySQL session tables using ENGINE=innoDB (in older MySQL, use
TYPE=innoDB)
http://mysql.com can tell you lots more about innoDB

Then just wrap the contents of each function from the books in a BEGIN
query to make them be transactions... Err, no, I mean, start the
transaction in the one function and COMMIT in the save function.

That should take care of all the concurrency issues, I think.

> Folks using database sessions who do not deal with this scenario be
> warned! I'm surprised so much bad code is going around for this task...

Well, I don't use frames, or iFrames or any of that crap.

And web surfer users have been conditioned by really bad shopping carts to
not hit "Submit" twice :-)

I guess I'm just saying that in the real world, the race condition for a
single user/session just doesn't occur that often, and when it does, the
user generally recognizes the problem/error and accepts that they caused
it by being impatient or running two windows at once or whatever they did
that made it happen.

That doesn't make it Right, but it does make it Practical.

Note that innoDB will have some performance differences from myIsam:

myIsam is better/faster for all SELECT and INSERT applications with little
or no UPDATE/DELETE. This is because it's been optimized like crazy over
a long period of time by the MySQL folks, but it has to lock the whole
damn table on UPDATE/DELETE (but not INSERT) in order to preserve
integrity. It's also less susceptible to corruption on power loss, due to
the way the optimization and INSERT work.

innoDB will win if you have much UPDATE/DELETE because it does row-level
locking and provides transaction support. Get a UPS if you want to avoid
corruption on power loss.

--
Like Music?
http://l-i-e.com/artists.htm

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

On Tue, Mar 08, 2005 at 10:38:28AM -0800, Richard Lynch wrote:
> Josh Whiting wrote:
> > SO, does anyone have some code that uses MySQL to replace PHP's native
> > session storage that also correctly handles this concurrency problem?
>
> Create your MySQL session tables using ENGINE=innoDB (in older MySQL, use
> TYPE=innoDB)
> http://mysql.com can tell you lots more about innoDB
>
> Then just wrap the contents of each function from the books in a BEGIN
> query to make them be transactions... Err, no, I mean, start the
> transaction in the one function and COMMIT in the save function.
>
> That should take care of all the concurrency issues, I think.

Agreed, initially I thought of that but I also need to use transactions
in my business logic and MySQL doesn't support nested transactions, so
I'd have to open a separate connection to the DB to handle the session
transaction while another connection handles the business
transaction(s). I'm hoping to find a solution that uses locking in the
application level instead of the database. Were I using a DB that
supported nested transactions, it would be a different story. maybe
it's time to switch databases.

> I guess I'm just saying that in the real world, the race condition for a
> single user/session just doesn't occur that often, and when it does, the
> user generally recognizes the problem/error and accepts that they caused
> it by being impatient or running two windows at once or whatever they did
> that made it happen.
>
> That doesn't make it Right, but it does make it Practical.

Point taken. I guess it's almost more of a pyschological thing for me
as a programmer - the idea of writing code vulnerable to race conditions
just doesn't sit well with me.

on the other hand, with the growing popularity of tabbed browsers, and
of coures the frames issue, i think it is reasonable to demand proper
behavior during concurrent requests, and while you may not be using
frames, lots of sites do, and that's a setup for a real headache.

thanks
/josh

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

> Agreed, initially I thought of that but I also need to use transactions
> in my business logic and MySQL doesn't support nested transactions, so
> I'd have to open a separate connection to the DB to handle the session
> transaction while another connection handles the business
> transaction(s). I'm hoping to find a solution that uses locking in the
> application level instead of the database. Were I using a DB that
> supported nested transactions, it would be a different story. maybe
> it's time to switch databases.

Since the data only changes when you write it, at the end of the script,
you could maybe get away with the transaction only being in the
session_save handler, and be sure to rollback or commit your business
logic before that.

That would for sure take a lot of discipline, and might even be downright
impossible for what you need, but it's worth pondering.

--
Like Music?
http://l-i-e.com/artists.htm

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

On Wed, Mar 09, 2005 at 02:52:52PM -0800, Richard Lynch wrote:
> > Agreed, initially I thought of that but I also need to use transactions
> > in my business logic and MySQL doesn't support nested transactions, so
> > I'd have to open a separate connection to the DB to handle the session
> > transaction while another connection handles the business
> > transaction(s). I'm hoping to find a solution that uses locking in the
> > application level instead of the database. Were I using a DB that
> > supported nested transactions, it would be a different story. maybe
> > it's time to switch databases.
>
> Since the data only changes when you write it, at the end of the script,
> you could maybe get away with the transaction only being in the
> session_save handler, and be sure to rollback or commit your business
> logic before that.
>
> That would for sure take a lot of discipline, and might even be downright
> impossible for what you need, but it's worth pondering.

well the trouble is not in the writing at the end of the request, which
would likely only be a single query and therefore not need a transaction
in itself. the trouble is the lack of locking out other requests from
reading the data when you're using it during the main body of
the request.

so... no luck finding a concurrency-aware database session handler?

i'm going to try to roll my own, and i'll certainly share what i come up
with on the list.

thanks though, for the help up to this point!

/jw

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

> On Wed, Mar 09, 2005 at 02:52:52PM -0800, Richard Lynch wrote:
>> > Agreed, initially I thought of that but I also need to use
>> transactions
>> > in my business logic and MySQL doesn't support nested transactions, so
>> > I'd have to open a separate connection to the DB to handle the session
>> > transaction while another connection handles the business
>> > transaction(s). I'm hoping to find a solution that uses locking in
>> the
>> > application level instead of the database. Were I using a DB that
>> > supported nested transactions, it would be a different story. maybe
>> > it's time to switch databases.
>>
>> Since the data only changes when you write it, at the end of the script,
>> you could maybe get away with the transaction only being in the
>> session_save handler, and be sure to rollback or commit your business
>> logic before that.
>>
>> That would for sure take a lot of discipline, and might even be
>> downright
>> impossible for what you need, but it's worth pondering.
>
> well the trouble is not in the writing at the end of the request, which
> would likely only be a single query and therefore not need a transaction
> in itself. the trouble is the lack of locking out other requests from
> reading the data when you're using it during the main body of
> the request.

But they other scripts are going to read their session data from the
database, which is the way the world "is" at that point in time.

More importantly, if you really lock it for the whole duration of the
script, then youre frames/iFrames/whatever are gonna get real slow and
clunky.

Each frame will have to wait for the other frame to finish before it can
read anything.

Ugh!

You'd really be better off designing your application so that some frames
are "read-only" and don't alter data, and catch the error condition if two
write-frames try to write out of sequence.

I guess I'm saying that if you have to worry about this, the analysis and
correction belongs in the business logic layer, not the session handling
layer, in order to make iframes and tabbed multi-user useful.

Or you can just "lock" the user into one iframe/tab at a time, and not
allow them to open up a second session, by invalidating the previous
session (frame/tab) when they open one up.

Short Version: Either your frames/tabs are gonna be so clunky as to be
useless, or you need to lock them into only one, or you need business
logic, not session-logic. Solving the race condition at session layer
will only make your application un-responsive.

This may go a long way to explaining why you're not finding any readily
available solutions out there at the session layer.

--
Like Music?
http://l-i-e.com/artists.htm

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

On Fri, Mar 11, 2005 at 09:57:46AM -0800, Richard Lynch wrote:
> > well the trouble is not in the writing at the end of the request, which
> > would likely only be a single query and therefore not need a transaction
> > in itself. the trouble is the lack of locking out other requests from
> > reading the data when you're using it during the main body of
> > the request.
[...]
> Short Version: Either your frames/tabs are gonna be so clunky as to be
> useless, or you need to lock them into only one, or you need business
> logic, not session-logic. Solving the race condition at session layer
> will only make your application un-responsive.
>
> This may go a long way to explaining why you're not finding any readily
> available solutions out there at the session layer.

You raise good points. However, my goal is simply to have a
transparent, database-driven replacement for PHP's native session
handler. I don't want to change my application logic because I'm
switching session handlers - my application shouldn't care! And so,
transparently reproducing PHP's session layer functionality is going to
mean locking concurrent requests out, because that is what PHP's session
handler already does (for good reason IMHO).

you're argument about slow frames is valid, but is ALSO applicable to
PHP's native session handler, e.g. i'm not introducing a new problem.
the trouble can be mostly avoided in either case by not starting session
in frames that don't need it (most of them), and doing any session stuff
right away and calling session_write_close() ASAP to free up the lock so
other frames can run.

you're right that i could change my application logic to deal safely
with concurrent requests from the same session, but PHP already nicely
defeats the issue so you don't have to worry about it, and at a more or
less negligable performance loss if you design the scripts to reduce
session open/locked time to the bare minimum...

i would speculate that the reason i haven't found a readily available
solution is because most folks either (1) don't understand the problem,
or (2) don't think such a race condition is likely enough to warrant a
fix, or (3) are using a different backend (oracle, etc.) that makes the
problem easy to solve. the PHP team, however, clearly thought it was
important enough as is evident by their session handler design.

....have i convinced you yet of the worthiness of this endeavor? :)

/jw

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

session_write_close() is also nice in that freeing the lock doesn't
destroy your existing copy of the session variables, you can still use
them in the rest of your script just remember that they exist in your
current script as they were when you called session_start(), even if
some other script modifies the session, you won't know until your next
call to session_start().

Be careful using data from an unlocked session to to modify that data
later on, as another instance can modify the session in between. Only
release the lock when you really don't care if it's modified
elsewhere.



On Sat, 12 Mar 2005 14:50:44 -0600, Josh Whiting wrote:
> On Fri, Mar 11, 2005 at 09:57:46AM -0800, Richard Lynch wrote:
> > > well the trouble is not in the writing at the end of the request, which
> > > would likely only be a single query and therefore not need a transaction
> > > in itself. the trouble is the lack of locking out other requests from
> > > reading the data when you're using it during the main body of
> > > the request.
> [...]
> > Short Version: Either your frames/tabs are gonna be so clunky as to be
> > useless, or you need to lock them into only one, or you need business
> > logic, not session-logic. Solving the race condition at session layer
> > will only make your application un-responsive.
> >
> > This may go a long way to explaining why you're not finding any readily
> > available solutions out there at the session layer.
>
> You raise good points. However, my goal is simply to have a
> transparent, database-driven replacement for PHP's native session
> handler. I don't want to change my application logic because I'm
> switching session handlers - my application shouldn't care! And so,
> transparently reproducing PHP's session layer functionality is going to
> mean locking concurrent requests out, because that is what PHP's session
> handler already does (for good reason IMHO).
>
> you're argument about slow frames is valid, but is ALSO applicable to
> PHP's native session handler, e.g. i'm not introducing a new problem.
> the trouble can be mostly avoided in either case by not starting session
> in frames that don't need it (most of them), and doing any session stuff
> right away and calling session_write_close() ASAP to free up the lock so
> other frames can run.
>
> you're right that i could change my application logic to deal safely
> with concurrent requests from the same session, but PHP already nicely
> defeats the issue so you don't have to worry about it, and at a more or
> less negligable performance loss if you design the scripts to reduce
> session open/locked time to the bare minimum...
>
> i would speculate that the reason i haven't found a readily available
> solution is because most folks either (1) don't understand the problem,
> or (2) don't think such a race condition is likely enough to warrant a
> fix, or (3) are using a different backend (oracle, etc.) that makes the
> problem easy to solve. the PHP team, however, clearly thought it was
> important enough as is evident by their session handler design.
>
> ...have i convinced you yet of the worthiness of this endeavor? :)
>
> /jw
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

Josh Whiting wrote:
> I've been trying to switch to MySQL-based session storage instead of the
> native PHP session storage. In doing so, I've run into a lot of code on
> the web that exhibits a serious flaw regarding concurrent requests from
> the same client. All the code I've seen has glossed over the need to
> lock the session for the duration of the request, e.g. not allow
> concurrent requests to use it until it has finished. PHP's native
> session handler correctly does this, but lots of MySQL-driven session
> code does not.
>
> Example timeline:
> 1. client sends a request
> 2. session data is loaded from the db
> 3. same client sends a request before the first one is finished (e.g.
> frames, tabbed browsing, slow server response times, etc)
> 4. session data is again loaded from the db
> 5. the first request changes some values in $_SESSION
> 6. the second request changes some values in $_SESSION
> 7. the first request writes the data to the db and exits
> 8. the second request writes it's data (over that written by the first
> request) and exits
>
> PHP's native handler solves this problem by forcing concurrent requests
> to wait for each other. The same thing needs to happen in a
> database-driven session handler.
>
> SO, does anyone have some code that uses MySQL to replace PHP's native
> session storage that also correctly handles this concurrency problem?
> Ideally I'd like to see just a set of functions that can be used with
> sessions_set_save_handler() to transparently shift PHP's sessions to a
> database, but I'm not going to use the stuff I've found on the web or
> even in books (appendix D of O'Reilly's Web Database Applications with
> PHP & MySQL publishes code with this problem).
>
> Folks using database sessions who do not deal with this scenario be
> warned! I'm surprised so much bad code is going around for this task...

MySQL's InnoDB supports row-level locking. So row the right row in
session_start and release it in session_close function.

In MyISAM you can use application-level locking: GET_LOCK() and
RELEASE_LOCK() in session_start and session_close, respectively.
Parameter would be session id with some prefix.

The problem is when you need to create session id - you must lock the
whole table to find unused session id and insert it into table.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: warning & question about mysql sessions & concurrency

On Wed, Mar 16, 2005 at 06:59:43PM +0100, Marek Kilimajer wrote:
> >SO, does anyone have some code that uses MySQL to replace PHP's native
> >session storage that also correctly handles this concurrency problem?
> >Ideally I'd like to see just a set of functions that can be used with
> >sessions_set_save_handler() to transparently shift PHP's sessions to a
> >database, but I'm not going to use the stuff I've found on the web or
> >even in books (appendix D of O'Reilly's Web Database Applications with
> >PHP & MySQL publishes code with this problem).

>
> MySQL's InnoDB supports row-level locking. So row the right row in
> session_start and release it in session_close function.

But mysql does not support nested transactions. Locking the row means
that your entire script has to happen inside a single transaction.
Since my application logic requires transactions as well, it would mean
using two separate connections to MySQL, one for the session transaction
and one for business transactions, and that means twice the connection
overhead.

> In MyISAM you can use application-level locking: GET_LOCK() and
> RELEASE_LOCK() in session_start and session_close, respectively.
> Parameter would be session id with some prefix.

AH! GET_LOCK() - that would do the trick! I didn't realize MySQL
supported locking mechanisms independent of transactions/tables etc.

> The problem is when you need to create session id - you must lock the
> whole table to find unused session id and insert it into table.

Hmm... couldn't I just do an 'insert ignore' with the session id every
time to kick off the process? Then MySQL would handle checking for an
existing row and create an empty one if it didn't exist. A very stripped
down example:

To open a session:
1. insert ignore into sessions (id,content) values ($sess_id,'')
2. select get_lock('my_prefix_".$sess_id, 15)
3. if NULL returned then abort, otherwise lock is aquired OK
4. select * from sessions where id=$sess_id

Do stuff ...

To close it:
1. update sessions set content='...'
2. select release_lock("my_prefix_".$sess_id)

I don't have all the details covered yet but I think that is just
what I needed. Thanks!!

/Josh W

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php