1

I have a stored procedure that selects from a table A values it then uses to select from table B. If the number of rows is less than a threshold, it updates Table A's values and reselects table B.

Select val into v from A;
Select ID from B where total > v;

if (Found_Rows() < 3) then
  Update A set val = val +1;
end if;

What I am concerned about is when this stored procedure is hit concurrently and the number of rows is > 3 and the Update is run multiple times.

So first, can this happen? If so how can I prevent this from occurring since Lock isn't allowed in a stored procedure?

Thanks for the info in advance!

vbbartlett
  • 179
  • 2
  • 14

1 Answers1

0

You are updating the whole table A inconditionally. Therefore (even with InnoDB) the whole table will be locked in exclusive mode for the time of the update. This lock is acquired and released automatically by the engine.

So you are safe, concurrency-wise. All concurrent threads will be put on hold when trying to read from A until the update completes.

However (contrary to your last comment), all UPDATE's will take place sequentially, and each of them will increment A.val.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Will this slow the process down? I may have a lot of connections calling this procedure. Would it be better to split it into two procedures one to select and test and the second to actually do the update? Would that stop it from locking the whole table? – vbbartlett Aug 01 '13 at 21:08
  • What should happen is only rarely should the if cause succeed. I don't want it locking unless it needs to. – vbbartlett Aug 01 '13 at 21:09
  • The table is locked during execution of the `UPDATE` statement, not during the `SELECT`. If the `UPDATE` does not run, the table will not be locked at all. – RandomSeed Aug 01 '13 at 22:18