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
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
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
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 |
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
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.
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
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
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.
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,
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
11 matches
Mail list logo
