session 1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set name=(select name from bai where a.id=bai.id) where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
session 2:
mysql> use test;
Database changed
mysql> update bai set name='ee' where id=10000;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
默认的隔离级别是rr,如果session1 单独select是不会阻塞2的,select的会使用快照,update使用当前读,所以在子查询中的select为了防止幻读,就直接不让表有dml了。