Why not something like that ?
1) SELECT id FROM users WHERE email = 'blah'
2) IF NULL -> INSERT INTO users values (....'blah' ) WHERE NOT EXISTS ....
-> SELECT id FROM users WHERE email = 'blah'
I think that only one process should do the insert but all will return the id...
-------------------------------------------------------------------------------------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
-------------------------------------------------------------------------------------------
Hello,
I'm programming a little system that has an 'users' table and i've met a concurrency problems: users will be added to this table upon the reception of emails from them (for those who want to know, it's like http://www.ordb.org). So, if john@doe.com sends an email to an special address he wil be added to the users table.
The problem i have is that some users have automated systems that shoot a lot of emails at once, so i have multiple processes trying to check if john@doe.com exists and add him if he doesnt. The process for this is:
1) SELECT id FROM users WHERE email='blah';
2) If the previous select returns NULL, the user will be added and it's id will be returned.
3) If the previous select returns the id, it will be returned.
What happened?. Well, two processes believed that john@doe.com didn't exist, both tried to add him and one of them got a beautyfull duplicated key error.
I need to avoid this, i looked at pg's table and row locking techniques. I dont know fi SELECT ... FOR UPDATE would work because i would be selecting a row that doesnt exist yet. LOCK TABLE ... FOR ACCESS EXCLUSIVE MODE would work, but it seems to be a little extreme for me.
Any ideas or tips?. TIA.
--
Maximiliano A. Kolus
Network Administrator
<kolus.maximiliano@bcr.com.ar>
Bolsa De Comercio Rosario - Argentina