I'm trying to understand how mysql prevents phantom in repeatable read isolation level. Apparently, mysql uses next key locking, which locks the gap between index, to prevent insertion from other transactions. The underlying message is, without this next key locking algorithm, phantom would have occured.
Since the doc says gap lock can be turned off explicitly, I want to try it. However, the doc says you can turn it off by switching to read committed isolation level. But that's not the point, can I turn it off but stay in repeatable read isolation level?
On a related note, all the doc that talks about phantom use "select for update", which is locking read. I wonder, how does mysql prevent phantom for normal read operation? Is it because of MVCC? The reason for asking is, for normal read, I don't expect next key locking to take effect, then how is phantom prevented?