CODEWITHSUNDEEP
mysql
Don Wool
Don Wool

Reputation: 1167

how to manage locks to prevent a duplicate insert

I am trying to cache an aggregation but due to concurrency could run into a referential integrity violation..

Here is what I am doing (sort of boiled down a bit) :

select  low, high
into    l_low, l_high
from    nasd_stats
where   symbol_id = l_symbol_id;

if( l_low is NULL ) then 
      select  max(last),
              min(last)  
      into    l_high, l_low
      from    nasd
      where   symbol_id = l_symbol_id;

      insert  into nasd_stats values ( l_symbol_id, l_low, l_high );
end if;

So, two users could run the sproc that has this code, and so detect, at the same time, that l_low is null, and then both try and insert into nasd_stats at the same time after the min/max aggregation (one of which would fail as there is a unique key on nasd_stats based on symbol_id).

Any idea how to prevent that from happening??

I'm guessing I could do this :

Start Transaction;
delete from nasd_stats where symbol_id = l_symbol_id;
insert into nasd_stats values ( l_symbol_id, l_low, l_high;
Commit;

logic being, the delete would lock the row (even if it doesn't exist), and then the insert would do what I want to do.

TIA.

Don

Upvotes: 0

Views: 2027

Answers (2)

Qui_Jon
Qui_Jon

Reputation: 163

Can you just check for the symbol ID in your insert and it it exists then someone already got to it so you dont need to do another insert?

So your code would be the same but the insert statement would be

  insert  into nasd_stats values ( l_symbol_id, l_low, l_high ) where symbol_id =    l_symbol_id AND l_low is NULL ;

Upvotes: 0

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

if the key is defined on symbol_id field then execute the query in following manner

insert  into nasd_stats Select  distinct l_symbol_id, l_low, l_high  from nasd_stats where not exists (Select 1 from nasd_stats where symbol_id  = l_symbol_id) ;

Upvotes: 1

Related Questions