MySQL中如何书写update避免锁表

1. 什么是MySQL锁表？

MySQL锁表是指在对某个数据表进行读写操作时，为了保证数据的一致性和完整性，系统会对该数据表进行锁定，防止其他用户对该表进行操作。

2. 为什么会出现锁表？

当多个用户同时对同一个数据表进行读写操作时，由于MySQL采用的是行级锁定机制，为了保证数据的一致性和完整性，系统会将该数据表锁定，直到当前用户完成操作后才会解锁。

3. mysql中UPDATE语句避免表锁定，主要考虑以下几点：

1.使用合适的索引

确保表上存在适当的索引，有助于提高UPDATE查询的性能并减少锁定时间。通过为 WHERE 条件列创建索引，MySQL 可以更快地定位要更新的行，而不需要锁定整个表。

场景一：执行update语句更新指定条件的数据，where条件列是主键索引

UPDATE vehicle_sim_binding_15 SET vehicle_model_code="",vehicle_model_id=NULL  where id=3;

场景二：执行update语句更新指定条件的数据，where条件列没有主键索引也没有设置其他索引

2.1 给where条件列设置索引

2.2 使用select concat update语句，拼接update语句，导出执行语句后，执行update

SELECTconcat( 'UPDATE vehicle_sim_binding_15 SET vehicle_model_code="",vehicle_model_id=NULL  where id=', id,';') 
FROMvehicle_sim_binding_15 
WHEREproject_id IN ( 53,171 ) AND vehicle_model_code = 'CUSC001' AND bind_status = 0;

导出where条件列包含主键id的更新语句，执行上图生成的update语句。

总结：为什么推荐 MySQL 的update 语句中 where 条件要有主键？

where 不加主键还不能更新了？不是的，能更新，也能使用。但是我不建议这样做。因为我们大多数人使用 MySQL 都使用的是 innodb 存储引擎，它是支持事务的。如果你的 where 条件不加主键，那么 innodb 的行级锁就可能变成表级锁。如果升级为表级锁，那么并发性就将大打折扣了。InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁，而加锁不当势必会影响并发。

只需要记住主键和唯一索引是行锁，其他索引并不一定是行锁，很可能是表锁。这样，死锁的概率就非常的高，并发也就随之下降。

2.批量更新

如果需要更新大量的行，可以考虑分批次更新，而不是一次性更新所有行。通过将大的更新任务分割成多个较小的更新操作，可以减少锁定的时间和冲突的可能性。

3.选择适当的事务隔离级别

根据应用需求选择合适的事务隔离级别。在某些情况下，将事务隔离级别设置为 READ COMMITTED 或 REPEATABLE READ 可以减少锁定的范围，从而提高并发性能。