Sam
2018-07-19 17:12:20 UTC
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
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