Re: how do i avoid multiple sessions from inserting the same row? - Mailing list pgsql-general
| From | Dmitry Tkach |
|---|---|
| Subject | Re: how do i avoid multiple sessions from inserting the same row? |
| Date | |
| Msg-id | 3E5A7523.7080204@openratings.com Whole thread Raw |
| In response to | Re: how do i avoid multiple sessions from inserting the same row? ("Patrick Fiche" <patrick.fiche@aqsacom.com>) |
| List | pgsql-general |
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-- >
pgsql-general by date: