Professional Web Applications Themes

Is UPDATE Atomic? - MySQL

I'm not really sure if my problem lies in the PHP code or the SQL... but here it is: begin(); $query = "UPDATE Nodes " ."SET NodeStatus = 1 " ."WHERE NodeID = $NodeID AND NodeStatus = 0"; $result = mysql_query($query); if ($result == null || $result == false) { rollback(); return; } In the Nodes table, there should be only one instance of a node with a particular NodeID. So, presumably, user1 will hit the UPDATE and change the NodeStatus to 1. Then, when user2 hits the UPDATE, it should fail, since the NodeStatus =1 (and a condition of ...

  1. #1

    Default Is UPDATE Atomic?

    I'm not really sure if my problem lies in the PHP code or the SQL...
    but here it is:

    begin();


    $query = "UPDATE Nodes "
    ."SET NodeStatus = 1 "
    ."WHERE NodeID = $NodeID AND NodeStatus = 0";


    $result = mysql_query($query);


    if ($result == null || $result == false) {
    rollback();
    return;
    }


    In the Nodes table, there should be only one instance of a node with
    a
    particular NodeID.

    So, presumably, user1 will hit the UPDATE and change the NodeStatus
    to
    1. Then, when user2 hits the UPDATE, it should fail, since the
    NodeStatus =1 (and a condition of the update is that the NodeStatus
    =0).


    However, when multiple users simultaneously (at least down to the
    second, according to logs) access the method, they ALL succeed in the
    UPDATE query. I don't understand how this can happen. I'm assuming
    that the UPDATE operation is atomic in mySQL.


    I feel like I shouldn't have to use locks.. but I'm considering it
    with the terrible results so far.

    evanpeck@gmail.com Guest

  2. #2

    Default Re: Is UPDATE Atomic?

    == Quote from evanpeck ( com)'s article 

    you don't need to consider locks; however, you do need to look at your isolation
    level. can you tell us what is the isolation level of mysql server. it can be
    found in the my.cnf .

    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: Is UPDATE Atomic?

    Unfortunately, I wasn't the person who originally set up the server,
    as I am continuing work from last year. I also don't have access to
    that file. I'm assuming it is the default setting, however.

    evanpeck@gmail.com Guest

  4. #4

    Default Re: Is UPDATE Atomic?

    == Quote from evanpeck ( com)'s article 

    an obvious question i forgot to ask is "are you sure you're committing?" and if
    not, maybe you should do an explicit commit to ensure that the updates take place.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  5. #5

    Default Re: Is UPDATE Atomic?

    Thank you for the help.

    I found a solution, however. I thought that UPDATE would 'fail' if it
    did not update anything. Instead, it only fails if there is an error.

    On Jun 18, 10:37 am, lark <net> wrote: 
    >
    > an obvious question i forgot to ask is "are you sure you're committing?" and if
    > not, maybe you should do an explicit commit to ensure that the updates take place.
    > --
    > POST BY: lark with PHP News Reader[/ref]


    evanpeck@gmail.com Guest

Similar Threads

  1. Atomic directory swap
    By Michael in forum Mac Programming
    Replies: 5
    Last Post: February 6th, 08:48 PM
  2. inline assembler and atomic byte reversal operations
    By Rob in forum UNIX Programming
    Replies: 8
    Last Post: October 20th, 02:43 PM
  3. Is fwrite atomic or not?
    By Herouth Maoz in forum PHP Development
    Replies: 0
    Last Post: August 6th, 02:25 PM
  4. 5.0.7 Update Pack 1 requires SCO Update license?
    By Scott McMillan in forum SCO
    Replies: 0
    Last Post: July 30th, 08:04 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139