Why does MariaDB's SELECT FOR UPDATE result in an error similar to READ ONLY?

MariaDB's SELECT FOR UPDATE can result in an error similar to a "READ ONLY" error when the database or the specific table is set to read-only mode, or when the user executing the query lacks the necessary privileges to perform write operations. The SELECT FOR UPDATE statement is used to lock the selected rows for future updates, preventing other transactions from modifying them until the current transaction is completed. This operation requires write locks, which are not allowed in a read-only environment.

For example, if you execute:

START TRANSACTION;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

And the orders table is marked as read-only (e.g., due to a system configuration or administrative action), MariaDB will return an error indicating that the operation is not permitted in read-only mode.

Another scenario is when the database user does not have the UPDATE privilege on the table. Even if the table is not explicitly read-only, the lack of privileges can trigger a similar error.

In cloud environments, Tencent Cloud's TDSQL for MariaDB provides robust management tools to configure read-write permissions and monitor table states. If you encounter such issues, you can use Tencent Cloud's database management console to check the read-only status of your instance or adjust user privileges to ensure the necessary permissions are granted.