I would like to add a correction. I have been unable to remove the table lock from the linux install with these techniques.
> -----Original Message----- > From: Power, Paul C. [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 17, 2007 12:24 PM > To: mysql@lists.mysql.com > Subject: quirky MySQL:innodb locking... > > 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 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
