Bug #9597 read lock stays when querying view from stored procedure
Submitted: 4 Apr 2005 7:07 Modified: 18 Jul 2005 21:10
Reporter: Rob LoPresti
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Microsoft Windows (Windows XP)
Assigned to: Bugs System Target Version:

[4 Apr 2005 7: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 7: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.
[14 Jul 2005 0: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
[15 Jul 2005 0: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
[15 Jul 2005 0: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 9:04] Oleksandr Byelkin 
pushed to 5.0.10
[15 Jul 2005 11: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 21:10] Paul DuBois 
Noted in 5.0.10 changelog.