Does MySQL select lock the table?

Does MySQL select lock the table?

Some people say that MySQL's Select will lock the table, some people say that the MySQL query will not lock the table.
They are all right, no, but very one-sided.

In fact, whether the MySQL select will lock the table, this depends entirely on what the table is using the storage engine.

Here I take the most familiar storage engine InnoDB and MyISAM to illustrate this problem.

Table Select for MyISAM locks the table, causing other operations to hang and wait.
Table Select for InnoDB is not locked. In fact, the snapshot is used here. Snapshots are not discussed here.

Here is my corroboration:

Answer one:
Select Sql_no_cache * from tmp002
Table tmp002 has 5618288 data storage engine when InnoDB, all query out approximately about 5min

Answer two:
Update tmp002
Set num=6
where Mreasonid in (' 700098 ', ' 301001 ') limit 10000

We first execute the following statement. Re-execute two.

We're going to find out. 2 was executed soon.
Update tmp002
Set num=6
where Mreasonid in (' 700098 ', ' 301001 ') limit 10000

Rows affected: 0
Time: 0.120s

In reply to three kinds of execution under show full processlist.
No locking phenomenon was found.

To modify the table storage Engine:
ALTER TABLE tmp002 Engine=myisam
Rows affected: 5618288
Time: 17.510s

The same is the example above

Answer one:
Select Sql_no_cache * from tmp002
Table tmp002 has 5618288 data storage engine when InnoDB, all query out approximately about 5min

Answer two:
Update tmp002
Set num=6
where Mreasonid in (' 700098 ', ' 301001 ') limit 10000

We first execute the following statement. Re-execute two.

We'll get back to you. Always in the execution state without returning any information.

In reply to three kinds of execution under show full processlist.
13564441 root 192.168.53.41:53312 tempDB Query 2 Locked update tmp002 set num=6 where Mreasonid in (' 700098 ', ' 301001 ') l Imit 10000
13564671 Root 192.168.53.41:53736 TempDB Query 4 Writing to net Select Sql_no_cache * from tmp002

The 13564441 status is Locked, waiting for the release of the lock.

In summary, the following results can be obtained.
Table Select for MyISAM locks the table, causing other operations to hang and wait.
Table Select for InnoDB is not locked. In fact, the snapshot is used here. Snapshots are not discussed here.

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1411235