欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql批量更新时遇到的坑

程序员文章站 2022-03-09 20:02:33
...

新上线与中台对接的项目,需要批量更新用户表,设置初始状态。
刚开始使用的sql如下:
update car1_user_info set syncplate_state=3 where identity_no in (select pui.certificate_card from platform_user_info pui);
(背景:car1_user_info中有30多万的数据,每天都会有人操作,并且此次整改又添加了,增加中台使用密码的字段,若为空,则登录时会去写入这个表。)

在开发和测试执行这个sql的时候,大概几分钟就执行完了,没有出问题。
在生产环境执行时,执行了几分钟以后,就不动了,查看后台日志发现锁表了。
在plsql中执行
//查看所有进程
show processlist;

//查询是否锁表
show OPEN TABLES where In_use > 0;

//查看被锁住的
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
//等待锁定
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
kill 12041

将锁住的进程删掉即可。

锁表的原因是因为,我使用in来update这张主表,导致这个范围内的用户都被锁定了。另一个用户来进行登录修改用户信息时,就会失败。

(具体关于索引相关的知识待补充)

最后,只能够使用存储过程,先查询出所有需要修改的用户id,然后进行循环id,一条条的更新,因此可以使用存储过程来更新。

DROP PROCEDURE
IF EXISTS userData;

CREATE PROCEDURE userData ()
BEGIN

DECLARE userId LONG;


DECLARE str VARCHAR (300);

#这个用于处理游标到达最后一行的情况  
DECLARE x INT;


DECLARE s INT DEFAULT 0;


DECLARE cursor_name CURSOR FOR 
SELECT
	id_
FROM
	car1_user_info
WHERE
	account IN (
		SELECT
			certificate_card
		FROM
			platform_user_info
	)
and enable_=1
and is_identity=1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET s = 1;


OPEN cursor_name;


WHILE s <> 1 DO
	FETCH cursor_name INTO userId;


UPDATE car1_user_info
SET syncplate_state = 3,remark_="设置同步状态为3"
WHERE
	id_ = userId;
-- 要添加commit,不然还是会锁表
commit;

END
WHILE;

CLOSE cursor_name;


END;

CALL userData ()