Is it possible to use a native SQL query for atomic balance update without locking rows?

Yes, it is possible to perform an atomic balance update using a native SQL query without explicit row locking, by using an UPDATE statement with an arithmetic expression. In most DBMSs, such operations are atomic at the level of a single command.

Example for updating the balance:

UPDATE accounts
SET balance = balance + :amount
WHERE account_id = :id;

Here, :amount can be a positive or negative number. This operation guarantees that the balance update occurs atomically, without the need to manually lock rows.

It is important that the transaction is properly configured, and the isolation level allows avoiding issues with concurrent access (for example, using READ COMMITTED). If stricter control is required, SELECT ... FOR UPDATE can be used, but in simple cases, such a query is sufficient.

Thus, a native SQL query with an arithmetic update of a field is an effective way to atomically change the balance without explicit locks.