Bug #9597 read lock stays when querying view from stored procedure
Submitted: 4 Apr 2005 5:07 Modified: 18 Jul 2005 19:10
Reporter: Rob LoPresti Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Windows (Windows XP)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[4 Apr 2005 5:07] Rob LoPresti 
Description:
I have a stored procedure which queries a table through a view and then tries to update that table.  I get a message saying that I cannot update the table because of a read lock.  I've played around with the circumstances and it appears this only happens when it's a view inside a stored procedure.  If I simply execute the queries outside of a stored procedure, or if I query the tables directly it works just fine.

May be related to bug #9565, but I felt it was different enough to warrant a separate report.

How to repeat:
DROP DATABASE IF EXISTS bug_demo;
CREATE DATABASE IF NOT EXISTS bug_demo;
USE bug_demo;

CREATE TABLE bug_table (
	bug_table_seq	INTEGER NOT NULL
) TYPE=InnoDB;

CREATE OR REPLACE VIEW bug_view AS SELECT * from bug_table;

DROP PROCEDURE IF EXISTS add_bug_record;
delimiter //
CREATE PROCEDURE add_bug_record ( )
BEGIN
	DO (
		SELECT	@next := IFNULL(max(bug_table_seq),0) + 1
		FROM	bug_view
	);
	INSERT INTO bug_table VALUES (1,@next);
END //

delimiter ;

call add_bug_record();
[4 Apr 2005 5:12] Rob LoPresti 
Slight typo in there, the VALUES (1,@next) should just be VALUES (@next).  I cut and paste from the wrong file, although they both produce the same error.
[13 Jul 2005 22:39] Bugs System 
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/27035
[14 Jul 2005 22:12] Bugs System 
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/27108
[14 Jul 2005 22:22] Bugs System 
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/27111
[15 Jul 2005 7:04] Oleksandr Byelkin 
pushed to 5.0.10
[15 Jul 2005 9:33] Bugs System 
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/27140
[18 Jul 2005 19:10] Paul DuBois 
Noted in 5.0.10 changelog.