title: How to avoid deadlock in a mysql function with only select queries?
body: <p>I have two functions for creating a virtual path name from a table named tree_elements. The function path(id,language) is called on any update of the table. From time to time the update of the table leads to deadlocks with the error message (example):</p>
  
  <pre><code>select path(621163,"de") 
  Deadlock found when trying to get lock; try restarting transaction
  </code></pre>
  
  <p>I do not understand why there is any locking. The functions use only selects, no update, no insert, no delete. How can I avoid this phenomenon?</p>
  
  <p>There are my functions:</p>
  
  <pre><code>mysql&gt; show create function path\G
  *************************** 1. row ***************************
              Function: path
              sql_mode:
       Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `path`(id integer, language char(2)) RETURNS varchar(255) CHARSET utf8
      READS SQL DATA
      DETERMINISTIC
      COMMENT 'Converts a record id into an url string with path'
  begin
          declare ret varchar(255);
          declare r varchar(255);
    declare element varchar(255);
          set ret = path_component(id,language);
          set id = (select id_parent from tree_elements t where t.id=id);
          while (id &gt; 0) do
      set element = concat(path_component(id,language), '/');
      if (locate( element, ret )) then return concat( 'Infinite loop in path for id ', id ); end if;
                  set ret = concat(path_component(id,language), '/', ret );
                  set id = (select id_parent from tree_elements t where t.id=id);
          end while;
          return ret;
  end
  character_set_client: latin1
  collation_connection: latin1_swedish_ci
    Database Collation: utf8_general_ci
  
  mysql&gt; show create function path_component\G
  *************************** 1. row ***************************
              Function: path_component
              sql_mode:
       Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `path_component`( id integer, language char(2)) RETURNS varchar(500) CHARSET utf8
      READS SQL DATA
      DETERMINISTIC
  begin
     declare f varchar(255);
          set f = (select case language
                  when 'en' then title_en
                  when 'de' then title_de
                  else title_en
                  end
          from tree_elements t where t.id=id);
          if (isnull(f) or length(trim(f)) = 0) then
                  set f = (select title_en from tree_elements t where t.id=id);
          end if;
          return url(f);
  end
  character_set_client: latin1
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci
  </code></pre>