table lock status for use in if ... then statement - MySQL

Question

I am trying to determine within a MySQL event if a table is locked.

If it is not, then I want to run a query.

I don't know the systems tables well or how to get this into a variable for the comparison in the the If .. Then.

Thanks,

Bud

question from:https://stackoverflow.com/questions/65895244/table-lock-status-for-use-in-if-then-statement-mysql

Answer 1

Look at these locking functions. Essentially you can obtain a named lock by using GET_LOCK() function like so:

SELECT GET_LOCK('myNamedLock');
// Do your thing in the app, then
SELECT RELEASE_LOCK('myNamedLock');

Your current session will own the lock and no other session will be able to obtain a lock with the same name. It is a sort of pseudo locking, nothing is actually locked but if you want to make sure another connection is not doing something on the DB, make that other connection obtain the named lock first and then for your current connection keep checking if lock has been released/is available.