Single Instance Stored Procedure call

Question

Is there any way that I can make sure that a stored procedure completely finishes before another instance of it is started?

I have to do 3 things in the procedure and if two instances are running at the same time it will mess up a boundary case.

Example: Count rows, if < X insert a row, return calculated Y

if multiple instances of the stored proc can run at the same time I could go past my X target. Chance is small, but its there.

DB we're using is MYSQL5, but also wondering for MSSQL

Answer 1

Use transactions. Check row count afterwards and roll back if it's gone over.

Answer 2

You can try leveraging locking reads to allow concurrency to ensure that you're operating as a singleton.

NOTE: This will block all other calls and they will execute serially. Make sure this is the behavior you're after.