数据库报错 ‘ERROR 1290’

最近在观察生产环境的运行日志时发现，日志中出现了一些 Error 1290: The MySQL server is running with the --read-only option so it cannot execute this statement 错误。询问了系统运维，得到的答复是数据库没有 down，也没有数据库升级或者迁移的动作。

遂询问 MYSQL 生产环境所用的架构以及服务商，得到以下答复：

  • 服务商：阿里云云数据库RDS
  • 架构：读写分离

问题排查

通过日志信息，找到出错的 SQL 语句，发现均为 SELECT 查询语句，且使用 FOR UPDATE 加了行级锁。

推测为加锁失败导致的报错。

查阅阿里云 RDS 文档，得到以下关键信息：读写分离架构的数据库，可单独设置每个实例的读写权重。

再次询问系统运维，得知项目所用数据库的读写权重为主实例只写，其他从实例均摊读请求。

推测错误原因为：读写分离架构，数据库代理将所有的 SELECT 请求发送到了从实例，但是部分 SELECT 请求使用了 FOR UPDATE，需要写数据库，由于从实例为只读，该写入被数据库拒绝了，返回了 ERROR 1290 错误。

实操，分别直连数据库主实例以及数据库代理，进行 SELECT FOR UPDATE 查询，直连方式能成功，经过数据库代理的返回了错误，符合预期。

解决方案

方案一：不使用数据库代理，直连主实例

该方法简单粗暴，但所有请求只能由主实例处理，加大了数据库服务器的负载，所以不推荐该方法。

方案二：在查询 SQL 中添加注解

RDS 提供了通过 Hint 指定 SQL 发往主实例、只读实例的方法：

RDS文档Hint截图

该方法比较麻烦一点，需要在所有应用了 FOR UPDATE 的 SQL 查询中添加 /*FORCE_MASTER*/ 注解，但该方式不需要改变数据库架构，且稳定性较高，较推荐。

小结

其实这样的问题是不应该在生产环境中暴露的。由于测试环境的数据库是单节点（节省成本），所以测试环境并为出现该问题。所以最好是有一个跟生产环境一致的预发布环境来验证功能。

另外就是做应用开发，对项目部署的各种依赖的架构也需要了解一下。避免遇到问题了摸不着头脑。

UPDATE 使用 CASE WHEN…THEN…ELSE…END 语法锁表问题

起因是有个项目有需求，涉及到全表新增字段并写入数据，该数据需要取数据行计算后得出。所以写了个脚本，分页批量拉取数据并使用标题所述语法进行数据更新，这时候问题来了。

问题一：分页的数据量太大时数据库会报无法持有锁问题

问题二：使用多线程方式并发更新时效率并没有提升

问题排查

首先根据数据库日志，排查到数据库报错为等待锁时间超时。查看了数据库的锁等待时间为 50s，正常执行命令不应该 50s 还获取不到锁，所以肯定是数据库 SQL 有问题。

数据库 SQL 为：

1
2
3
4
5
UPDATE TABLE SET COLA = CASE 
WHEN id=? THEN v1
WHEN id=? THEN v2
ELSE COLA
END

推测该语法加锁时进行了全表加锁，所以在其他进程使用表的时候无法持有锁。

验证一下确实是存在表级锁，该语法没有添加 WHERE 子句，数据库会对所有数据加锁。所以小小优化一下，增加一下 WHERE 子句，在 id 有主键索引的情况下，效率直接快了几十倍。

改进后的 SQL：

1
2
3
4
5
UPDATE TABLE SET COLA = CASE 
WHEN id=id1 THEN v1
WHEN id=id2 THEN v2
ELSE COLA
END WHERE id in (id1,id2)

小结

这是个很基础的错误，UPDATE 一般都是需要跟 WHERE 子句的，否则必然会波及到全表数据，犯这个错的原因是 CASE WHEN … 这个语法用的比较少，错误的认为有了 WHEN 子句，只会对该子句中涉及的行加锁，想想其实也能知道不可能，所有 WHEN 子句都能包含一个表达式，当 WHEN 子句无穷无尽时，需要计算出所有符合 WHEN 子句的记录并集。。

使用不熟悉的语法还是要多测试的。