List:General Discussion« Previous MessageNext Message »
From:Akshay Suryavanshi Date:August 29 2014 6:16am
Subject:Re: next-key lock
View as plain text  
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 <
mailtogeetanjali@stripped> 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.green@stripped>
> 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
> >
> >
>

Thread
next-key lockgeetanjali mehra26 Aug 2014
  • Re: next-key lockshawn l.green27 Aug 2014
    • Re: next-key lockgeetanjali mehra29 Aug 2014
      • Re: next-key lockAkshay Suryavanshi29 Aug 2014
        • Re: next-key lockgeetanjali mehra2 Sep 2014
          • Re: next-key lockNilnandan Joshi2 Sep 2014
          • Re: next-key lockAkshay Suryavanshi2 Sep 2014
            • Re: next-key lockgeetanjali mehra3 Sep 2014
              • Re: next-key lockAkshay Suryavanshi3 Sep 2014
                • Re: next-key lockgeetanjali mehra4 Sep 2014
                  • Re: next-key lockAkshay Suryavanshi4 Sep 2014
                    • Re: next-key lockgeetanjali mehra4 Sep 2014