Bug #12205 ndb: LOCK IN SHARE MODE and FOR UPDATE only works on PKs and UKs
Submitted: 27 Jul 2005 13:15 Modified: 29 Jan 2007 20:16
Reporter: Max Mether Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.0 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[27 Jul 2005 13:15] Max Mether 
Description:
SELECT ... FOR UPDATE / LOCK IN SHARE MODE only locks rows if the query uses a PK or a UK. 

This already is a bug since it should work for all queries. 

The second problem is that it is the optimizer who decideds whether an index will be used or not, so it is not very easy to know whether a query will lock rows or not even if you're aware of the above

How to repeat:
start 2 transactions and do SELECTS ... FOR UPDATE without using a PK in both. 
Then do the same using a PK with an equality condition and with a range condition.

 

Suggested fix:
For now:
Ignore all LOCK IN SHARE MODE / FOR UPDATE  for now and clearly document this.

In the long run:
SELECT ... LOCK IN SHARE MODE / FOR UPDATE should work with all types of queries regardless on whether an index is used or not
[29 Jan 2007 20:16] Max Mether 
Is there any update on this?
[13 Mar 2014 13:34] Omer Barnir 
This bug is not scheduled to be fixed at this time.