It encourages you to write validations for things that don't need to hit the DB, but actually uses the DB for handling constraints, since it's the real source of truth for data consistency.
Most CRUD applications go the one extra step of putting a uniqueness constraint on the column instead of just relying on the app layer validation, and comparing hidden time stamps on the form if multiple people might edit the same row simultaneously. It’s exceedingly straightforward.
Did you really think thousands of web apps up to and including GitHub and shopify were squeaking by on blind luck? If basic database transactions and integrity weren’t doable in Rails, that’d have had to have been fixed years ago.
Most of your basic questions are covered by the starter Rails Guide for ActiveRecord, eg) transactions, validations, how to add a uniqueness constraint to the DB in a migration
https://guides.rubyonrails.org/active_record_basics.html
The ActiveRecord Queries guide also covers some mechanisms for handling row-level write contention
https://guides.rubyonrails.org/active_record_querying.html#l...
There are a couple of footguns in Rails involving transactions - by default, validating uniqueness of a column in Rails will be done at the app layer which is prone to race conditions, and with a typical CRUD model for an application, there's nothing in particular preventing overwrites of model data if say, a field was present on a form and two people were editing it, even if the "later" editor made no changes to that particular field.
When multiple systems and things other than databases (or multiple databases) are involved, you're generally on your own as well.