Error in the documentation for transactions?

[tobyxdd]

Version

1.26.0

What happened?

https://docs.sqlc.dev/en/latest/howto/transactions.html

In the given example, the code tries to select a row from records and increase the counter by 1 (in a transaction). According to the official MySQL manual:

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety - https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-reads.html

So, shouldn't

-- name: GetRecord :one
SELECT * FROM records
WHERE id = $1;

be

-- name: GetRecord :one
SELECT * FROM records
WHERE id = $1
FOR UPDATE;

in this case?

[hungtcs]

I don't think sqlc can handle locking very well because each query is independent, but locks and transactions are highly correlated.

I'm using a postgres database, and my current solution is to handle all operations that require locking as a function (or stored procedure), and then sqlc only makes the calls.

But although this simplifies the work of using sqlc, I need to write a large number of functions in the database.

If you have a better solution, please let me know.