MySQL Internals Manual  /  Test Synchronization  /  Wait Condition

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.


--connection conn1
       --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().
       WHERE ID = $conn2_id AND STATE = 'Locked';
   # Run the condition in a loop until it becomes true.
   --source include/
   # Run through the critical code.

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.