List:General Discussion« Previous MessageNext Message »
From:Paul C. Power Date:May 17 2007 6:23pm
Subject:quirky MySQL:innodb locking...
View as plain text  
OnMySQL 5.1.14-beta-community (windows) 5.1.7-beta (linux)
 
Consider:
CREATE TABLE 
 Entity 
(
 Entity_ID  char(4) NOT NULL,
 Entity_Name varchar(50) NULL,
  
 PRIMARY KEY ( Entity_ID ) 
) ENGINE=InnoDB ;
 
now create a dumb function to use this data:
 
DELIMITER $$
 
DROP FUNCTION IF EXISTS voxinternal.ufn_DumbFunction $$
CREATE FUNCTION voxinternal.ufn_DumbFunction( _ID char(4) )
RETURNS char(4) READS SQL DATA 
BEGIN
    DECLARE _EntID char(4);
 
    SET _EntID = ( SELECT Entity_ID FROM Entity WHERE Entity_ID = _ID); 
 
    return _EntID ;
END $$
 
DELIMITER ;
 
This will lead to a table lock on Entity. BUT, if you replace
    SET _EntID = ( SELECT Entity_ID FROM Entity WHERE Entity_ID = _ID); 
with
    SELECT Entity_ID INTO _EntID FROM Entity WHERE Entity_ID = _ID;
 
there will not be a table lock
 
the following also creates a table lock:
 
IF EXISTS( SELECT Entity_ID FROM Entity WHERE Entity_ID = _ID) THEN
...
END IF;
 
i find this confusing. if people have explanations, i am listening.
more than anything i wanted to let people know.
 
PCPIII
 

Thread
quirky MySQL:innodb locking...Paul C. Power17 May
  • RE: quirky MySQL:innodb locking...Paul C. Power17 May