Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
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 Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. === original thread === On 2018-08-18 23:59, shawn l.green wrote: Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net 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
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, j...@lxvi.net 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
Re: how to select the record with one sql statement?
Hello sea, On 8/13/2018 7:01 PM, sea wrote: helle, I have a table, like this: pigId dayweigt pig1 2018-1-121 pig2 2018-1-131 pig3 2018-1-141 pig1 2018-1-222 pig2 2018-1-231 pig3 2018-1-240 pig1 2018-1-323 pig2 2018-1-330 pig3 2018-1-341 . only the pig1'weight increase continuously for 3 days. Giving the input: num_of_day(weight increasing continuously for num_of_day); expecting the output: certain_day, pigId;from certain_day, pigId'weight increasing continuously for num_of_day. How to select the records in one sql statement? thanks I've thought about this a bit (since your question appeared on the list) and I break down the tasks you need to perform in my head like this. (Others on the list may have different ways to approach the same problem) task 1 - For each bucket, a pigId value, assemble an ordered list (not a set) of each weight sorted by time. (not hard) task 2 - Within each ordered list, compare the values of every consecutive pair. (several ways to do this) task 3 - Iterate over those "consecutive value differences" generated in task 2 looking for the longest sequence of positive non-zero values for each pigId. (this is not really a set-oriented process so normal SELECT or GROUP BY command patterns will not handle it with any efficency) I'm afraid that attempting all of that sequencing and iteration using just a single set-based SQL command is not going to be practical. Using one or more cursors within a stored procedure is your best bet for this type of sequential trend analysis. I could easily imagine the first step as a INSERT...SELECT...ORDER BY... command going to a new table with an autoincrement column on it (to provide a global sequence number across all of your individual pigId values) . The second step could do a self join to that table where the ON clause could look like a.pigId = b.pigID AND a.seq-1 = b.seq But at that point, counting the length of sequences (and remembering when each trend became positive) needs a loop. That's where even complicated set-wise SQL fails you and you need to shift into using the SQL of stored programs. Regards, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: what is the rationale for not allowing LOCK TABLES in a stored procedure
Hello Jeff, On 8/13/2018 12:05 PM, j...@lxvi.net 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
