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;
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?
大家讲道理2017-05-24 11:36:38
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
在执行时会加写锁,有时就是锁全表,所以会影响并发性能,但这只是一瞬间的事,所以并发不高的情况下往往看不出来;
一次UPDATE
UPDATE
(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;
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.
MySQLUPDATE
和DELETE
都会加写锁,而且锁全表;SELECT
会加读锁,所以多个SELECT
可以并发,但不能和UPDATE
、DELETE
并发;INSERT
的加锁有点特殊,锁的强度可能介于读锁和写锁之间,与SELECT
、INSERT
UPDATE
and DELETE
will add write locks and lock the entire table;
SELECT
will add read locks, so multiple < code>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