Yes, each query is transaction within itself, so within single query it is impossible to have race condition (assuming that there are no bugs in DB implementation). I would say that DB is probably better optimised for single UPDATE queries than locking mechanisms as second one can impose some additional restrictions as DB do not know what you want to do with that lock.
