View Full Version : Race conditions: row or table locking (InnoDB or MyISAM)? Help.

02-22-2009, 05:05 PM

I have built my own session engine with PHP. The session data is stored in MySQL. Now I'm wondering, to prevent race conditions (where two concurrent threads write content and read and mess it up), I need to have locking.

I am not an expert of MySQL. Does MySQL automatically lock something? Right now I'm using MyISAM, but should I use InnoDB instead as it supports row-based locking?

02-22-2009, 06:01 PM
Why would you store session data in the database? Its meant to be stored in the 'session' and then later, inputted to the db when all the data has been gathered. if you still need to use the session data elsehwere, you would use it direct from the session and not, the db.

unless you have a very specific, strange requirement.


02-22-2009, 06:38 PM
Why would you store session data in the database? Its meant to be stored in the 'session'
The session engine/system is itself in the database.

The database is far faster to process information than the file system. Opening, modifying, closing files like what PHP default session system does is pricier and slower so I moved and made my own session system that is entirely in the database.

02-22-2009, 06:41 PM
Let me rephrase my problem.

UPDATE table SET name=CONCAT(name,'test') WHERE id=1;

If two concurrent persons (e.g. through AJAX) are calling that, will it mess up?

Person A will read the name and it is "jack" and just before it manages to concat, person B will concat test into it so it becomes "jacktest" instead of "jacktesttest". A race condition occured.

So shouldn't I do something like this with my session:

SELECT * FROM sessions WHERE sessid=xxx FOR UPDATE;
Now it gets locked and whenever I update something for that row it will get permanently updated and read by others.

02-22-2009, 08:09 PM
As far as I understand, MyIsam has table locking features.
InnoDB has row locking features.

MyIsam is quicker than innodb in many cases so there has to be compromise somewhere.

But then if we re-consider sessions within the browser, there is no need for locking because they are each individual sessions. only when the data is to be sent to the db, from the session, should locking be required and there will therefore be less locking done.

OK I don't use php but, even if php sessions are slower than mysql, they may make the issue easier, therefore another type of compromise?


02-22-2009, 08:12 PM
That's not what I wanted to know, but take a look at this:

$c = mysql_connect('localhost','root','hottis');
$r = mysql_query('SELECT username FROM admins WHERE id=1');
$a = mysql_fetch_assoc($r);
mysql_query('UPDATE admins SET username=CONCAT("'.$a['username'].'","asd") WHERE id=1');
If you run this script twice at the same time, the result is that the username will have "asdasd" in the end of it. Why does it work like that? I never locked anything. Did the SELECT query lock it?