mysql中update语句的锁
UPDATE rent_contacts SET contacts_mobile='11111' WHERE rent_unit_code in (SELECT rent_unit_code FROM rent_unit );
第一种情况: 条件无索引
sql一执行:
BEGIN;
UPDATE contacts SET mobile='11111' WHERE code in ('3424342' );
sql二执行 UPDATE contacts SET mobile='11111' WHERE code in ('1111' );
sql二执行受阻,sql一commit之后,sql二可执行成功
结论:条件无索引,锁表
第二种情况: 条件有索引,in语句是确定的值
sql一执行:
BEGIN;
UPDATE contacts SET mobile='11111' WHERE code in ('3424342' );
sql二执行 UPDATE contacts SET mobile='11111' WHERE code in ('1111' );
sql二正常执行,sql二不依赖sql一的commit
结论:条件有索引,锁行
第三种情况: 条件有索引,in语句是复杂查询
sql一执行:
BEGIN;
UPDATE contacts SET mobile='11111' WHERE code in ( select code from user where id=1);
sql二执行 UPDATE contacts SET mobile='11111' WHERE code in ('1111' );
sql二执行受阻,sql一commit,sql二正常执行
结论:条件有索引,in语句是不确定的值,锁表