Обсуждение: how do i avoid multiple sessions from inserting the same row?
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
-------------------------------------------------------------------------------------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
-------------------------------------------------------------------------------------------
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kolus Maximiliano
Sent: Monday, February 24, 2003 5:13 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] how do i avoid multiple sessions from inserting the same row?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
Patrick Fiche wrote: > This is a multi-part message in MIME format. > > ------=_NextPart_000_0012_01C2DC29.DC150490 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Why not something like that ? > > 1) SELECT id FROM users WHERE email =3D 'blah' > > 2) IF NULL -> INSERT INTO users values (....'blah' ) WHERE NOT EXISTS .... > -> SELECT id FROM users WHERE email =3D 'blah' > > I think that only one process should do the insert but all will return the > id... I doubt this will work either - if the other process has not committed yet, then (2) will not see the new row, just like(1) would not... I believe, your best choice is to let it barf and handle the error on the higher level - just do the insert without any checksat all, and, if it fails because of duplication, ignore the error, and assume that the entry has already been inserted... Dima > > > ---------------------------------------------------------------------------- > --------------- > Patrick Fiche > email : patrick.fiche@aqsacom.com > t=E9l : 01 69 29 36 18 > ---------------------------------------------------------------------------- > --------------- > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kolus Maximiliano > Sent: Monday, February 24, 2003 5:13 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] how do i avoid multiple sessions from inserting the same > row? > > > > 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=3D'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 > > > ------=_NextPart_000_0012_01C2DC29.DC150490 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Diso-8859-= > 1"> > <TITLE>how do i avoid multiple sessions from inserting the same row?</TITLE> > > <META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR></HEAD> > <BODY> > <DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff si= > ze=3D2>Why=20 > not something like that ?</FONT></SPAN></DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff=20 > size=3D2></FONT></SPAN> </DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff si= > ze=3D2>1)=20 > SELECT id FROM users WHERE email =3D 'blah'</FONT></SPAN></DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff=20 > size=3D2></FONT></SPAN> </DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff si= > ze=3D2>2) IF=20 > NULL -> INSERT INTO users values (....'blah' ) WHERE NOT EXISTS=20 > ....</FONT></SPAN></DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff=20 > size=3D2> = > =20 > -> SELECT id FROM users WHERE email =3D 'blah'</FONT></SPAN></DIV> > <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV> > <DIV><SPAN class=3D240201916-24022003><FONT face=3DArial color=3D#0000ff si= > ze=3D2>I=20 > think that only one process should do the insert but all will return t= > he=20 > id...</FONT></SPAN></DIV> > <DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV></DIV> > <P><B><I><FONT face=3DArial=20 > size=3D2>------------------------------------------------------------------= > -------------------------</FONT></I></B>=20 > <BR><B><FONT face=3DArial size=3D2>Patrick Fiche</FONT></B> <BR><B><FONT fa= > ce=3DArial=20 > size=3D2>email :</FONT><I> <FONT face=3DArial=20 > size=3D2>patrick.fiche@aqsacom.com</FONT></I></B> <BR><B><FONT face=3DArial= > =20 > size=3D2>t=E9l</FONT><I></I><I><FONT face=3DArial size=3D2> : 01 69 29 36= > =20 > 18</FONT></I></B> <BR><B><I><FONT face=3DArial=20 > size=3D2>------------------------------------------------------------------= > -------------------------</FONT></I></B>=20 > </P> > <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"> > <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT face=3DTah= > oma=20 > size=3D2>-----Original Message-----<BR><B>From:</B>=20 > pgsql-general-owner@postgresql.org=20 > [mailto:pgsql-general-owner@postgresql.org]<B>On Behalf Of </B>Kolus=20 > Maximiliano<BR><B>Sent:</B> Monday, February 24, 2003 5:13 PM<BR><B>To:</= > B>=20 > pgsql-general@postgresql.org<BR><B>Subject:</B> [GENERAL] how do i avoid= > =20 > multiple sessions from inserting the same row?<BR><BR></FONT></DIV> > <P><FONT size=3D2>Hello,</FONT> </P> > <P> <FONT size=3D2>I'm programm= > ing a=20 > little system that has an 'users' table and i've met a concurrency proble= > ms:=20 > users will be added to this table upon the reception of emails from them = > (for=20 > those who want to know, it's like <A href=3D"http://www.ordb.org"=20 > target=3D_blank>http://www.ordb.org</A>). So, if john@doe.com sends an em= > ail to=20 > an special address he wil be added to the users table.</FONT></P> > <P> <FONT size=3D2>The problem = > i have=20 > is that some users have automated systems that shoot a lot of emails at o= > nce,=20 > so i have multiple processes trying to check if john@doe.com exists and a= > dd=20 > him if he doesnt. The process for this is:</FONT></P> > <P><FONT size=3D2>1) SELECT id FROM users WHERE email=3D'blah';</FONT> <B= > R><FONT=20 > size=3D2>2) If the previous select returns NULL, the user will be added a= > nd it's=20 > id will be returned.</FONT> <BR><FONT size=3D2>3) If the previous select = > returns=20 > the id, it will be returned.</FONT> </P> > <P> <FONT size=3D2>What happene= > d?.=20 > Well, two processes believed that john@doe.com didn't exist, both tried t= > o add=20 > him and one of them got a beautyfull duplicated key error.</FONT></P> > <P> <FONT size=3D2>I need to av= > oid=20 > this, i looked at pg's table and row locking techniques. I dont know fi S= > ELECT=20 > ... FOR UPDATE would work because i would be selecting a row that doesnt = > exist=20 > yet. LOCK TABLE ... FOR ACCESS EXCLUSIVE MODE would work, but it seems to= > be a=20 > little extreme for me.</FONT></P> > <P> <FONT size=3D2>Any ideas or= > tips?.=20 > TIA.</FONT> </P> > <P><FONT size=3D2>--</FONT> <BR><FONT size=3D2>Maximiliano A. Kolus</FONT= > >>=20 > > <BR><FONT size=3D2>Network Administrator</FONT> <BR><FONT=20 > size=3D2><kolus.maximiliano@bcr.com.ar></FONT> <BR><FONT size=3D2>B= > olsa De=20 > Comercio Rosario - Argentina</FONT> </P></BLOCKQUOTE></BODY></HTML> > > ------=_NextPart_000_0012_01C2DC29.DC150490-- >
If you create a PGSQL function using PLPG SQL you could do the same sort
of thing.
Difference being you return -1 as an indicator of failure for
duplicates.
CREATE OR REPLACE fn_add_email( VARCHAR(128) ) RETURNS INTEGER AS'
DECLARE
new_email ALIAS FOR $1;
result INTEGER := -1;
rec_chk_exist RECORD;
BEGIN
SELECT INTO rec_chk_exist *
FROM users WHERE email = new_email;
IF NOT FOUND THEN
INSERT INTO users( email ) VALUES ( new_email );
--IF SEQUENCE GENERATED ID
result := SELECT last_value
FROM sequence_users;
--otherwise
result := SELECT( id ) FROM users WHERE email =
new_mail;
END IF;
RETURN result;
END;' language 'plpgsql';
On Tue, 2003-02-25 at 05:13, Kolus Maximiliano wrote:
> 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
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.