MySQL Bugs: #37029: Error: 1422 SQLSTATE: HY000 while SELECT with FOR UPDATE in stored function

Bug #37029	Error: 1422 SQLSTATE: HY000 while SELECT with FOR UPDATE in stored function
Submitted:	28 May 2008 8:42	Modified:	7 Jul 2008 14:19
Reporter:	Alex Tumanov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.41-community-nt	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	row-locking, SF, transaction

Description:
In multiple conncetion execution of stored procedure (SP) with START TRANSACTION - COMMIT/ROLLBACK statments and use stored function (SF) only two SELECT SUM(..) INTO declare variable FROM .. WHERE .. with FOR UPDATE or LOCK IN SHARE MODE options and return one of declared variable, sometimes SP rise error 1422.

If FOR UPDATE or LOCK IN SHARE MODE options remove from SF error will newer raise.

How to repeat:
Repetable call described SP in two or more connections. 

Suggested fix:
Do not use FOR UPDATE or LOCK IN SHARE MODE options in SF or use it outside SF, but first incomplete application aim and second one uncomfortable because the SF may uses in many other SP.

Thank you for the report.

Could you please provide definition of the procedure demonstrating such behavior?

I can't mix demo to catch the error but I found source of problem.
This error is hidden clause of DEADLOCK. If rewrite root SP to avoid SF using, but still use SELECT .. FOR UPDATE statments, it will raise 1213 error in same situation. Therefore when use SELECT .. FOR UPDATE in SF its need add ERROR HANDLER for 1422 error to force ROLLBACK in root SP and restart transaction.

Thank you for the feedback.

If I understood correctly implicit commit happens in SF under some circumstances.

If you still think this is MySQL bug please describe in more details what you want us to fix.

I think its need to change error reporting in SF to clear situation, because main is information about DEADLOCK 1213, but not usability error 1422.

Thank you for the feedback.

I can not say if this will be fixed until I see why this happens. Additionally I can not repeat described behavior with test data: always get error "1205: Lock wait timeout exceeded; try restarting transaction" Please upgrade to current version and if problem still exists try to create repatable test case or provide SP and SF, so we can guess what you are doing (you can add them as private comment).

So I can't repeat DEADLOCK situation it in simply test.
My test hungs until timeout exceed and not register DEADLOCK!
If You can recreate DEADLOCK situation on simply test then I can convert it on described one.

You can use test from bug #35821 for example:

TRX1> set autocommit=0;
TRX1> delete from locktest where c='1';

TRX2> set autocommit=0;
TRX2> insert into locktest values ('1');
(Waiting)

TRX3> set autocommit=0;
TRX3> insert into locktest values ('1');

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".