mysql - InnoDB Concurrent Read-Write -

i wanted understand how innodb handles multiple simultaneous reads/write operations. consider below scenario: performing pretty intense batch write. read request come in have wait till batch write gets completed. during time, write request requested on table. time, batch write complete, there multiple read requests , write requests pending. in order innodb resolve requests. in cases, prefer getting recent result-set table. waiting write requests way, lead read-request starvation. writes requests non-updates row requests. update requests believe acquire row-level lock, whereas insert requests require table-level lock.

can please explain how take place in innodb? thanks

in innodb, inserts not take table lock. (myisam different story.)

roughly speaking, each read or write innodb table lock row(s) needed. if there no overlap between rows of 1 query , another, there no waiting. issues make "roughly":

• some locks "gap" locks. think of inserting new row between 2 existing rows.
• shared read locks (on same row) not block each other
• exclusive locks act described.

"mvcc" allows multiple transactions "see" different versions of same row. let's 1 transaction selecting 1 row while updateing it. until both transactions finished there physically 2 versions of row. things cleaned after both transactions commited or rollbacked.

it may worth nothing of row-level locking , checking costly. if have dozens of connections pounding away, slow down. in older versions, 4-8 connections practical limit. 5.7 64 can handled. still, innodb can handle thousands of transactions per second, limited slow disk i/o.

"batch inserts" -- if mean insert single table, optimal create single insert statement(s) contain 100-1000 rows. decreases overhead of communication, parsing, optimizing, , transaction overhead. increases risk of colliding reads, speedup (usually) outweighs collision delay.

be sure check errors after every innodb statement. deadlocks can occur. generally, handled rollback plus rerunning begin...commit.