Thanks to all, Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist
On Thu, Sep 4, 2014 at 2:36 PM, Akshay Suryavanshi < akshay.suryavansh...@gmail.com> wrote: > 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 >>>>>> >> > >>>>>> >> > >>>>>> >> >>>>>> > >>>>>> > >>>>>> >>>>> >>>>> >>>> >>> >> >
