Geetanjali, There is a difference between next-key locking, gap locking and locking reads.
Next-key locking and gap-locking are used with normal Selects statement in Innodb, whereas locking reads wont release a lock on the whole column until transaction completed, and not just selected values. May be you can try your example with SELECT... LOCK IN SHARE MODE; Cheers!!! Akshay Suryawanshi On Fri, Aug 29, 2014 at 11:22 AM, geetanjali mehra < mailtogeetanj...@gmail.com> wrote: > 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 > > > > >
