Thanks for your reply. I read those docs. Still my doubt is at the same stage. Please clarify the same to me. Should not other sessions be allowed to insert the rows beyond that range.?
As far as I understand, Innodb brought the concept of next-key locks so as to prevent phantom problem. So, it is clear to me that issuing the below query Select * from new where c1 between 12 and 17 for update; will not allow other sessions to insert any value between 12 and 17. But if i am trying to insert 20 from other session, it is not allowed. Why this is so? The session is hanging. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Thu, Aug 28, 2014 at 2:26 AM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > On 8/26/2014 1:12 AM, geetanjali mehra wrote: > >> Hello to all, >> In repeatable read isolation level, when we issue: >> >> Select * from new where c1 between 12 and 17 for update; >> >> this range will be locked by innodb by using next-key locks. >> >> But, why is is preventing any other session to insert any value beyond >> that >> range; any value above the range and any value below the range. I am >> unable >> to understand this. >> > > I believe you are confusing gap locking (the space between the values) and > next-key locking (the space after the range). > > http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html > > See also: > http://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html > http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html > > > >> Best Regards, >> Geetanjali Mehra >> Senior Oracle and MySQL DBA Corporate Trainer and Database Security >> Specialist >> >> > Yours, > -- > Shawn Green > MySQL Senior Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
