List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:March 24 2010 4:19pm
Subject:Re: SELECT and INSERT if no row is returned
View as plain text  
I needed to give greater detail.

parent_id isn't unique. The table has a composite primary key (parent_id,
seq_id).
Here's a better schema def

CREATE TABLE sometable (
parent_id INT(10) NOT NULL,
seq_id INT(10) AUTO_INCREMENT,
child_id INT(10) NULL,
PRIMARY KEY(parent_id, seq_id),
UNIQUE KEY(child_id)
) ENGINE=INNODB;

The requirement is that there can be only 1 parent_id associated with a
given child or there can be only one parent_id not associated with a
child_id (NULL child_id). I need to avoid a race condition where 2
connections can SELECT and return an empty row and insert rows of the same
parent_id not associated with a message_id. It's that .1% of the cases we
want to avoid.

Kyong

On Wed, Mar 24, 2010 at 6:26 AM, Rodrigo Ferreira
<rodrigof_silva@stripped>wrote:

> First, if you want no duplicate parent_id, make it unique key (as JW
> saids). Look at INSERT ... ON DUPLICATE KEY UPDATE, I think this will solve
> the problem with one statement.
>
> Rodrigo Ferreira
>
> --- On *Wed, 3/24/10, Johnny Withers <johnny@stripped>* wrote:
>
>
> From: Johnny Withers <johnny@stripped>
> Subject: Re: SELECT and INSERT if no row is returned
> To: "Kyong Kim" <kykimdba@stripped>
> Cc: "mysql" <mysql@stripped>
> Date: Wednesday, March 24, 2010, 9:32 AM
>
> Make parent_id a unique key. Doing a select first and inserting if no
> result will work 99.9% of the time; however, consider 2 rows being
> inserted at the same time.
>
> JW
>
> On Tuesday, March 23, 2010, Kyong Kim <kykimdba@stripped> wrote:
> > I need to INSERT a row into a table only if it does not exist to
> > insure that there won't be duplicate rows.
> > Due to the the usage case and the design of the primary key,
> > non-unique id + auto increment, I don't think insert ignore is an
> > option.
> >
> > What would be simplest and cheapest way to make sure that given the
> > following schema
> >
> > create table some_table
> > ( parent_id int //non-unique
> >   seq_id int auto_increment ) ENGINE=INNODB
> >
> > that no row with the same parent_id can be inserted?
> >
> > Kyong
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
> >
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
>

Thread
SELECT and INSERT if no row is returnedKyong Kim24 Mar
  • Re: SELECT and INSERT if no row is returnedJohnny Withers24 Mar
Re: SELECT and INSERT if no row is returnedRodrigo Ferreira24 Mar
  • Re: SELECT and INSERT if no row is returnedKyong Kim24 Mar
    • Re: SELECT and INSERT if no row is returnedShawn Green24 Mar