mysql optimization - mysql innodb and myisam lock

Question

innodb

---

Will insert be locked? If it's locked, what's the point?
Update and delete in innodb will implicitly add exclusive lock,
update table set... where id in (1,2,3,4); What is the exclusive lock? Add these four records directly, or lock, modify and commit in sequence according to the ID;

myisam

---

Will delete and update implicitly add write locks?
Will select implicitly add a read lock?
If it is added in two situations, both are table lock levels, then the concurrency is particularly bad, right?

Choice of two engines
MyISAM: If you execute a large number of SELECTs, MyISAM is a better choice. Why is this? I actually tested 3.6 million pieces of data, all of which used index selection. Innodb is much more efficient.

InnoDB: If your data performs a large number of INSERT or UPDATE, you should use an InnoDB table. Is this because of the myisam table lock?

Answer 1

Thanks for the invitation.

InnoDB
InnoDB may only lock the table header for INSERT, but it will not lock the entire table; INSERT可能只锁表头吧，总之不会锁全表的；
UPDATE(如果没有FOR UPDATE或LOCK IN SHARE MODE)和DELETE在执行时会加写锁，有时就是锁全表，所以会影响并发性能，但这只是一瞬间的事，所以并发不高的情况下往往看不出来；
一次UPDATEUPDATE(if there is no FOR UPDATE or LOCK IN SHARE MODE) and DELETE will add write locks during execution, sometimes locking the entire table, so it will affect concurrency performance, but this is only a momentary matter. , so it is often not visible when concurrency is not high;

Multiple rows of UPDATE at one time must be locked together and released together after submission, because MySQL must ensure the atomicity of this statement. When one has a primary key conflict, the others cannot be submitted.

MySQL
UPDATE和DELETE都会加写锁，而且锁全表；
SELECT会加读锁，所以多个SELECT可以并发，但不能和UPDATE、DELETE并发；
INSERT的加锁有点特殊，锁的强度可能介于读锁和写锁之间，与SELECT、INSERTUPDATE and DELETE will add write locks and lock the entire table;

SELECT will add read locks, so multiple SELECT can be concurrent, but it cannot be concurrent with UPDATE and DELETE;

INSERT's locking is a bit special, and the strength of the lock It may be between a read lock and a write lock, and can be concurrent with SELECT and INSERT. SELECT而言性能不会比InnoDB好很多，这还取决于行的存储方式，比如MyISAM的FIXED可能会比DYNAMIC
Finally, MyISAM is faster. In addition, your example will definitely be more beneficial to InnoDB: if it is changed to a non-primary key index, then InnoDB may not be so fast; if only the id column is SELECTed, then MyISAM may not be so slow. You can search for the reason

clustered index🎜. 🎜