I needed to give greater detail.
parent_id isn't unique. The table has a composite primary key (parent_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),
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.
On Wed, Mar 24, 2010 at 6:26 AM, Rodrigo Ferreira
> 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.
> 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:
> Johnny Withers
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: