Hi Geetanjali, It seems, you are confused with gap locking. Can you tell if there is a record with value 12 and 17 in the new table? Select count(*) from new where c1 = 17;
AFAIK, if you run this query "Select * from new where c1 between 12 and 17 for update;" and if you don't have value 17 in the table but the next value is 30 then innodb will lock the records between 12 to 30 and it will not let you insert records with value 20, 25 etc. This is called gap locking so if there is no such record, innodb locks the gap between last record and next record (if it exists) Kindly check with table, if you have both the values. regards, Nilnandan On Tue, Sep 2, 2014 at 11:59 AM, geetanjali mehra < mailtogeetanj...@gmail.com> wrote: > Dear Akshay, > > ASFIK, normal selects are always non-locking read and they do not put any > locks. > Select..., Select..........where......,Select ........where......between > > Does above select statement will use next-key locking and/or gap locking? > I dont think so. > > Please correct me if I am wrong. > > > Best Regards, > Geetanjali Mehra > Senior Oracle and MySQL DBA Corporate Trainer and Database Security > Specialist > > > > On Fri, Aug 29, 2014 at 11:46 AM, Akshay Suryavanshi < > akshay.suryavansh...@gmail.com> wrote: > > > 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 > >> > > >> > > >> > > > > >
