Bug #93982 Ways to retain Read-Locks for 'SELECT .. FOR UPDATE' across commit points
Submitted: 18 Jan 2019 14:29 Modified: 22 Jan 2019 13:50
Reporter: Vibin Varghese Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.3.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: Cursor Processing, For Update, Retain Read Lock across Commit Points

[18 Jan 2019 14:29] Vibin Varghese
Hi There, 

This is not a bug. 

I've an issue with an application migrated to MySQL, where I'm facing trouble with lack of WITH HOLD construct in MySQL Cursor Processing. 

Basically, I need to have MySQL retain the Read Locks attained at the time of a opening a 'SELECT .. FOR UPDATE' cursor, across COMMIT points for the same process. 

Is there a way around to achieve this without major application changes?


How to repeat:

Suggested fix:
[18 Jan 2019 15:37] Sinisa Milivojevic
Hello Mr. Varghese,

MySQL has two different ways of defining cursors. One is in its stored routines and the other is in our Connectors. If you are using Java try our Connector/J and see whether there is a feature which would enable holdability.

Our stored routines do not support this feature.

Since SQL standard specifies WITH HOLD or WITHOUT HOLD, I think that you made a nice feature request.

Verified as a feature request.
[19 Jan 2019 8:17] Frederic Descamps
Hello Mr. Varghese,

I would just let you know that since you are migrating to MySQL, please do not migrate to an old/EOL version like 5.5. We recommend migrating to MySQL 8.0 (especially for a new migration or a new application). MySQL 8.0 has much better features, security and performance. And of course, if you have Bug Reports or Feature Requests for MySQL 8.0 too, please let us know.

[22 Jan 2019 13:50] Vibin Varghese
Thanks for the response Sinisa and Frederic.
Just to add one more comment. 
My application is connecting to the database over the ODBC driver. 

Is feature that you suggested to explore over Connector/J available over ODBC as well?