Re: Atomic check for row existence and insert if doesn't exist

Bernt M. Johnsen wrote: > > Hi, > >> I'm going to correct myself here and say that SERIALIZABLE would probably >> be >> the right choice in this case, since REPEATABLE READ won't prevent >> phantoms. >> I was trying to avoid SERIALIZABLE if possible to increase concurrency, >> but >> I don't t

Re: Atomic check for row existence and insert if doesn't exist

Ace Jayz wrote: > > > If REPEATABLE READ is in effect, then no other transaction would be able > to delete the row between the insert and the select because this would > result in > different results for the select statement if it were repeated. For > example, the f

Re: Atomic check for row existence and insert if doesn't exist

Bernt M. Johnsen wrote: > >>>>>>>>>>>>> Ace Jayz wrote (2007-02-09 21:08:44): > >> A couple of obvious approached come to mind: >> >> 1) - select * from t1 where C=c for update >> - if row returned, then get valu

Re: Atomic check for row existence and insert if doesn't exist

2) - insert into t1 where C=c - if insert fails, select id from t1 where C=c - if insert succeeds, get identity column from last insert, id - insert row with column value of id into second table this would seem to be subject to the row being deleted between the insert attempt and the

Atomic check for row existence and insert if doesn't exist

I've got a table with an identity column, P, as a primary key. This table has another column, C, with a uniqueness constraint. I want to insert a row into the table if no row has a value for C=c, and if a row does exist whose column C=c I want to get the value of the identity column for storage