shawn l.green Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Aug 18, 2018; 12:59
Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
On 8/13/2018 12:05 PM, firstname.lastname@example.org 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!