Dear Akshay,
Thanks for you reply, You are really great. Now , one more confusion. mysql> create table test.new as select id,name from City; Query OK, 4079 rows affected (0.18 sec) Records: 4079 Duplicates: 0 Warnings: 0 mysql> select * from new limit 15; +----+-------------------+ | id | name | +----+-------------------+ | 1 | Kabul | | 2 | Qandahar | | 3 | Herat | | 4 | Mazar-e-Sharif | | 5 | Amsterdam | | 6 | Rotterdam | |11|hhh | | 20 | ´s-Hertogenbosch | | 21 | Amersfoort | | 22 | Maastricht | | 23 | Dordrecht | | 24 | Leiden | | 25 | Haarlemmermeer | | 26 | Zoetermeer | | 27 | Emmen | | 28 | Zwolle | Now, *Session 1* *Session 2* mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from new where id between 9 and 15 for update; +----+------+ | id | name | +----+------+ | 11 | hhh | +----+------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into new values(17,'fff'); (session is hanging) mysql> insert into new values(18,'fff'); (session is hanging) mysql> insert into new values(19,'fff'); (session is hanging). mysql> insert into new values(20,'fff'); Query OK, 1 row affected (0.00 sec) mysql> insert into new values(21,'fff'); Query OK, 1 row affected (0.01 sec) mysql> insert into new values(8,'fff'); (session hang) mysql> mysql> insert into new values(7,'fff'); (session hang) mysql> insert into new values(6,'fff'); (session hang) mysql> insert into new values(5,'ggg'); Query OK, 1 row affected (0.00 sec) I tried the above scenario with index and without index. Without index it is showing the same behaviour as before. Using non-unique index, it is not locking the next value (20)immediately after the gap. But it is locking a row with id=6, the value immediately before the gap. Can you explain me the same? When I tried the same scenario with unique index, this is what I got from another session: mysql> insert into new values(20,'jjj'); (hang) mysql> insert into new values(6,'jjj'); ERROR 1062 (23000): Duplicate entry '6' for key 'idx1' Here it is locking 20 , but not 6. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Wed, Sep 3, 2014 at 10:59 PM, Akshay Suryavanshi < akshay.suryavansh...@gmail.com> wrote: > 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 >>>> >> > >>>> >> > >>>> >> >>>> > >>>> > >>>> >>> >>> >> >
