Transaction management in PostgreSQL

by Alex
Transaction management in PostgreSQL

In this tutorial we will see how to manage PostgreSQL transactions from Python using psycopg2. A transaction is a unit of work that affects a set of database operations.

Preparing

In most cases, you need to perform a database operation only after some other operation is complete. For example, in a banking system, you can only transfer an amount from account A to account B after withdrawing funds from account A. In a transaction, either all transactions are executed or none of them are executed. Thus, it is necessary to perform all transactions in one transaction for it to be successful. This is what we are going to talk about next:

  • Enabling and disabling the auto-commit (auto-save) mode.
  • Transactions for saving changes in the database.
  • Support for ACID property of a transaction.
  • Rollback of all transactions in case of failure.
  • Changing the isolation level of a PostgreSQL transaction from Python.

Transaction management psycopg2

PostgreSQL transactions are handled by the connection object. It is responsible for saving changes or undoing them if they fail. The cursor object is used to perform transactions in the database. You can create an unlimited number of cursor objects from the connection object. If any of the cursor object commands fails or the transaction is undone, the following commands are not executed until the connection.rollback() method is called.

  • The connection object is responsible for stopping the transaction. This can be done using the commit() or rollback() methods.
  • After using the commit () method the changes are saved in the database.
  • With the rollback() method you can roll back the changes.

Note: A call to the callose() method or any other method responsible for destroying the connection object results in an implicit call to rollback(), causing all changes to be rolled back.

Connection.autocommit

By default, the connection works in autocommit mode, that is, the auto-commit property is True. This means that if the query succeeds, the changes are immediately saved in the database, and rollback becomes impossible. To execute queries in a transaction, this property must be disabled. To do that, make connection.autocommit=False. In this case it will be possible to rollback the executed query to the original state in case of failure. Syntax of autocommit:

connection.autocommit=True or Falsec

Connection.commit

If all transactions in a transaction are complete, use connection.commit() to save changes to the database. If you don’t use the method, all data interaction effects will not be applied. The syntax for commit:

connection.commit()

Connection.rollback

If at least one transaction fails, connection.rollback() is used to undo the changes. The syntax of rollback:

connection.rollback()

PostgreSQL transaction management example from Python

  1. Disable auto-commit mode.
  2. If all operations were completed successfully, use connection.commit() to save them to the database.
  3. If any of the operations failed, use connection.rollback() to roll back to the last state.

Note: a transaction remains open until commit() or rollback() is called explicitly. Let’s downgrade one phone and upgrade the other.


import psycopg2
from psycopg2 import Error
try:
# Connect to an existing database
connection = psycopg2.connect(user="postgres",
# password that you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
connection.autocommit=False
cursor = connection.cursor()
amount = 200
query = """select price from mobile where id = 1"""
cursor.execute(query)
record = cursor.fetchone() [0]
price_a = int(record)
price_a -= amount
# Decrease price of the first
sql_update_query = """update mobile set price = %s where id = 1""
cursor.execute(sql_update_query,(price_a,))
query = """select price from mobile where id = 2""
cursor.execute(query)
record = cursor.fetchone() [0]
price_b = int(record)
price_b += amount
# Increase price of the second
sql_update_query = """Update mobile set price = %s where id = 2""
cursor.execute(sql_update_query, (price_b,))
# transaction execution
connection.commit()
print("The transaction was successfully completed")
except (Exception, psycopg2.DatabaseError) as error :
print ("Transaction error. Cancel all other transactions", error)
connection.rollback()
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")

You will get the following output after all transactions in the transaction are successfully completed.

The transaction has been successfully closed
Connection with PostgreSQL closed
If at least one of the operations completes with an error, the output will be as follows.
Error in transaction. Cancel all other transactions
Connection with PostgreSQL closed

With instruction to control transactions

Use the with instruction to create a transaction within a certain block in your code. What does the with instruction do? Connections and cursor objects in psycopg2 are just context managers that can be used with with. The main advantage is that it allows you not to explicitly write commit or rollback. Syntax:

with psycopg2.connect(connection_arguments) as conn:
with conn.cursor() as cursor:
cursor.execute(Query)

When the connection exits the with block and the request is executed without errors or exceptions, the transaction is saved. If there is a problem in the process, the transaction is rolled back. A connection is not closed when it leaves the block, but cursor objects and any other related objects are. A connection can be used in more than one with, and each with block is just a transaction. In this example, let's execute a transaction in an online trading platform. Let's buy one item, reduce the buyer's balance, and transfer the appropriate amount to the company's account.


import psycopg2
from psycopg2 import Error
connection = psycopg2.connect(**params)
with connection:
with connection.cursor() as cursor:
# Search for the price of the item
query = """select price from itemstable where itemid = 876""
cursor.execute(query)
record = cursor.fetchone() [0]
item_price = int(record)
# Getting the balance of the account
query = """select balance from ewallet where userId = 23""
cursor.execute(query)
record = cursor.fetchone() [0]
ewallet_balance = int(record)
new_ewallet_balance -= item_price
# Balance update with item_price
sql_update_query = """Update ewallet set balance = %s where id = 23"""
cursor.execute(sql_update_query,(new_ewallet_balance,))
# company balance entry
query = """select balance from account where accountId = 2236781258763"""
cursor.execute(query)
record = cursor.fetchone()
account_balance = int(record)
new_account_balance += item_price
# Company account update
sql_update_query = """Update account set balance = %s where id = 132456""
cursor.execute(sql_update_query, (new_account_balance,))
print("Transaction completed successfully")

Isolation levels psycopg2

In database systems, isolation levels can be used to determine what level of transaction integrity will be visible to other users and systems. For example, when a user performs an action or transaction that has not yet been completed, details about it are available to other users who can perform sequential actions. For example, a consumer purchases an item. The information about this becomes known to other systems, which is necessary to prepare the invoice, receipt and discount calculations, which speeds up the entire process. If the level of isolation is low, multiple users can access the same data at the same time. However, this can also lead to some problems, up to and including lost updates. So you have to consider everything. A higher level of isolation can block other users or transactions before completion. psycopg2.extensions provides the following levels of isolation:

  • psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT,
  • psycopg2.extensions.ISOLATION_LEVEL_READ_UNCOMMITTED,
  • psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED,
  • psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ,
  • psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
  • psycopg2.extensions.ISOLATION_LEVEL_DEFAULT.

How to set isolation levels

This can be done with the connection class:

conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

You can also use the connection.set_session method.

connectio.set_session(isolation_level=None, readonly=None, deferrable=None, autocommit=None)

In this case the isolation_level can be READ UNCOMMITED, REPEATABLE READ, SERIALIZE or others from the list of constants.

Related Posts

LEAVE A COMMENT