Quick Links

Re: how do i avoid multiple sessions from inserting the same row?

From:	Dmitry Tkach <dmitry(at)openratings(dot)com>
To:	Patrick Fiche <patrick(dot)fiche(at)aqsacom(dot)com>
Subject:	Re: how do i avoid multiple sessions from inserting the same row?
Date:	2003-02-24 19:40:19
Message-ID:	3E5A7523.7080204@openratings.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	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 checks at 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(dot)fiche(at)aqsacom(dot)com
> t=E9l : 01 69 29 36 18
> ----------------------------------------------------------------------------
> ---------------
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Kolus Maximiliano
> Sent: Monday, February 24, 2003 5:13 PM
> To: pgsql-general(at)postgresql(dot)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(at)doe(dot)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(at)doe(dot)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(at)doe(dot)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(dot)maximiliano(at)bcr(dot)com(dot)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> ze=3D2>Why=20
> not something like that ?</DIV>
> <DIV> size=3D2> </DIV>
> <DIV> ze=3D2>1)=20
> SELECT id FROM users WHERE email =3D 'blah'</DIV>
> <DIV> size=3D2> </DIV>
> <DIV> ze=3D2>2) IF=20
> NULL -> INSERT INTO users values (....'blah' )  WHERE NOT EXISTS=20
> ....</DIV>
> <DIV> size=3D2>           =
>     =20
> -> SELECT id FROM users WHERE email =3D 'blah'</DIV>
> <DIV> </DIV>
> <DIV> ze=3D2>I=20
> think that only one process should do the insert but all will return t=
> he=20
> id...</DIV>
> <DIV> </DIV></DIV>
> size=3D2>------------------------------------------------------------------=
> -------------------------=20
> Patrick Fiche ce=3DArial=20
> size=3D2>email : size=3D2>patrick(dot)fiche(at)aqsacom(dot)com =20
> size=3D2>t=E9l : 01 69 29 36=
> =20
> 18 size=3D2>------------------------------------------------------------------=
> -------------------------=20
> 
> <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
> <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft> oma=20
> size=3D2>-----Original Message----- From:=20
> pgsql-general-owner(at)postgresql(dot)org=20
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Kolus=20
> Maximiliano Sent: Monday, February 24, 2003 5:13 PM To:</=
> B>=20
> pgsql-general(at)postgresql(dot)org Subject: [GENERAL] how do i avoid=
> =20
> multiple sessions from inserting the same row? </DIV>
> Hello, 
>         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(at)doe(dot)com sends an em=
> ail to=20
> an special address he wil be added to the users table.
>         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(at)doe(dot)com exists and a=
> dd=20
> him if he doesnt. The process for this is:
> 1) SELECT id FROM users WHERE email=3D'blah'; <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. 3) If the previous select =
> returns=20
> the id, it will be returned. 
>         What happene=
> d?.=20
> Well, two processes believed that john(at)doe(dot)com didn't exist, both tried t=
> o add=20
> him and one of them got a beautyfull duplicated key error.
>         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.
>         Any ideas or=
> tips?.=20
> TIA. 
> -- Maximiliano A. Kolus</FONT=
>
>>=20
>
> Network Administrator <FONT=20
> size=3D2><kolus(dot)maximiliano(at)bcr(dot)com(dot)ar> B=
> olsa De=20
> Comercio Rosario - Argentina </BLOCKQUOTE></BODY></HTML>
>
> ------=_NextPart_000_0012_01C2DC29.DC150490--
>

In response to

Re: how do i avoid multiple sessions from inserting the same row? at 2003-02-24 16:32:53 from Patrick Fiche

Browse pgsql-general by date

	From	Date	Subject
Next Message	Hadley Willan	2003-02-24 19:41:27	Re: how do i avoid multiple sessions from inserting the
Previous Message	Dmitry Tkach	2003-02-24 19:36:33	7.4?