Hi Geeetanjali, I retried the scenario you mentioned, however I am getting consistent locking results on both unique and non-unique index, the preceding records are getting updated however just the next record is being locked "next-key locking". If I try to insert a new record after the "next key" it is getting inserted.
I wonder how it is different in your situation. All I can say for now is InnoDB indexes are not ordered, so unsure exactly if 20 falls just right after the gap. Also did you delete the records from 7 through 19 or they are just not inserted in your test table, because that it wont be a gap, for the index records they are just values 6 and 20 which might sit next to each other in the innodb page. There are multiple blogs by experts on how the locking internals work, http://dom.as/2011/07/03/innodb-index-lock/ https://www.facebook.com/note.php?note_id=479123255932 https://blogs.oracle.com/mysqlinnodb/entry/introduction_to_transaction_locks_in http://dev.mysql.com/doc/internals/en/innodb-user-records.html "In the User Records part of a page, you'll find all the records that the user inserted. There are two ways to navigate through the user records, depending whether you want to think of their organization as an unordered or an ordered list. An unordered list is often called a "heap". If you make a pile of stones by saying "whichever one I happen to pick up next will go on top" — rather than organizing them according to size and colour — then you end up with a heap. Similarly, InnoDB does not want to insert new rows according to the B-tree's key order (that would involve expensive shifting of large amounts of data), so it inserts new rows right after the end of the existing rows (at the top of the Free Space part) or wherever there's space left by a deleted row. But by definition the records of a B-tree must be accessible in order by key value, so there is a record pointer in each record (the "next" field in the Extra Bytes) which points to the next record in key order. In other words, the records are a one-way linked list. So InnoDB can access rows in key order when searching." Hope this helps. Cheers!!! Akshay On Thu, Sep 4, 2014 at 9:48 AM, geetanjali mehra <mailtogeetanj...@gmail.com > wrote: > 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 >>>>> >> > >>>>> >> > >>>>> >> >>>>> > >>>>> > >>>>> >>>> >>>> >>> >> >
