Lock a MyIsam table across connections?

Question

I have a bunch of mysql statements (that work on one MyIsam table only) in my PHP script and I don't want to execute these statements concurrently at any time, regardless of how many connections are being used.

For example, two different clients execute the php script on my server and the script opens one mysql connection per each request. I want first to execute the entire bunch of statements in the 1st request and only when this is completed, to execute the bunch of statements in the 2nd request.

I tried:

using "START TRANSACTION" before the bunch of statements => the table is MyIsam and must stay so, so this has no effect.

using "LOCK TABLE t WRITE" before the bunch of statements => locks are not maintained across connections so this doesn't work.

Thanks for help.

Answer 1

I'm using GET_LOCK and RELEASE_LOCK in this situation.

GET_LOCK() can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked within one session, GET_LOCK() blocks any request by another session for a lock with the same name. This enables clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also enables a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.

If multiple clients are waiting for a lock, the order in which they will acquire it is undefined. Applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

Before running a bunch of statements, run:

SELECT GET_LOCK('my unique lock name', 10);

If it returns 1 (success) go on with your bunch of statements and in the end

SELECT RELEASE_LOCK('my unique lock name');

Add some handling of the timeout case, such as retry few times.

It doesn't matter whether your statements work with one table or many tables, whether they are MyIsam, or not. These application-specific locks are server-wide.