CODEWITHSUNDEEP
mysqlstored-proceduressynchronization
krisy
krisy

Reputation: 1558

Synchronizing stored procedures in mysql

I have two applications, both of them are using the same stored procedure in MySQL. I would like this procedure to be synchronized, that is while one applications calls it, the other one has to wait.

Is there a way to do this without altering the codes of the applications (that is only modifying the stored procedure)?

Thanks, krisy

Upvotes: 3

Views: 1574

Answers (1)

Jeremy Smyth
Jeremy Smyth

Reputation: 23513

You can absolutely do this within the stored procedure without changing your application code, but bear in mind that you're introducing locking issues and the possibility of timeouts.

Use GET_LOCK() and RELEASE_LOCK() to take care of the synchronization. Run GET_LOCK to perform the synchronization at the start of your stored procedure, and RELEASE_LOCK once you're done:

IF (GET_LOCK('lock_name_for_this_SP', 60)) THEN
   .... body of SP
   RELEASE_LOCK('lock_name_for_this_SP');
ELSE
   .... lock timed out
END IF

You'll also need to take care that your application timeouts are longer than the lock timeout so you don't incur other problems.

Upvotes: 2

Related Questions