SQL adaptation guide For Oracle MySQL 5.x / BDL programming |
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.
Check if the MySQL storage engine supports SELECT FOR UPDATE, otherwise review the program logic.