0

I have a table with InnoDB engine (comments) in MySQL database and I have the following scenario:

There are two users trying to access the same comments table at the same time as following:

user1:

INSERT INTO comments (comment) VALUES ('HELLO WORLD');

user2:

SELECT * FROM comments;

I want the following points to be clarified , please:

  1. The user2 can not read (SELECT) from the table , it should wait for some time (I think until the insert of the user1 is finihshed). Is this waiting called row-level lock which used by InnoDB?
  2. If the answer of the previous question was YES so what is the job of MVCC? in InnoDB engine. I read that MVCC means to let the users read the table rows (repeatable-read) even if there is an update or insert situation at the same time (because the user here read an old version of the row even if it is being updated at the mean time).

Notes:

  • I want to mention that I use the above code in the following form but it still making the same problem (waiting problem):

user1:

SET AUTOCOMMIT=0;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO comments (comment) VALUES ('HELLO WORLD');
COMMIT;

user2:

SELECT * FROM comments;
  • I even try to change the isolation level from repeatable-read to read uncommited -the dirty read is not so important in my case- (I thought it will solve the waiting problem but it did not. the waiting is still stand).

user1:

SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO comments (comment) VALUES ('HELLO WORLD');
COMMIT;

user2:

SELECT * FROM comments;

The big question is How could my users read while other users inserting or updating rows?

Basel
  • 359
  • 3
  • 16
  • no. there is no reason for innodb to lock the table for reading if you insert another row. Just open 2 connections in 2 different terminals and run your scenario – zerkms Nov 25 '13 at 07:43
  • I tried it and it did not work for that I ask the question – Basel Nov 25 '13 at 08:03
  • What "did not work" means? – zerkms Nov 25 '13 at 08:11
  • I mention in my question that any update or insert operation will stop any select until it finishes. And for that I am trying to figure out why?taking in consideration the steps and searches in my question – Basel Nov 25 '13 at 08:26

1 Answers1

0

You are selecting everything from the table, so a row-level-lock doesn't help you at all as you are asking for all rows. Add a "where id = 5" or something similar and everything is just fine.

Here you can find some explanations for the different locking mechanisms: http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

Argeman
  • 1,345
  • 8
  • 22