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 >> > >> > >> > >
