two processes update the same column in short time

IEhlbGxvLA0KDQpJIGhhdmUgYSB0YWJsZSwgc2F5IGl0cyBzdHJ1IGlzIGxp a2U6DQoNCmRvbWFp
biAgICAgaXAgICAgICBub3RpY2VkDQoNClRoZSBjb2x1bW4gIm5vdGljZWQi IGlzIGFuIGVudW0g
dmFsdWUgKGVpZ2hlciAwIG9yIDEpLg0KV2hlbiBwcm9jZXNzIG9uZSB1cGRh dGUgImlwIiwgaXQg
d2lsbCBzZXQgIm5vdGljZWQiIHRvIDAuDQpUaGVuIHByb2Nlc3MgdHdvIGtu b3cgdGhlIHN0YXR1
cyBjaGFuZ2VkLCBpdCB3aWxsIGRvIHNvbWV0aGluZyBhbmQgc2V0ICJub3Rp Y2VkIiB0byAxLg0K
DQpXaGF0IHRoZSBwcm9ibGVtIEkgbWVldCBpcywgd2hlbiBwcm9jZXNzIHR3 byByZWFkIGlwIGZy
b20gdGhlIHRhYmxlLCBhbmQgYmVmb3JlIGl0IHVwZGF0ZSB0aGUgIm5vdGlj ZWQiIHRvIDEgKGl0
IGhhcyB0byBkbyBzb21ldGhpbmcgd2l0aCB0aGUgaXAsIGFuZCB0aGlzIHdp bGwgdGFrZSBmZXcg
c2Vjb25kcyksIHByb2Nlc3Mgb25lIGFsc28gdXBkYXRlZCB0aGUgdGFibGUg d2l0aCBuZXcgaXAg
YW5kIG5vdGljZWQ9MC4gVW5kZXIgdGhpcyBjYXNlLCBwcm9jZXNzIHR3byBo YXMgZ290dGVuIHRo
ZSBvdXRkYXRlZCBJUHMgKG5vdCB0aGUgbmV3IG9uZXMgdXBkYXRlZCBieSBw cm9jZXNzIG9uZSks
IHNpbmNlIGl0IHdpbGwgc2V0IG5vdGljZWQ9MSBmaW5hbGx5IHNvIGl0IGRv ZXNuJ3QgaGF2ZSBh
IGNoYW5jZSB0byBmaW5kIGl0J3MgdXNpbmcgb2xkIElQcy4NCg0KU28gaG93 IHRvIHJlc29sdmUg
dGhpcyBwcm9ibGVtPw0KVGhhbmtzIGZvciBteSBuZXdiaWUgcXVlc3Rpb25z Lg0KTWVycnkgSG9s
aWRheXMhDQoNCkV2YS4=

Re: two processes update the same column in short time

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@att.net
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: two processes update the same column in short time

2009-12-26 2:36, Don Read :

>
> 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'"
>

Thanks Don.
That means, when process one want to update "ip" but it see
noticed="scan", it will block until the status become "done", is it?

Eva

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: two processes update the same column in short time

On Sat, 26 Dec 2009 10:43:55 +0800 Eva said:

> 2009-12-26 2:36, Don Read :
>
> >
> > 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'"
> >
>
> Thanks Don.
> That means, when process one want to update "ip" but it see
> noticed="scan", it will block until the status become "done", is it?
>
> Eva

Depends on how closely coupled the two processes are.
a. proc #1 can only insert new ip's (INSERT IGNORE)
b. proc #1 could ignore/refuse to update any ip not 'new'
c. proc #1 could leave the noticed field alone.
d. proc #1 could set noticed to 'new' (and let proc #2 scan it again)

Which alternative works best for your application?

Regards,
--
Don Read don_read@att.net
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org