From a2764b8ea51156f14a383c5e93cf05c882b276d5 Mon Sep 17 00:00:00 2001 From: Daniele Varrazzo Date: Fri, 15 Oct 2021 12:47:43 +0200 Subject: [PATCH] Improve transactions documentation close #110 --- docs/api/connections.rst | 2 +- docs/basic/from_pg2.rst | 2 +- docs/basic/transactions.rst | 214 ++++++++++++++++++++++++++++++------ 3 files changed, 184 insertions(+), 34 deletions(-) diff --git a/docs/api/connections.rst b/docs/api/connections.rst index 705b56302..e5dee3510 100644 --- a/docs/api/connections.rst +++ b/docs/api/connections.rst @@ -174,7 +174,7 @@ The `!Connection` class ... The latter is useful if you need to interact with the - `Transaction` object. See :ref:`transaction-block` for details. + `Transaction` object. See :ref:`transaction-context` for details. Inside a transaction block it will not be possible to call `commit()` or `rollback()`. diff --git a/docs/basic/from_pg2.rst b/docs/basic/from_pg2.rst index f0ae5c19b..819e4d6b2 100644 --- a/docs/basic/from_pg2.rst +++ b/docs/basic/from_pg2.rst @@ -172,7 +172,7 @@ In order to manage transactions as blocks you can use the `Connection.transaction()` method, which allows for finer control, for instance to use nested transactions. -.. seealso:: See :ref:`transaction-block` for details. +.. seealso:: See :ref:`transaction-context` for details. .. _diff-callproc: diff --git a/docs/basic/transactions.rst b/docs/basic/transactions.rst index a7563f7c7..b9f91d3a7 100644 --- a/docs/basic/transactions.rst +++ b/docs/basic/transactions.rst @@ -19,6 +19,84 @@ connection will start a new transaction. If a database operation fails, the server will refuse further commands, until a `~rollback()` is called. +If the cursor is closed with a transaction open, no COMMIT command is sent to +the server, which will then discard the connection. Certain middleware (such +as pgbouncer) will also discard a connection left in transaction state, so, if +possible you will want to commit or rollback a connection before finishing +working with it. + +An example of what will happen, the first time you will use Psycopg (and to be +disappointed by it), is likely: + +.. code:: python + + conn = psycopg.connect() + + # Creating a cursor doesn't start a transaction or affect the connection + # in any way. + cur = con.cursor() + + cur.execute("SELECT count(*) FROM my_table") + # This function call executes: + # - BEGIN + # - SELECT count(*) FROM my_table + # So now a transaction has started. + + # If your program spends a long time in this state, the server will keep + # a connection "idle in transaction", which is likely something undesired + + cur.execute("INSERT INTO data VALUES (%s)", ("Hello",)) + # This statement is executed inside the transaction + + conn.close() + # No COMMIT was sent: the INSERT was discarded. + +There are a few things going wrong here, let's see how they can be improved. + +One obvious problem after the run above is that, firing up :program:`psql`, +you will see no new record in the table ``data``. One way to fix the problem +is to call `!conn.commit()` before closing the connection. Thankfully, if you +use the :ref:`connection context `, Psycopg will commit the +connection at the end of the block (or roll it back if the block is exited +with an exception): + +The code modified using a connection context will result in the following +sequence of database statements: + +.. code:: python + + with psycopg.connect() as conn: + + cur = con.cursor() + + cur.execute("SELECT count(*) FROM my_table") + # This function call executes: + # - BEGIN + # - SELECT count(*) FROM my_table + # So now a transaction has started. + + cur.execute("INSERT INTO data VALUES (%s)", ("Hello",)) + # This statement is executed inside the transaction + + # No exception the end of the block: + # COMMIT is executed. + +This way we don't have to remember to call neither `!close()` nor `!commit()` +and the database operation have actually a persistent effect. The code might +still do something you don't expect: keep a transaction from the first +operation to the connection closure. You can have a finer control on the +transactions using an :ref:`autocommit transaction ` and/or +:ref:`transaction contexts `. + +.. warning:: + + By default even a simple :sql:`SELECT` will start a transaction: in + long-running programs, if no further action is taken, the session will + remain *idle in transaction*, an undesirable condition for several + reasons (locks are held by the session, tables bloat...). For long lived + scripts, either make sure to terminate a transaction as soon as possible or + use an `~Connection.autocommit` connection. + .. hint:: If a database operation fails with an error message such as @@ -41,59 +119,131 @@ inside a transaction, such as :sql:`CREATE DATABASE`, :sql:`VACUUM`, .. __: https://www.postgresql.org/docs/current/xproc.html -.. warning:: +With an autocommit transaction, the above sequence of operation results in: - By default even a simple :sql:`SELECT` will start a transaction: in - long-running programs, if no further action is taken, the session will - remain *idle in transaction*, an undesirable condition for several - reasons (locks are held by the session, tables bloat...). For long lived - scripts, either make sure to terminate a transaction as soon as possible or - use an `~Connection.autocommit` connection. +.. code:: python + + with psycopg.connect(autocommit=True) as conn: + + cur = con.cursor() + cur.execute("SELECT count(*) FROM my_table") + # This function call now only executes: + # - SELECT count(*) FROM my_table + # and no transaction starts. -.. _transaction-block: + cur.execute("INSERT INTO data VALUES (%s)", ("Hello",)) + # The result of this statement is persisted immediately by the database -Transaction blocks ------------------- + # The connection is closed at the end of the block but, because it is not + # in a transaction state, no COMMIT is executed. + +An autocommit transaction behaves more as someone coming from :program:`psql` +would expect. This has a beneficial performance effect, because less queries +are sent and less operations are performed by the database. The statements, +however, are not executed in an atomic transaction; if you need to execute +certain operations inside a transaction, you can achieve that with an +autocommit connection too, using an explicit :ref:`transaction block +`. + + +.. _transaction-context: + +Transaction contexts +-------------------- A more transparent way to make sure that transactions are finalised at the right time is to use ``with`` `Connection.transaction()` to create a -transaction block. When the block is entered a transaction is started; when -leaving the block the transaction is committed, or it is rolled back if an -exception is raised inside the block. +transaction context. When the context is entered, a transaction is started; +when leaving the context the transaction is committed, or it is rolled back if +an exception is raised inside the block. -For instance, an hypothetical but extremely secure bank may have the following -code to avoid that no accident between the following two lines leaves the -accounts unbalanced: +Continuing the example above, if you want to use an autocommit connection but +still wrap selected groups of commands inside an atomic transaction, you can +use a `!transaction()` context: .. code:: python - with conn.transaction(): - move_money(conn, account1, -100) - move_money(conn, account2, +100) + with psycopg.connect(autocommit=True) as conn: + + cur = con.cursor() + + cur.execute("SELECT count(*) FROM my_table") + # The connection is autocommit, so no BEGIN executed. + + with conn.transaction(): + # BEGIN is executed, a transaction started - # The transaction is now committed + cur.execute("INSERT INTO data VALUES (%s)", ("Hello",)) + cur.execute("INSERT INTO times VALUES (now())") + # These two operation run atomically in the same transaction -But because the bank is, like, *extremely secure*, they also verify that no -account goes negative: + # COMMIT is executed at the end of the block. + # The connection is in idle state again. + + # The connection is closed at the end of the block. + + +Note that connection blocks can also be used with non-autocommit connections: +in this case you still need to pay attention to eventual transactions started +automatically. If an operation starts an implicit transaction, a +`!transaction()` block will only manage :ref:`a savepoint sub-transaction +`, leaving the caller to deal with the main transaction, +as explained in :ref:`transactions`: .. code:: python - def move_money(conn, account, amount): - new_balance = add_to_balance(conn, account, amount) - if new_balance < 0: - raise ValueError("account balance cannot go negative") + conn = psycopg.connect() + + cur = con.cursor() + + cur.execute("SELECT count(*) FROM my_table") + # This function call executes: + # - BEGIN + # - SELECT count(*) FROM my_table + # So now a transaction has started. + + with conn.transaction(): + # The block starts with a transaction already open, so it will execute + # - SAVEPOINT + + cur.execute("INSERT INTO data VALUES (%s)", ("Hello",)) + + # The block was executing a sub-transaction so on exit it will only run: + # - RELEASE SAVEPOINT + # The transaction is still on. + + conn.close() + # No COMMIT was sent: the INSERT was discarded. + +If a `!transaction()` block starts when no transaction is active then it will +manage a proper transaction. In essence, a transaction context tries to leave +a connection in the state it found it, and leaves you to deal with the wider +context. + +.. hint:: + The interaction between non-autocommit transactions and transaction + contexts is probably surprising. Although the non-autocommit default is + what demanded by the DBAPI, the personal preference of several experienced + developers is to: + + - use a connection block: ``with psycopg.connect(...) as conn``; + - use an autocommit connection: ``conn.autocommit = True``, eventually as + `!connect()` parameter too; + - use `!with conn.transaction()` blocks to manage transactions only where + needed. + + +.. _nested-transactions: -In case this function raises an exception, be it the `!ValueError` in the -example or any other exception expected or not, the transaction will be rolled -back, and the exception will propagate out of the `with` block, further down -the call stack. +Nested transactions +^^^^^^^^^^^^^^^^^^^ -Transaction blocks can also be nested (internal transaction blocks are +Transaction blocks can be also nested (internal transaction blocks are implemented using SAVEPOINT__): an exception raised inside an inner block has a chance of being handled and not completely fail outer operations. The following is an example where a series of operations interact with the -database: operations are allowed to fail, plus we also want to store the +database: operations are allowed to fail; at the end we also want to store the number of operations successfully processed. .. __: https://www.postgresql.org/docs/current/sql-savepoint.html -- 2.47.2