InnoDB engine seems lock the table automatically in Mysql5.7 I have a table named Students like:

mysql&gt; select * from students;
+----+-------+---------+
| id | name  | classId |
+----+-------+---------+
| 1  | Kris  |       7 |
| 2  | Tom   |       7 |
| 3  | Mary  |       2 |
| 4  | Leon  |       2 |
| 5  | Mario |       3 |
+----+-------+---------+
5 rows in set (0.00 sec)


I execute the following sql statement, but don't give the commit statement:

set autocommit = 1; start transaction; update students set classId = 1 where id = 1; 


After that I will open another session and execute the following sql statement: 

set autocommit = 1; start transaction; update students set classId = 1 where id = 2;  


Now, the whole shell screen will block and not respond. After a while it will give me an error, similar to this one:

1205 Lock wait timeout exceeded; try restarting transaction


So, I am confused as to why does the InnoDB engine give me a row-level lock on the table? In this case, why does MySQL block my updates to another row?
I apologize for the other answer you were given that just copied and pasted from the manual without being related to your question.

This is a common misconception- Innodb does not do full table locks to ensure isolation between transactions- however, it does apply row and gap locking by default to assure no phantom reads happen. Because your table is unindexed the only (inefficient) way to do gap locking is to setup individual lock in every row and every gap between ( for example if you try to insert a new row with id 0, it should get blocked too.

You have two options: create an index on id (probably a primary key is the right way) so only a single row is actually locked thanks to the new index, or change your default isolation level to read committed (SET SESSION transaction_isolation='READ-COMMITTED';).

This is a common question here on dba.stackexchange so here you have a more detailed explanation I wrote on another similar question: MariaDB - &quot;ERROR 1205 ... Lock wait timeout exceeded; ...&quot; when doing multiple parallel UPDATEs affecting different rows

However, in your case I strongly recommend you to go the route of creating the index.