mysql批量更新时遇到的坑
新上线与中台对接的项目,需要批量更新用户表,设置初始状态。
刚开始使用的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 ()
上一篇: C语言入门自学书籍推荐
下一篇: 讲解php如何从字符串中创建函数
推荐阅读
-
Mysql 5.7.19 免安装版遇到的坑(收藏)
-
spring cloud配置高可用eureka时遇到的一些坑
-
Win10环境下安装Mysql5.7.23问题及遇到的坑
-
mysql 记录不存在时插入 记录存在则更新的实现方法
-
Mysql数据库从5.6.28版本升到8.0.11版本部署项目时遇到的问题及解决方法
-
html标签从.net framework转移到.net standard(.net core 2.2)时遇到的坑及填坑
-
nodejs同步调用获取mysql数据时遇到的大坑
-
PHP如何批量更新MYSQL中的数据
-
MySQL并发更新数据时的处理方法
-
mysql 8.0.18 压缩包安装及忘记密码重置所遇到的坑