SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Lock tables

  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lock tables

    I am not sure if I understand correctly lock tables statement. I hope somebody can explain this situation:

    I would like to lock table photos, so nobody can insert new photo while user A is inserting photo and getting inserted id.

    photos
    photoId | photo | userId

    PHP Code:
    $db->query("LOCK TABLES photos write");
    $db->query("INSERT INTO photos (userId,photo) VALUE (1,'$photo')");
    $photoId=mysql_insert_id();
    $db->query("UNLOCK TABLES"); 
    I tried this in localhost. First I removed the last line ($db->query("UNLOCK TABLES")), so when a user A open the script, table stays locked. I opened script test.php in firefox and then the same script as user B in chrome. As user B I didn't get any error and new photo has been inserted. Why that? Isn't supossed to get error in this case as table is still locked?

    tnx!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you don't need to do that

    mysql_insert_id is guaranteed to get you the correct value for each separate connection
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So there is no chance that another user will in exact the time between insert and mysql_inser_id insert another photo or is it just so small possibility?

    But anyway if I or somebody else needs that for other purposes. I think my the problem was that after the php script ends, it also unlock table automatically. Normally lock will means that execution of script will wait for beeing table unlocked, without any error. Am I right?

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by meee View Post
    So there is no chance that another user will in exact the time between insert and mysql_inser_id insert another photo or is it just so small possibility?

    But anyway if I or somebody else needs that for other purposes. I think my the problem was that after the php script ends, it also unlock table automatically. Normally lock will means that execution of script will wait for beeing table unlocked, without any error. Am I right?
    no, the php mysql insert id / auto_increment is guaranteed to be unique, MySQL will handle this internally.
    http://dev.mysql.com/doc/refman/5.0/...increment.html

    Yes, I think it will be fine in most cases,
    BUT what happens if your php script dies right after it 'locks tables'.
    and it can also put extra load on the database - mysql will be handle the concurrency around the id generation, but you're making it handle concurrency for the whole table.

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    jurn I just read today OReilly Learning PHP MySQL and JavaScript book and the author wrote about this function:
    "But there’s a slight window of opportunity for an error to slip in. Suppose that two
    people visit the website at the same time and submit new information, causing the web
    server to run your program twice at the same time. (Web servers can run several programs
    at the same time to speed up response time.) The second visitor might insert a
    new cat just before the first visitor’s program issues mysql_insert_id. This is a rare but
    serious problem, because the first person could end up being associated with the second
    person’s cat.
    So a completely safe procedure for linking tables through the insert ID is to use locks"

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by meee View Post
    jurn I just read today OReilly Learning PHP MySQL and JavaScript book and the author wrote about this function:
    "But thereís a slight window of opportunity for an error to slip in. Suppose that two
    people visit the website at the same time and submit new information, causing the web
    server to run your program twice at the same time. (Web servers can run several programs
    at the same time to speed up response time.) The second visitor might insert a
    new cat just before the first visitorís program issues mysql_insert_id. This is a rare but
    serious problem, because the first person could end up being associated with the second
    personís cat.
    So a completely safe procedure for linking tables through the insert ID is to use locks"
    I acquired the book because you made me worry
    See
    http://forums.oreilly.com/content/Le...sql-insert-id/
    The author gracefully admits to a mistake.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    jurn, awesome update, thanks for that

    in any dispute between the vendor's docs and a book, i would go with the docs

    you're right, that was a graceful admission
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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