Hello Jerome, On 03/14/2017 01:18 PM, jerome brauge wrote: > Hello Alexander, > I feared something like that. > > Comment of class Select_materialize indicate : > /** > Select_materialize -- a mediator between a cursor query and the > protocol. In case we were not able to open a non-materialzed > cursor, it creates an internal temporary HEAP table, and insert > all rows into it. When the table reaches max_heap_table_size, > it's converted to a MyISAM table. Later this table is used to > create a Materialized_cursor. > */ > > Perhaps that it never able to open non-materialzed cursor. > In any case, I suppose it is not easy to implement. > > In addition on Oracle, in the "for update of" clause, we can specify each > table on which we want hold locks. > Exemple : > CURSOR fall_jobs_cur IS > SELECT w.task, w.expected_hours, > w.tools_required, w.do_it_yourself_flag > FROM winterize w, husband_config hc > WHERE year = TO_CHAR (SYSDATE, 'YYYY') > FOR UPDATE OF husband_config.max_procrastination_allowed; > > In this example, lock are hold only on table husband_config. > > But, like I am a lucky man, I only have 4 procedures in 17100 that use > cursors with "current of " ... > So I would change them.
Thanks for your clarification! Right, in this case it's easier to fix the 4 procedures. > > Thanks. > Jérôme. > > >> -----Message d'origine----- >> De : Alexander Barkov [mailto:[email protected]] >> Envoyé : mardi 14 mars 2017 05:33 >> À : jerome brauge >> Cc : MariaDB Developers ([email protected]) >> Objet : Re: bb-10.2-compatibility - Updatable cursor >> >> Hello Jerome, >> >> On 03/10/2017 05:21 PM, jerome brauge wrote: >>> Hello Alexander, >>> Nice to hear you again. >>> You can find description here : >>> https://mariadb.com/kb/en/sql-99/positioned-update-statement/ >>> https://mariadb.com/kb/en/sql-99/positioned-delete-statement/ >>> >>> I attached an example (positioned_update.sql). >>> It's a useful feature to update or delete rows in a table without unique key >> and lock all records when cursor is open. >> >> I can add tasks, but I'm afraid this is something not doable in short terms. >> >> MariaDB cursors are materialized. I.e. a temporary table is created and >> populated during OPEN cursor, so FETCH is actually done from this temporary >> table rather than the original table. >> >> If the table does not have a unique key, then we have troubles with "WHERE >> CURRENT OF" referring the original table. >> >> Implementing "WHERE CURRENT OF" seems to need adding of non- >> materialized cursors first. >> >>> >>> Regards, >>> Jérôme. >>> >>> >>>> -----Message d'origine----- >>>> De : Alexander Barkov [mailto:[email protected]] Envoyé : vendredi 10 >>>> mars 2017 11:24 À : jerome brauge Cc : MariaDB Developers >>>> ([email protected]) >>>> Objet : Re: bb-10.2-compatibility - Updatable cursor >>>> >>>> Hello Jerome, >>>> >>>> can you please give examples? >>>> >>>> Thanks! >>>> >>>> >>>> On 03/08/2017 07:33 PM, jerome brauge wrote: >>>>> Hello Alexander, >>>>> >>>>> Can you add a task for updatable cursor (for positioned update and >>>> delete). >>>>> >>>>> >>>>> >>>>> Regards, >>>>> >>>>> Jérôme. >>>>> _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
