Search code examples
mysqldatabasepostgresqlasynchronousconcurrency

MySQL / PostgreSQL - Is this possible to perform parallel write in a transaction?

If I have to update two differents rows at the same time, but one must not be updated if the update of the other fail, my current thinking is to use transactions.

But are transactions able to perform paralleles operations ?

In the case where I dont need the result of a query to perform the next(s) one(s), is there a way to run the queries at the same time ?

This is a theorical question so:

  • The rows dont have to be on the same table
  • The operations could be update, insert or delete (on a same table, insert are processed sequencialy, but in many different tables ?)
  • The solution don't have to be in SQL (maybe there is a way to get a kind of "barrier-transaction" from the client, and then run asyncronously the queries ?)

I learned about locks and concurency issues in relational database by looking for an answer at my question but didn't found what I was looking for...

Solution

  • Yes, this is possible; it is called a distributed transaction. It is implemented with two-phase commit in both MySQL and PostgreSQL (and every RDBMS I ever heard of).

    The idea is that you start transactions on two concurrent database sessions. When you have to rollback in one transaction, you also rollback in the other one.

    Once you are done with your work, you prepare both transactions. This is a special process that does all the work short of the actual commit, so that a subsequent commit is guaranteed to work. Moreover, such a prepared transaction must be persisted so that it can survive a crash.

    If the prepare step fails on a database, you roll back both transactions.

    Once all transactions have been prepared successfully, you commit them. That should never fail, since all the “hard work” was already done during preparation.

    You need a component that coordinates that work. That component is called a transaction manager and must persist the state of each transaction so that it can continue processing even if work is interrupted by a crash or something else. This component makes sure that all transactions are either committed or rolled back.

    The SQL statements for that are different in PostgreSQL and MySQL:

              |         PostgreSQL         |       MySQL
----------+----------------------------+--------------------
 start    | BEGIN                      | XA START
 prepare  | PREPARE TRANSACTION <name> | XA PREPARE <name>
 commit   | COMMIT PREPARED <name>     | XA COMMIT <name>
 rollback | ROLLBACK PREPARED <name>   | XA ROLLBACK <name>

    See the documentation for PostgreSQL and MySQL.