Re: next-key lock

[Akshay Suryavanshi]

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 
 

Re: next-key lock

[geetanjali mehra]

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 

Re: next-key lock

[geetanjali mehra]

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)









mysqlbegin;

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, 

Re: next-key lock

[Akshay Suryavanshi]

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)









 mysqlbegin;

 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 

Re: next-key lock

[geetanjali mehra]

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)









 mysqlbegin;

 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 

Re: next-key lock

[geetanjali mehra]

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
 
 

Re: next-key lock

[Nilnandan Joshi]

Hi Geetanjali,

It seems, you are confused with gap locking. Can you tell if there is a
record with value 12 and 17 in the new table?
Select count(*) from new where c1 = 17;

AFAIK, if you run this query Select * from new where c1 between 12 and 17
for update; and if you don't have value 17 in the table but the next value
is 30 then innodb will lock the records between 12 to 30 and it will not
let you insert records with value 20, 25 etc. This is called gap locking so
if there is no such record, innodb locks the gap between last record and
next record (if it exists)

Kindly check with table, if you have both the values.

regards,
Nilnandan

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
  
  
 
 
 

Re: next-key lock

[Akshay Suryavanshi]

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:

Re: next-key lock

[Akshay Suryavanshi]

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
 
 

Re: next-key lock

[geetanjali mehra]

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

Re: next-key lock

[shawn l.green]

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