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:
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.41-community-nt OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: row-locking, SF, transaction

[28 May 2008 8:42] Alex Tumanov
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.
[31 May 2008 18:58] Sveta Smirnova
Thank you for the report.

Could you please provide definition of the procedure demonstrating such behavior?
[7 Jun 2008 7:16] Alex Tumanov
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.
[7 Jun 2008 9:32] Sveta Smirnova
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.
[7 Jun 2008 10:14] Alex Tumanov
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.
[7 Jun 2008 12:49] Sveta Smirnova
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).
[7 Jun 2008 13:36] Alex Tumanov
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.
[7 Jun 2008 14:19] Sveta Smirnova
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');

TRX3> set autocommit=0;
TRX3> insert into locktest values ('1');
[7 Jul 2008 23:00] Bugs System
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".