24.2 Wait Condition

Like 'sleep', this method can also be used, when all but one
thread reach a blocked state.

If you are able to detect that the threads are in their blocked
state by using SQL statements, then you can use this method. The
remaining thread runs the statement(s) until the expected result
is returned. Then it continues with the test.

Example:

--connection conn1
LOCK TABLE t1 WRITE;
--connection conn2
# Get the id of this thread.
let $conn2_id= `SELECT CONNECTION_ID()`;
# This will block in wait_for_lock().
send INSERT INTO t1 VALUES (1);
--connection conn1
# Specify the condition that shows if conn2 reached wait_for_lock().
let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID = $conn2_id AND STATE = 'Locked';
# Run the condition in a loop until it becomes true.
--source include/wait_condition.inc
# Run through the critical code.
FLUSH TABLE t1;

In conn2 we get the thread ID first. In conn1 we use a SELECT
statement that returns '1' when the processlist shows that conn2
reached the 'Locked' state. With this setup we call the
wait_condition method. It runs the statement and checks the
result. If the condition is not met, it sleeps for 0.1 second and
retries.

The maximum waste of time is 0.1 seconds. This is much better than
the 'sleep' method, but could still waste a little time.

Another problem is that the condition could be "fuzzy" in some
situations. In the example above, the thread state (proc_info) is
set to "Locked" right before the locking function is called. In
theory it could happen that conn1 continues before conn2 did
acquire the lock. The test would then fail to repeat what it was
intended to do.

The "Debug Sync Facility" should be able to replace most of the
"wait condition" uses.