[odb-users] PostgreSQL C++ ORM (ODB): What's the scope of database lock in transactions? Can transactions fail?

[Sam]

   Hi guys,
   I'm new to database development, but been doing C++ for a long time.
   I'm using PostgreSQL with ODB. I understand that transactions
   introduce the concept of atomicity in database to modify multiple
   entries/tables and ensure consistency (even in read-modify-write
   operations over multiple tables). But what I don't understand is how a
   transaction can always succeed given these (apparently) impossible
   circumstances.  I'm writing a program that may use hundreds of threads
   to access the same database, and I'm wondering whether I have to
   assume the possibility of failed transactions.
   First, let's establish that ODB allows to do transactions from
   multiple threads, but only 1 transaction per thread. I can always
   imagine situations like this one:
   `F1()` in thread 1:
   1. Begin transcation
   2. Load row X
   3. Load row Y in another table
   4. Execute some C++ code on X and Y
   5. Modify rows X and Y and conclude the transaction
   `F2()` in thread 2:
   1. Begin transaction
   2. Load row Y
   3. Execute C++ code on row Y
   4. Load row X
   5. Execute C++ code on rows X and Y
   6. Modify both X and Y and conclude the transaction
   Assuming PostgreSQL doesn't do global locks (which [1]seems to be the
   case), what will happen if `F1()` is between 2 and 3, and `F2()` is
   between 2 and 3?
   The puzzle to me here is that ODB never knows that row Y is needed in
   `F1()` next, and doesn't know that X is needed by `F2()` next. So now,
   it can either load row Y for `F1()`, which will make `F2()` fail,
   because Y will be modified, or load X for `F2()`, which will make
   `F1()` fail. Let's also remember that ODB is just a library, so it
   cannot simply roll back `F1()` (although it can roll back the
   database), as it cannot control the flow of the program. Doesn't this
   assert that transactions can fail due to atomicity?
   If transactions can fail, what's the correct way to deal with this?
   Infinite loops trying again and again until a successful attempt
   passes? The ODB manual [2]never seems to be discussing a failure
   situation, except when the program fails
   Can I assume that a transaction can never fail because of concurrency,
   but can only block? What are the correct assumptions when dealing with
   transactions?

   Best regards,
   Sam

References

   1. https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/
   2. https://www.codesynthesis.com/products/odb/doc/manual.xhtml#3.5