Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am not sure about this part of the transaction manual

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

Maybe I'm just nitpicking, but I seem to read that there is a special condition in which "only the index record found" is locked (being: "unique index with unique search condition"). For all other we get some sort of range.

But what about 'other search conditions" that are nog a range? For istance, if I lock a query like SELECT * FROM table WHERE table.col = 2, there is no range or gaps in question, even if col is not unique, isn't there? Should we get a "only record found" lock here as well?

share|improve this question
1  
But there is a range, all rows with table.col = 2 and PK = whatever – ypercube Feb 15 at 13:45
then maybe I don't understand 'range', but there is no range in the sense there could be gaps, is there? There will be a block on that col=2 condition, not on any PK, is there? – Nanne Feb 15 at 13:48

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.