Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Datebase with 10MB size . The Tables' engines were MyISAM (table-lock level). So I deiced to change them to InnoDB (row-lock level). I used the sql query for each table :

ALTER TABLE t ENGINE = INNODB;

After that the type column in PHPMyAdmin is showing InnoDB.

After this I found that my table is still using table-lock level because when I have two queries:

INSERT OR UPDATE (which should use row-lock) and after them a SELECT. All the select queries stop until the users finishes their INSERT or UPDATE.

IMPORTANT NOTES:

  1. I did not restart MySQL service after changing the engines .
  2. MySQl version 5.1.72-cll
  3. The default engine of my database is still MyISAM

what I want to Know is : Should I change any system variables in MySQL when I move to InnoDB?

share|improve this question
    
InnoDB is a transactional engine. When using InnoDB without any special options, it runs in auto commit mode, meaning that every query is isolated in the transaction of its own. To translate this to our mortal language, that means until the insert/update is done - you can't select (which for insert makes sense). However, this is expected functionality, which leads to conclusion that you're having an XY problem here. –  N.B. Jan 3 at 10:52
    
if u r talking a fact why InnoDB uses (row-lock level) If the whole tables will be locked when we use it? and how we can solve this problem? is changing the value of the auto commit is the solution? –  Basel Jan 3 at 10:57
    
How did you determine the whole table is locked? –  N.B. Jan 3 at 11:02
1  
@N.B.: that is simply not true. You can very well select from a table into which a different transaction inserts rows. That is the whole point of using InnoDB (and it's MVCC implementation) over MyISAM –  a_horse_with_no_name Jan 3 at 11:44
    
@a_horse_with_no_name - so you can perfectly fine select a record that hasn't yet been inserted? Makes perfect sense, care to show how it's done? I wasn't able to select something that doesn't exist yet. –  N.B. Jan 3 at 12:31
show 2 more comments

migrated from stackoverflow.com Jan 3 at 11:28

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.