Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts

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

    Hi,

    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?
    PHP 5 & MySQL 5 (Y)

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by bazz View Post
    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.
    PHP 5 & MySQL 5 (Y)

  • #4
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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.
    Last edited by kaisellgren; 02-22-2009 at 06:45 PM.
    PHP 5 & MySQL 5 (Y)

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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?

    bazz
    Last edited by bazz; 02-22-2009 at 08:13 PM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That's not what I wanted to know, but take a look at this:
    PHP Code:
    $c mysql_connect('localhost','root','hottis');
    mysql_select_db('test');
    $r mysql_query('SELECT username FROM admins WHERE id=1');
    $a mysql_fetch_assoc($r);
    sleep(5);
    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?
    PHP 5 & MySQL 5 (Y)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •