PHP Sessions – Files vs Database Based


One may think changing PHP session handler from file based to database driven is fully transparent. In many cases it is, sometimes however it may cause some unexpected problems as happened to one of our customers.

If you use file based sessions PHP will lock session file for whole script execution duration, which means all requests from the same sessions will be serialized on PHP level, which means they also will be serialized for single user on database level. If you change to store PHP sessions in MySQL instead this effect may be no more true and you may have number of requests executing for the same session at the same time. First of course means you may have your session data damaged because you will have lost session variables update from one of the script, in addition however you may run into database related issues of modifying user profile or other user/session related data in parallel, if you do not use transactions or lock tables.

So how you can get back your old file based session behavior with MySQL Sessions ?

If you have dedicated connection to session database and use Innodb tables for your session storage you can start transaction on the session start and use SELECT … FOR UPDATE to lock the session row in the session table for whole request length. On the end of the session the same row is updated and transaction is committed.

If you share session connection with other modules or do not use transactional tables for session you can use GET_LOCK to get same behavior. In the start of the session you can do SELECT GET_LOCK(‘‘,10) and in in the end of the request
SELECT RELEASE_LOCK(‘‘) where session_id is current session identifier. Note – setting this external lock on session name should be done before session data is read from database for things to work properly.

This approach assumes you do not use GET_LOCK in other places in your application as as soon as it is called second time previous lock is automatically released. The good thing about it however – you can use it as an extra to your current MySQL Sessions system without need to change how it works internally. If you do not use persistent connections you even do not have to release lock – as soon as connection is closed the lock is automatically released.

The value 10 in GET_LOCK is timeout in seconds – if lock can’t be granted for this amount of time it will return “0” indicating lock was not granted in this case you can select to continue without session or may do something else, like logging error as this generally should not happen in well tuned applications.


Share this post

Comments (42)

  • dvh Reply

    I used MySQL for user identification since my first day in php development because it felt natural thing to do. Why should I use PHP sessions instead? Is there any advantage?

    March 27, 2007 at 12:00 am
  • pathan sms Reply

    Hi . Hello i am enjoying.

    March 27, 2007 at 12:00 am
  • pathan sms Reply

    On a Compaq Pressario C714NR notebook pc and have everythingworking. This is the first version ofunbubtu that I have been able to figure out how to make everything work. Thanks for the list I have been wanting to try wine but never liked any version of linux enough to stick with it for more than a few weeks until now.

    March 27, 2007 at 12:00 am
  • pathan sms Reply


    This is what i was looking for my new site.

    Simple setting in php.ini file

    March 27, 2007 at 12:00 am
  • » PHP Sessions - MySQL -> Datei basiert Reply

    […] wo die Vor-/Nachteile von Datei bzw. Datenbank basierten Sessions liegt sollte mal einen Blick ins MySQL Performance Blog werfen, hier findet sich ein wirklich guter Tipp wie man möglichst unproblematisch von […]

    March 27, 2007 at 11:13 am
  • bobby Reply

    While it’s a completely different approach, you can also store sessions within memcached by djanga. Then you would not need to worry about the writes and table locking.

    March 27, 2007 at 12:07 pm
  • Dmitry Reply

    yeah… and if site gets slow for any reason and/or visitor gets impatient, you can always run SHOW PROCESSLIST on the session server and watch hundreds of such threads (several for the same session id), waiting for each other… 😉

    March 27, 2007 at 12:15 pm
  • Rasmus Reply

    Thank you so much for that information. I’ve been looking for a solution to that problem for several months! Also, thank you for a great blog!

    March 27, 2007 at 2:00 pm
  • peter Reply


    Just curious would not memcached session have the same problem as MySQL sessions in terms of locking ?

    In general there are many ways to store sessions, including MySQL Cluster – my point is not to advertise one approach over another but simply to show there are differences which you might not think about

    March 27, 2007 at 2:05 pm
  • peter Reply


    SELECT * FROM SESSIONS WHERE SESSION_ID=”aabbcc” FOUR UPDATE; will only lock that row if it is primary key.

    So you should have no more process waiting on the same session as you would have processes waiting on the same file in case of file based PHP sessions.

    If you have high concurrency within single session it of course does not work well but in this case plain MySQL sessions based session storage may not work for you either because there will be large chance to loose session variables updates.

    March 27, 2007 at 2:08 pm
  • catalinux Reply


    I use MySQL session for over 7 years (when php3 was at it’s start) and my sessions ids are md5 of special tokens. I never had problems with mysql bottleneck and it helped me a lot when cluster websites needed authentication.

    Also I used memcached sessions but you do not have same flexibility as MySQL (but u do have performance).

    I never user mmsessions altough they say that are better than file sessions.

    March 27, 2007 at 2:24 pm
  • Dmitry Reply

    Everything is fine with MySQL itself 🙂
    It’s just my experience with an application design problem – when PHP issues locking select for the session right at the beginning and releases (if finishes) at the end. So if it takes several seconds to run, user can start “jumping” around, sending more and more “select for update” for the same id (PK), which results in a nice bunch of threads on a sessiondb server, and if others do the same, the situation I described occurs… then the session purgers kicks in, and I get called 🙂

    March 27, 2007 at 2:57 pm
  • peter Reply


    I think you miss the point. Consider the case when you had 1 Web box and file sessions and it can’t keep up so you’re moving to several Web boxes but you’d like to avoid having sessions set up on NFS file system. So you end up storing them in database and your application breaks.

    Now the question is why it may break and how to make it fixed easiest way – this is what it is all about.

    If you have reloads happening because of slow page loads surely you should fix these first. You also can consider killing previous page view queries in some cases.

    Regarding session purge – this is another thing which is often implemented wrong way – you can make them to be very gentle in terms of locking – locking one row at the time, plus if you have index on (last_active) column the delete should only traverse rows which were not active for a while and so chance they are active now is pretty small.

    March 27, 2007 at 3:35 pm
  • Jakub Vrána Reply

    “PHP will lock session file for whole script execution duration”

    If you know that the script will not need to write anything into the session any more, it is possible to call session_write_close(). Reading is still possible though.

    March 28, 2007 at 12:36 am
  • peter Reply


    Thank you for your comment good point. I’m mainly describing default behavior here, while it would be of course good idea to do all session changes as early as possible and “unlock” the session.

    March 28, 2007 at 1:13 am
  • Dmitry Reply

    “you should fix these first” – sure 🙂
    Peter, I just wanted to share some of my real-world experience – a possible side-effect of proposed strategy.

    March 28, 2007 at 7:48 am
  • Martin Tsachev Reply

    I use same connection for sessions/data with InnoDB but before I register a shutdown function which explicitly rolls back any pending transactions and writes the session data to db. It works without any issues, I wasn’t aware of the concurrent session use issue though.

    I use a session table with a varchar primary key that PHP uses as the identifier but I was thinking about switching to a longint one and using that as a foreign key. I might need to prevent some sessions from being deleted for an extended period of time in the future. Would that force me to execute 2 queries with LOCK FOR UPDATE to make use for row level locking though? First SELECT id… WHERE name = ‘abcd’, and then SELECT… WHERE id = ? LOCK FOR UPDATE?

    March 28, 2007 at 12:13 pm
  • Aaron Kalsnes Reply

    We store our PHP sessions (and everything else we can) in Memcached.

    I would HIGHLY recommend it.

    March 28, 2007 at 3:03 pm
  • Notícias e links - 05/04 « PHP-BR Reply

    […] PHP Sessions – Files vs Database Based Dicas para utilização de mysql como “session handler”. Notei um dia desses que php 5.2 (não sei se outras versões também) é compilado com suporte a sqlite por padrão e com isso, suporte a sqlite como “session handler”. Verei algum esquema em breve para compartilhar sessão entre servidores futuramente. […]

    April 5, 2007 at 9:01 am
  • Martin Tsachev Reply

    Sorry, I noticed how stupid my question is. SELECT… FOR UPDATE doesn’t play nice across transactions, while GET_LOCK() does. Unfortunately you can use only 1 GET_LOCK() per thread.

    April 13, 2007 at 9:36 am
  • hamish Reply

    I needed to share session data across multiple web servers – so I decided to store in mysql.
    All sorts of problems arose – locking, queries taking ages, etc (really high load site).
    So I took Aaron’s advice to use memcached and it works fantastically. I also highly recommend it over over mysql for storing sessions!

    April 16, 2007 at 8:18 pm
  • Heather Reply

    What is the difference between file based applications and database systems and the pros and cons?

    May 13, 2007 at 12:51 am
  • Manish Pandey Reply

    Well of course sessions are more suitable in database than in files.

    August 14, 2007 at 3:54 pm
  • madhuka Reply

    What is main concept of Seassion in PHP

    August 15, 2007 at 3:18 am
  • PHP Sessions Reply

    For security purpose i also to save sessions in database rather than using flat files. Thou you would delete the file when you delete your sessions, but the file will remains readable by any script or human.

    September 20, 2007 at 8:32 pm
  • Learn PHP Reply

    Wow! i just read about memcached in post made by some earlier. I need to read up on memcached for caching and even storing sessions an d cookies

    September 20, 2007 at 8:35 pm
  • bash Reply

    Yes, memcached is great, but it is a cache nonetheless that is stored in memory. In case you run out of memory for the memcached daemon, your session data might end up getting garbage collected. I personally recommend using an memcached session handler backed by a mysql database.

    October 24, 2007 at 12:03 pm
  • Pramod Reply


    Looks like a great article as i want hunting for sharing the same sessions over subdomains.

    (Pramod Mane)

    July 17, 2008 at 9:13 am
  • Pramod Mane Reply


    This is what i was looking for my new site.

    Simple setting in php.ini file

    July 17, 2008 at 9:18 am
  • Worse than DDOS | MySQL Performance Blog Reply

    […] of PHP sessions “files” handler I already wrote about this topic, but when troubleshooting this all takes another angle. Default file handler means […]

    August 18, 2008 at 11:55 pm
  • myth-genius Reply

    session store memcache is a problem :count sessions ?????how to?????

    January 8, 2009 at 1:19 am
  • iko Reply

    Only consistent way how to count users online, if sessions are stored in memcached is
    to have your own session handler and implement full database backup, so new sessions are also created in db as references and cleaned standard way if memcache session is destroyed in runtime or by garbage collector. Under these conditions you can use standard queries to retrieve number of users online and other usefull stats that are problematic to get from MC.
    You can also create file based backup with simple counter (tried it myself and works), depends what level of flexibility and possible statistics you want to have.

    Another approach is to create backend collector that fetches content of MC server and stores results in MC object. This is however against MC nature, so i would stick with DB or file backup.

    March 27, 2009 at 5:42 am
  • PHP Sessions - Files vs Database Based | MySQL Performance Blog Reply

    […] PHP Sessions – Files vs Database Based | MySQL Performance Blog Share and […]

    June 6, 2009 at 4:59 am
  • Digital Lynx Reply

    Hey, nice article. I’ve tried doing something similar in a project of mine using something called mSess from:

    It doesn’t implement a custom session handler, but it works in a similar manner, and has the added benefit of being able to work with traditional PHP sessions at the same time. Fairly easy to use if you use the sample files as a reference.

    It’s a CC based license so it’s flexible to incorporate.

    June 28, 2009 at 7:20 am
  • koshker Reply

    I cant find function GET_LOCK on 🙁

    September 11, 2010 at 1:13 pm
  • peter Reply


    This is MySQL function not PHP

    September 13, 2010 at 9:04 am
  • Seo Lahore Reply

    Just installed unbuntu 9.04 on a Compaq Pressario C714NR notebook pc and have everything working. This is the first version of unbubtu that I have been able to figure out how to make everything work. Thanks for the list I have been wanting to try wine but never liked any version of linux enough to stick with it for more than a few weeks until now.

    February 12, 2011 at 9:38 am
  • Stefan Reply

    With suggested mysql backed memcached as session handler, there still exists problem with concurrency between simultaneous requests overwriting each others $_SESSION variable. On a multi-frontend environment client’s rapid consecutive ajax requests might end to different frontends accessing and modifying $_SESSION’s contents at same time. And when these frontends push their data back to shared memcached, last one overwrites all previous changes.

    The problem is not actually with memcached or mysql, but on using the $_SESSION that can be operated only as a single entity. It’s good for reliably locked simple single server installation, but not for distributed environments.

    I would recommending scache’s approach ( ) to completely drop using the generic session handler and instead split data on small enough fragments to reduce risk of threads overwriting each others data.

    April 10, 2012 at 1:19 am
  • Mahesh Yadav Reply

    Can anyone let me know, which one is better if I have dedicated hosting and also which one is faster in performance ?

    September 12, 2013 at 7:24 am
  • Angel Genchev Reply

    Does anybody tested/benchmark-ed the performance of PHP script using file-based vs InnoDB-based session handlers ?
    Here I found an implementation of MySQL-based session save handler:

    June 7, 2015 at 7:38 am
  • Vincent W Reply

    Beware that storing the session in the database can be a huge performance hit if you’re using the session a lot and have high traffic.

    It’s great if you’re only storing authentication and userid for example, but if you’re using it to remember state and parameters for every page, then a high number of users will quickly chew up your CPU cycles.

    October 27, 2016 at 3:58 pm
    • Angel Genchev Reply

      I totally agree. Moreover I don’t use sessions in database even for small variables size. I set up separate file system for ’em with some special options – lazy writes, etc to allow frequent session updates to be cached in the RAM.

      November 3, 2016 at 5:43 pm

Leave a Reply