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.

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

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 '14 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?
–
BaselJan 3 '14 at 10:57

How did you determine the whole table is locked?
–
N.B.Jan 3 '14 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_nameJan 3 '14 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 '14 at 12:31