List:General Discussion« Previous MessageNext Message »
From:Don Read Date:December 25 2009 6:36pm
Subject:Re: two processes update the same column in short time
View as plain text  
On Fri, 25 Dec 2009 15:41:40 +0800  Eva said:

>  Hello,
> 
> I have a table, say its stru is like:
> 
> domain     ip      noticed
> 
> The column "noticed" is an enum value (eigher 0 or 1).
> When process one update "ip", it will set "noticed" to 0.
> Then process two know the status changed, it will do something and set "noticed" to
> 1.
> 
> What the problem I meet is, when process two read ip from the table, and before it
> update the "noticed" to 1 (it has to do something with the ip, and this will take few
> seconds), process one also updated the table with new ip and noticed=0. Under this case,
> process two has gotten the outdated IPs (not the new ones updated by process one), since
> it will set noticed=1 finally so it doesn't have a chance to find it's using old IPs.
> 
> So how to resolve this problem?
> Thanks for my newbie questions.
> Merry Holidays!
> 
> Eva.

Change noticed to enum('new', 'scan', 'done') not null default 'new';

When proc two runs, it should 
"UPDATE noticed='scan' WHERE noticed='new'"
Then repeat the scan with "SELECT ... WHERE noticed='scan'" 
...
and finally it should "UPDATE domain=whatever, ... noticed='done'"


Regards,
-- 
Don Read                                        don_read@stripped
     It's always darkest before the dawn. So if you are going to
     steal the neighbor's newspaper, that's the time to do it.
Thread
two processes update the same column in short timeEva25 Dec
  • Re: two processes update the same column in short timeDon Read25 Dec
    • Re: two processes update the same column in short timeEva26 Dec
      • Re: two processes update the same column in short timeDon Read26 Dec