Re: what is the rationale for not allowing LOCK TABLES in a stored procedure

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.

On 2018-08-18 23:59, shawn l.green wrote:
> Hello Jeff,
> 
> On 8/13/2018 12:05 PM, [email protected] 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