Can you try the same on a big table, I think optimizer is choosing a FTS over an index lookup.
On Wed, Sep 3, 2014 at 8:25 PM, geetanjali mehra <mailtogeetanj...@gmail.com > wrote: > This is what I am doing. > > mysql> select * from new; > +------+------+ > | c1 | c2 | > +------+------+ > | 5 | 5 | > | 10 | 10 | > | 15 | 15 | > | 20 | 20 | > | 30 | 30 | > +------+------+ > > > Now, > > Session 1 > > > mysql> begin; > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> select * from new where c1 between 10 and 25 for update; > > +------+------+ > > | c1 | c2 | > > +------+------+ > > | 10 | 10 | > > | 15 | 15 | > > | 20 | 20 | > > +------+------+ > > 3 rows in set (0.00 sec) > > > > > > > > > > mysql>begin; > > mysql> insert into new values(29,29); > > (session is hanging) > > mysql> insert into new values(31,31); > > (session is hanging > > > The last value on my table is 30. Still it is not allowing me to insert > 31. > > I tried the scenario without index on column c1 and then with non-unique > index on column c1 and then unique index. I am getting the same result. It > seems that it is putting up lock on complete table. > > The scenario is working fine only when I made c1 primary key. After > making c1 primary key, I am able to insert value higher than 30. > > Can you please try the same scenario at your end? > > > > > Best Regards, > Geetanjali Mehra > Senior Oracle and MySQL DBA Corporate Trainer and Database Security > Specialist > > > > On Tue, Sep 2, 2014 at 1:53 PM, Akshay Suryavanshi < > akshay.suryavansh...@gmail.com> wrote: > >> Hello Geetanjali, >> >> Apologies if I have confused you with the "normal Select" notation. I >> meant to write with repeatable-read mode in mind, but looks like that is >> not an issue, since you already tested this scenario with that isolation >> mode. >> >> Moving further to the original issue. >> >> Do you have an index on column c1. Is the query "Select * from new where >> c1 between 12 and 17 for update;" using index ? If there is no index on >> the particular column, then InnoDB locks out whole table from other >> transactions. Which is the case you mentioned. Also this can be dangerous. >> Once you have indexed the column checkout the innodb status, you will see >> the necessary locking. Also try updating values beyond the boundary values. >> >> So most important fact to know here is the involvement of secondary >> indexes to introduce record locking, gap locking, and how their absence >> will affect the transaction. As to "Why" this is happening ? It should be >> understood that in InnoDB secondary keys are appended to PRIMARY index, so >> if there is no index to search the records PRIMARY index values cannot be >> filtered. In absence of secondary indexes a full scan is needed. And >> finally Innodb table is one big Index (Clustered table). >> >> *By default, InnoDB operates in REPEATABLE READ >> <http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html#isolevel_repeatable-read> >> transaction >> isolation level and with theinnodb_locks_unsafe_for_binlog >> <http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog> >> system >> variable disabled. In this case, InnoDB uses next-key locks for searches >> and index scans, which prevents phantom rows.* >> >> So Index scan above is infact a Full-table-scan (full index scan) >> >> Please try it out and let me know if you observe any difference. >> >> Cheers!!! >> Akshay >> >> >> 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 >>> >> > >>> >> > >>> >> >>> > >>> > >>> >> >> >
