SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to avoid several users editing the same rows at the same time.

DECLARE cc CURSOR FOR
SELECT ... FROM tab WHERE ... FOR UPDATE
OPEN cc
FETCH cc <-- lock is acquired
...
CLOSE cc <-- lock is released

MySQL locking mechanism depends upon the transaction manager. The default locking granularity is per table when you use the default non-transactional configuration. You must use the InnoDB Storage Engine to get transactions and locking mechanisms.

SELECT ... FOR UPDATE is only supported since MySQL version 6.0. Locks are released at the end of the transaction.

Solution

Check if the MySQL storage engine supports SELECT FOR UPDATE, otherwise review the program logic.