Why does MySQL forbid LOCK TABLES in stored procedures?
I couldn't find a "here's why" statement either, but from reading the items at the links below, it appears to be a combination of:
- preferred best practice: use SELECT...FOR UPDATE
- preference for row-level-locking over table-level locking
- performance issued related to concurrency and the finer-granularity of row-level-locking
- other alternatives appearing on the MySQL Stored Procedure Forum included switching the storage engine (though that still wont get a LOCK TABLES statement into an SP) if table-level-locking is truly justified or using transactions rather than table-level-locking. Those elections probably come down to other considerations not appearing in the original question.
- Also saw a statement on stored procedures declaring that "arbitrary SQL Statements" were not permitted. I didn't hunt for a list of "arbitrary SQL."
Good luck