How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed
We have identified the root cause: it's the innodb_autoinc_lock_mode = 1 .

Here is the summary from the official doc: 


0:  traditional lock mode, provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
1: consecutive lock mode: In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time
2: interleaved lock mode: In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.


In our case, when multiple API called INSERT statements, if one API call is prolonged, that INSERT statement would hold a TABLE lock on the destination table, which then causes the timeouts.

We switch it to innodb_autoinc_lock_mode = 2, restart the server and voila.  Now we can call multiple APIs to insert into the same table without having time-outs.