How postgres or Mysql implement transactions? Is it done by holding on a connection?

Question

From what I read and understood in https://mysqlserverteam.com/mysql-connection-handling-and-scaling/

Each connection have a THD object and what ever we update or insert using that is stored in THD and gets committed to the DB.

In case of transaction, especially programmatic transactions, My understanding is, we hold on the connection till we are done with our operations and then commit. meanwhile we can do both DB related updates OR inserts and other calculations that may result in some exception/errors. example :

Begin;
IN-Transaction : 
    UPDATE Table-1
    c = A/B
    Insert Table-2 with c
    ... so on
Commit;

in this programmatic transactions, we have to pass a reference of transaction for doing multiple DB operations, and that reference is not a transaction object, rather a connection and there is one-to-one mapping between connection and a transaction context. If we encounter an exception, we do a rollback and DB understand that it needs to discard THD related changes.

My questions are :

1. Is my understanding correct, If somethings missing, please help me with that!!.

2. If this is correct, is it the same strategy used in any other DB like postgres Or maria Or voltDB ??

3. If it is incorrect, how is it implemented in mysql, postgres, voltDB etc..? Can you point me to relevant links where I can get better idea!!.

thanks.

Answer 1

1. In general yes: a transaction runs in a single database connection or database session: it may start in a implicit way or explicit way: it's started and ended with special SQL statements. For PostgreSQL see https://www.postgresql.org/docs/9.5/tutorial-transactions.html.

However there are rare exceptions like distributed transactions using two-phase commit protocol where a single transaction runs on different sessions on different databases.

2. most databases using SQL work this way because it is requested by SQL standard.

You must also take into account that some client tools (for example psql for PostgreSQL) or JDBC API for Java have defaults settings that change a little bit this behaviour: both implement auto-commit mode after each statement (this means that each SQL statement runs in its own transaction) but you can change this behaviour in each tool.

3. the connection/session/thread/process depends a lot on the database. MySQL uses threads whereas PostgreSQL uses processes: there is no SQL statement for that: this is very implementation dependant.