Alex Rivera | Logout

How to properly implement a custom session persister in PHP + MySQL?

Asked 2009-06-20T20:01:39.317
11

I'm trying to implement a custom session persister in PHP + MySQL. Most of the stuff is trivial - create your DB table, make your read/write functions, call session_set_save_hander(), etc. There are even several tutorials out there that offer sample implementations for you. But somehow all these tutorials have conveniently overlooked one tiny detail about session persisters - locking. And now that's where the real fun starts!

I looked at the implementation of session_mysql PECL extension of PHP. That uses MySQL's functions get_lock() and release_lock(). Seems nice, but I don't like the way it's doing it. The lock is acquired in the read function, and released in the write function. But what if the write function never gets called? What if the script somehow crashes, but the MySQL connection stays open (due to pooling or something)? Or what if it the script enters a deadly deadlock?

I just had a problem where a script opened a session and then tried to flock() a file over an NFS share, while the other computer (that hosted the file) was also doing the same thing. The result was that the flock()-over-NFS call was blocking the script for about 30 seconds on each call. And it was in a loop of 20 iterations! Since that was an external operation, PHP's script timeouts didn't apply, and the session got locked for over 10 minutes every time this script was accessed. And, as luck would have it, this was the script that got polled by an AJAX shoutbox every 5 seconds... Major showstopper.

I already have some ideas on how to implement it in a better way, but I would really like to hear what other people suggest. I haven't had that much experience with PHP to know what subtle edge cases

php mysql session persistence
Edit
Report

2 Answers

2

I just wanted to add (and you may already know) that PHP's default session storage (which uses files) does lock the sessions files. Obviously using files for sessions has plenty of shortcomings which is probably why you are looking at a database solution.

answered 2009-07-22T17:01:24.140
1

Check with mysql_affected_rows() if the lock was obtained or not. If it was obtained - proceed. If not - re-attempt the operation every 0.5 seconds. If in 40 seconds the lock is still not obtained, throw an exception.

I see a problem in blocking script execution with this continual check for a lock. You're suggesting that PHP run for up to 40 seconds looking for this lock everytime the session is initialized (if I'm reading that correctly.)

Recommendation

If you have a clustered environment, I would highly recommend memcached. It supports a server/client relationship so all clustered instances can defer to the memcached server. It doesn't have locking issues you're fearful of, and is plenty fast. Quote from their page:

Regardless of what database you use (MS-SQL, Oracle, Postgres, MySQL-InnoDB, etc..), there's a lot of overhead in implementing ACID properties in a RDBMS, especially when disks are involved, which means queries are going to block. For databases that aren't ACID-compliant (like MySQL-MyISAM), that overhead doesn't exist, but reading threads block on the writing threads. memcached never blocks.

Otherwise, if you're still committed to an RDBMS session store (and worried that locking will become a problem), you could try some sort of sharding based on a sticky session identifier (grasping at straws here.) Knowing nothing else about your architecture, that's about as specific as I can get.

answered 2009-07-22T19:06:24.543

Your Answer