MySql: How do you use mutex/lock in a stored-procedure

Question

I want my function to only run once. Meaning that if many threads call it at the same time, the function will block all threads apart from one and only allow it to run.

Answer 1

It sounds like you want the stored procedure to do the synchronization. Why not just put the synchronization in the application itself.

pthread_mutex_lock(&lock);
... Call stored procedure here ..
pthread_mutex_unlock(&lock);

If you need to provide synchronization at the database level, you can use the LOCK and UNLOCK TABLES commands within your stored procedure. Lock on entry to the procedure and unlock on exit. Depending on your needs, it may be that you'll want to create a dummy table that is locked and unlocked instead of an actual data table in use by this and other processes. See here for more details.