SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Multiple LOCK TABLES statements

    Hello,

    I have a situation where I have ended up with something like this. Sort of "nested table locking". What troubles me is that it works, and I don't know why it works. I'd assume it should give an error.

    (obviously this is really simplified)


    LOCK TABLES table1 WRITE, table2 WRITE;

    UPDATE table1 SET col1='val' WHERE col2='val';
    UPDATE table2 SET col1='val' WHERE col2='val';

    LOCK TABLES table3 WRITE;

    UPDATE table3 SET col1='val' WHERE col2='val';

    UNLOCK TABLES;
    UNLOCK TABLES;


    Why does this work? Is it because

    a) The second lock tables statement implicitly releases the previous locks, or

    b) The second lock adds another lock, so I end up with all three tables locked.

    I need the latter to be true because I want to ensure that table2 isn't modified by another process before I modify table3, but I don't have a way to tell which is occuring. Do you know?
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff

  2. #2
    One website at a time mmj's Avatar
    Join Date
    Feb 2001
    Location
    Melbourne Australia
    Posts
    6,282
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    D'oh! I found my answer in the MySQL manual, and it's not what I wanted...

    All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.
    [mmj] My magic jigsaw
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    The Bit Depth Blog Twitter Contact me
    Neon Javascript Framework Jokes Android stuff


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
  •