mysql optimization - mysql innodb table lock, beg the DBA expert for answers

Question

Question:
1. When transactions are not enabled in innodb and autocommit=1, will update and delete cause table locks?

I opened two mysql sessions in the terminal, and table t has a total of 3.5 million rows of data

Execute alone:

 会话1:update t set status=1 where id>1; 16.*秒
 会话2:update t set status=1 where id<3500000; 12.*秒

Execute simultaneously, my understanding:

**在我看来，这两条sql，同时执行，同时操作同一条记录的情况，只有一次**
**在innodb中update和delete都会隐式添加排它锁，那么就是说这两条sql同时执行，只会阻塞很短的时间，毕竟只有同时操作同一条记录的情况下，才会阻塞**

Executed simultaneously, test results:

两条sql,间隔执行时间，在半秒左右.
会话1先执行:update t set status=1 where id>1;16.*秒，没有变化 
会话2后执行:update t set status=1 where id<3500000; 28.* = 12.*秒+16.*秒


会话2先执行:update t set status=1 where id>1;12.*秒，没有变化 
会话1后执行:update t set status=1 where id<3500000; 28.* = 16.*秒+12.*秒

Could it be that when two SQLs operate on the same record at the same time, the table is locked? My understanding should be that it will only block for a short period of time. After all, the same record can only be operated at the same time once. It is like two people, a counting from 1 to 10, and b counting from 10 to 1, regardless of whether they are at the same speed or Not at the same speed, they can only count to the same number once at the same time

Answer 1

A sql is a transaction, which does not mean that operating 1W records means 1W transactions. sql1 locks all records > 1, and sql2 will wait for sql1 to release the lock