有这样一条语句
UPDATE table SET onsale=0 WHERE uid=116980
uid 是有索引的,假如 uid=116980 的数据一共有 1 万条,执行这条更新语句大约需要 20s ,想问下,更新期间,mysql 是会一次锁住这 1 万条数据,还是每次短时间锁一条啊?
如果他会一次锁住 1 万条的话,我要怎么避免这种长时间的锁定
1
rqxiao 2022-09-29 17:29:30 +08:00
一次锁住这 1 万条数据
|
2
nekolr 2022-09-29 17:31:21 +08:00
缩小锁定的范围,拆开更新?
|
3
lmshl 2022-09-29 17:53:55 +08:00 4
1. 锁 1 万毋庸置疑
2. UPDATE table SET onsale=0 WHERE pk IN (SELECT pk FROM table where uid=116980 AND onsale <> 0 LIMIT <batch-size>) 重复执行几次,直至 effect rows = 0 我经常这么干,如果你这条查询走索引或者数据量不大的话就无所谓,数据量大且没索引的时候可以考虑先取到程序里再分批更新。 |
4
7911364440 2022-09-29 18:06:29 +08:00
分批更新的过程中如果有其它查询数据的请求进来,可能会查到中间状态的数据,需要考虑下会不会对系统有影响
|
5
brader OP @lmshl 好的,谢谢前辈,感觉这个解决方案适合我,想问下你平时<batch-size>一般设置多大? 100 ? 1000 ?
|
6
cnoder 2022-09-29 18:13:41 +08:00
可以不 wherein ,直接 limit ,一样的,UPDATE table SET onsale=0 WHERE uid=116980 and onsale !=0 limit 100
|
7
brader OP @7911364440 是的,我就是查询到线上前面比较多数据的几个用户,每人有大概 100 万条数据要更新,我就是担心一次更新 100 万条,会把数据库搞死,想小批量更新
|
8
dongtingyue 2022-09-29 18:29:21 +08:00
innodb 是锁行否则是锁表。每次更新少点
|
9
fmumu 2022-09-30 10:39:34 +08:00
先查出来主键,用主键去更新
|
10
googol2chen 2022-09-30 11:00:25 +08:00
@cnoder wherein 是为了用主键索引,加快查询速度。
|
11
lmshl 2022-09-30 11:55:46 +08:00
@cnoder 谢谢,学到了,原来 mysql 还支持 update/delete 的时候加 limit 。pg 不支持这个,我也没想到这个
|
12
brader OP |
14
hoko1814 150 天前
学习了
|