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
