How to avoid deadlock in a mysql function with only select queries?

Question

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):

select path(621163,"de") 
Deadlock found when trying to get lock; try restarting transaction

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?

There are my functions:

mysql> 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 > 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> 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

There are a answers [here](http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql) that may be of use. — Bojangles, Feb 19 '12 at 12:46

score 0 · Answer 1 · answered Feb 19 '12 at 12:41

You don't have to insert or update data to lock a table (or pages of a table). Selecting data can lock the table (according to the default lock policy of RDBMS engine)That's why we have specific directives to tell the engine not to put a lock over a table while selecting data.(In SQL Server you can obtain this by putting With NoLock directive after the name of a talbe). When you are selecting multiple rows in a table there's a possibility that multiple pages a table are locked. When you run two different queries over the same table and query 1 lock a page that query 2 needs and query 2 lock a page that query 1 needs you will get a deadlock.One solution is to force the query to use a certain index that will guaranty that pages will be locked in a certain order.Usually deadlock conditions are the most difficult issues to cope with.

Thank you. Meanwhile I bracketed the SELECTs in my functions with SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; and SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; respectively. Up to now no new error messages. — Alex Monthy, Feb 19 '12 at 19:37

How to avoid deadlock in a mysql function with only select queries?

1 Answers1