MySQL Bugs: #9486: can't perform multi-update in stored procedure

Bug #9486	can't perform multi-update in stored procedure
Submitted:	30 Mar 2005 13:58	Modified:	14 Jun 2005 7:18
Reporter:	Dmitry L	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Windows (Win2k)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Can't perform update inside stored procedure:
Error is: "Table 'op' was locked with a READ lock and can't be updated"

How to repeat:
here's procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `parallax_kernel`.`updateTitles`$$
CREATE PROCEDURE `parallax_kernel`.`updateTitles`(OUT affected INT)
BEGIN
  UPDATE objects_path op,
         objects_hier oh
     SET op.title = oh.title
   WHERE op.pid = oh.id
     AND op.map = oh.map;
  SELECT ROW_COUNT() INTO affected;
END$$
DELIMITER ;

Tables:
CREATE TABLE `objects_path` (
  `id` int(10) unsigned NOT NULL default '0',
  `pid` int(10) unsigned NOT NULL default '0',
  `map` smallint(6) unsigned NOT NULL default '0',
  `level` tinyint(4) unsigned NOT NULL default '0',
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`,`map`,`level`),
  KEY `pid` (`pid`),
  KEY `level` (`level`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `objects_hier` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `flags_0` int(10) unsigned NOT NULL default '0',
  `flags_1` int(10) unsigned NOT NULL default '0',
  `flags_2` int(10) unsigned NOT NULL default '0',
  `proto` smallint(6) unsigned NOT NULL default '0',
  `sort` smallint(6) NOT NULL default '0',
  `template` int(10) unsigned default NULL,
  `map` smallint(6) unsigned NOT NULL default '0',
  `mapped_from` smallint(6) unsigned default NULL,
  `lnk4` int(10) unsigned default NULL,
  `crt` double(15,4) unsigned NOT NULL default '0.0000',
  `mdf` double(15,4) unsigned NOT NULL default '0.0000',
  `path` bigint(20) unsigned default NULL,
  `owner` int(10) unsigned default NULL,
  `size` int(10) unsigned NOT NULL default '100',
  `title` varchar(255) default NULL,
  `remark` varchar(255) default NULL,
  `xml` mediumtext,
  PRIMARY KEY  (`id`,`map`),
  KEY `proto` (`proto`),
  KEY `title` (`title`),
  KEY `pid` (`pid`),
  KEY `id` (`id`,`mapped_from`),
  KEY `lnk4` (`lnk4`,`map`),
  KEY `sort` (`sort`),
  KEY `path` (`path`),
  KEY `owner` (`owner`),
  KEY `size` (`size`),
  KEY `template` (`template`,`map`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Call is:
call updateTitles(@a);

There are no active locks and opened transactions on the server and call is done by @root

Please also note that second invocation of updateTitles() will block current connection...

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24000

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24057

Fixed in 5.0.5

Noted in 5.0.5 changelog.

This bug persists in 5.0.6, as the following script shows:

CREATE TABLE airports (
code char(3) NOT NULL,
city char(100) default NULL,
latitude float NOT NULL,
longitude float NOT NULL,
PRIMARY KEY (code)
) ENGINE=MyISAM;

INSERT INTO airports VALUES ('JFK', 'New York, NY', 40.75, -73.97);
INSERT INTO airports VALUES ('LAX', 'Los Angeles, CA', 34.05, -118.22);
INSERT INTO airports VALUES ('LHR', 'London, England', 51.5, -0.45);
INSERT INTO airports VALUES ('HEL', 'Helsinki, Finland', 60.17, 24.97);
INSERT INTO airports VALUES ('CDG', 'Paris, France', 48.86, 2.33);
INSERT INTO airports VALUES ('STL', 'St Louis, MO', 38.63, -90.2);
INSERT INTO airports VALUES ('ARN', 'Stockholm, Sweden', 59.33, 18.05); 

-- Workaround for another bug
SET GLOBAL log_bin_trust_routine_creators=TRUE; 

DROP FUNCTION IF EXISTS GeoDistKM; 
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
  DECLARE pi, q1, q2, q3 FLOAT;
  DECLARE rads FLOAT DEFAULT 0;
  SET pi = PI();
  SET lat1 = lat1 * pi / 180;
  SET lon1 = lon1 * pi / 180;
  SET lat2 = lat2 * pi / 180;
  SET lon2 = lon2 * pi / 180;
  SET q1 = COS(lon1-lon2);
  SET q2 = COS(lat1-lat2);
  SET q3 = COS(lat1+lat2);
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) ); 
  RETURN 6378.388 * rads;
END;
|
DELIMITER ; 

CREATE TABLE flights (
  id INT PRIMARY KEY AUTO_INCREMENT,
  depart CHAR(3),
  arrive CHAR(3),
  distance DECIMAL(10,2),
  direction DECIMAL(10,2)
) ENGINE=MYISAM;

INSERT INTO flights
  SELECT
  NULL,
  depart.code,
  arrive.code,
 ROUND(GeoDistKM(depart.latitude,depart.longitude,arrive.latitude,arrive.longitude),2) AS Km,
  ROUND(DEGREES(ATAN(arrive.latitude-depart.latitude,arrive.longitude-depart.longitude)),2)
  FROM airports AS depart
  INNER JOIN airports AS arrive ON depart.code <> arrive.code
  HAVING Km <= 6000;
  
CREATE TABLE routes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  depart CHAR(3),
  arrive CHAR(3),
  hops SMALLINT,
  route CHAR(50),
  distance DECIMAL(10,2),
  direction DECIMAL(10,2)
) ENGINE=MYISAM; 

CREATE OR REPLACE VIEW nexthops AS
  SELECT 
    routes.depart,
    flights.arrive,
    routes.hops+1 AS hops,
    CONCAT(routes.route, ',', flights.arrive) AS route,
    MIN(routes.distance + flights.distance) AS distance,
    routes.direction
  FROM routes INNER JOIN flights 
    ON routes.arrive = flights.depart
    AND LOCATE(flights.arrive,routes.route) = 0
  WHERE flights.direction+360>routes.direction+270 
    AND flights.direction+360<routes.direction+450
  GROUP BY depart,arrive;
  
SET GLOBAL log_bin_trust_routine_creators=TRUE; 
DROP PROCEDURE IF EXISTS BuildRoutes;
DELIMITER |
CREATE PROCEDURE BuildRoutes()
BEGIN
  DECLARE rows INT DEFAULT 0;
  TRUNCATE routes;

  -- STEP 1, LISTING 5: SEED ROUTES WITH 1-HOP FLIGHTS
  INSERT INTO routes
    SELECT 
      NULL,
      depart,
      arrive,
      1,
      CONCAT(depart,',',arrive),
      distance,
      direction
  FROM flights;
  SET rows = ROW_COUNT();

  WHILE (rows > 0) DO

    -- STEP 2, LISTING 8: ADD NEXT SET OF ROUTES
    INSERT INTO routes
      SELECT 
        NULL,
        nexthops.depart,
        nexthops.arrive,
        nexthops.hops,
        nexthops.route,
        nexthops.distance,
        nexthops.direction 
      FROM nexthops
      LEFT JOIN routes ON nexthops.depart = routes.depart 
            AND nexthops.arrive = routes.arrive
      WHERE routes.depart IS NULL AND routes.arrive IS NULL;
    SET rows = ROW_COUNT();

  END WHILE;

END;
|
DELIMITER ;

ERROR 1099 (HY000): Table 'routes' was locked with a READ lock and can't be updated

Hi, Peter!

Original problem mentioned in this bug report was impossibility to perform multi-update in  stored procedure due to problem with table locking (I have updated synopsis to clarify this). 

After investigating your issue I think that your problem has nothing to do with original one so it should be reported and handled separately. And as I've noticed you already have done that! :)

Thank you!