5

I have read through several sections of the MySQL reference manual, and while it states repeatedly that LOCK TABLES cannot be used in a stored procedure, I have never found an explanation for the reasoning behind that.

2

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

0

tl;dr

They are forbidden in order to prevent you from inadvertently causing a commit that you may not want to happen (eg by calling the stored procedure in the middle of a transaction).

Copied from the (now deprecated) MySQL mailing list:

the following is Shawn Green's answer to the post I made there, and then his reply to another poster in the same thread:

Hello Jeff,

On 8/13/2018 12:05 PM, jeff@stripped wrote:

Hello, I have read through several pages of the reference manual, and I've seen several instances where it is stated that LOCK TABLES (and UNLOCK TABLES) is not allowed in a stored procedure, but so far, I haven't found an explanation as to why that is. Could someone please enlighten me?

Thanks

Normally, the list is more responsive than this. This is a pretty easy question and someone usually handles those before I need to step in as a backstop.

The key why you cannot execute a LOCK TABLE command within a stored program is here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

Stored programs execute under the scope of the transaction in which they are started. That determines which sets of rows are "visible" to the routine and sets boundaries on what may be committed or rolled back should the need arise.

(a simple example)

* your session: START TRANSACTION
* your session: ...other data activity ...
* your session (INSERT ... )
   * causes an INSERT trigger to fire
     * which calls a stored procedure

If that stored procedure or that trigger called a LOCK TABLE command, it would forcibly COMMIT the existing transaction you had been working within until that moment. Your half-completed work would have become fully committed even if a later step had needed you to issue a ROLLBACK command.

Note, even if you are not in a multi-statement transaction that any stored programs called by or executed within the scope of your user command are part of that little mini (auto-committed) transaction.

Does that help?

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


Hello Mogens,

On 8/18/2018 2:32 PM, Mogens Melander wrote:

Guys,

I think I remember this from way back.

You could ask for a lock, and get an OK if it is safe.

Something like, if there is pending transactions, on your target tables, you would get a NO.

But then again. I could be wrong, and Shawn is the authority on this.

Your request for a lock would have waited until all existing readers or writers (depending on the type of lock you asked for) had finished using the tables you wanted to lock. By extension, that means that any transactions active against the tables you wanted to lock would have also needed to have committed or rolled back before your request would have been granted. Any new actions against the table would have been queued up behind your LOCK request. This has confused more than one DBA as they didn't realize that the LOCK was going to be such a tight bottleneck.

These kinds of whole table locks live above the blocking/locking coordination of the individual storage engines or the transaction control code. They are managed in the "server layer" of our code.

This separation of scope is one reason why blending transactional and non-transactional tables in the same data management process is generally frowned on. Either be all-transactional (InnoDB) or not. The behavior will be easier to predict allowing your developers to use either the transaction control commands (BEGIN/COMMIT/ROLLBACK/... ) or the LOCK commands with confidence.

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.