Database Transaction Management

What is a Database Transaction?

For those familiar with databases, the concept of transactions is fundamental. A database transaction is a sequence of SQL statements that are executed together as a single unit of work. This ensures that either all the statements are committed to the database, or none of them are, maintaining the database's consistency. Transactions are crucial when multiple updates need to be made to a database, where each individual update could temporarily leave the database in an inconsistent state. A classic example is transferring money between bank accounts, where both the debit from one account and the credit to another must be committed together to avoid discrepancies.

Autocommit Mode in mssql-python

In the mssql-python module, the default behavior is autocommit mode, meaning that each SQL statement is automatically committed to the database as soon as it is executed. This simplifies the process for straightforward operations but is not suitable for complex transactions that need to be managed as a single unit of work.

Example of Autocommit Mode

from mssql_python import connect

# Establish a connection
conn = connect("Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;")

# Create a cursor object
cursor = conn.cursor()

# Execute a SQL statement
cursor.execute("INSERT INTO Employees (name, age) VALUES ('John Doe', 30)")

# No need to call conn.commit() as autocommit is True by default

Manual Transaction Management

For scenarios requiring more control, you can disable autocommit mode and manage transactions manually. This involves explicitly starting a transaction, committing it, or rolling it back if necessary.

Disabling Autocommit

To disable autocommit, set the autocommit attribute of the connection object to False.

from mssql_python import connect

# Establish a connection
conn = connect("Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;")

# Disable autocommit
conn.setautocommit(False)

# Create a cursor object
cursor = conn.cursor()

# Execute a SQL statement
cursor.execute("INSERT INTO Employees (name, age) VALUES ('Jane Doe', 25)")

# Commit the transaction
conn.commit()

# Close the connection
conn.close()

Rolling Back Transactions

If an error occurs during a transaction, you can roll back the changes to maintain data integrity.

from mssql_python import connect

try:
    # Establish a connection
    conn = connect("Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;")
    
    # Disable autocommit
    conn.autocommit = False
    
    # Create a cursor object
    cursor = conn.cursor()
    
    # Execute multiple SQL statements
    cursor.execute("INSERT INTO Employees (name, age) VALUES ('Alice', 28)")
    cursor.execute("INSERT INTO Employees (name, age) VALUES ('Bob', 32)")
    
    # Commit the transaction
    conn.commit()
except Exception as e:
    # Roll back the transaction in case of error
    conn.rollback()
    print(f"Transaction failed: {e}")
finally:
    # Close the connection
    conn.close()

Database Transactions in mssql-python

When using mssql-python with autocommit=False, you do not explicitly open a database transaction in your Python code. Instead, a transaction is implicitly started when a Connection object is created. This transaction is then either committed or rolled back by calling commit() or rollback(), respectively. After committing or rolling back, a new transaction is implicitly started. SQL statements are executed using the Cursor.execute() function. For example, the following SQL:

BEGIN TRANSACTION
  UPDATE T1 SET ...
  DELETE FROM T1 WHERE ...
  INSERT INTO T1 VALUES ...
COMMIT TRANSACTION
BEGIN TRANSACTION
  INSERT INTO T2 VALUES ...
  INSERT INTO T3 VALUES ...
COMMIT TRANSACTION

In Python would be:

conn = connect('Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;', autocommit=False)
cursor = conn.cursor()
cursor.execute("UPDATE T1 SET ...")
cursor.execute("DELETE FROM T1 WHERE ...")
cursor.execute("INSERT INTO T1 VALUES ...")
conn.commit()
cursor.execute("INSERT INTO T2 VALUES ...")
cursor.execute("INSERT INTO T3 VALUES ...")
conn.commit()
conn.close()

As you can see, no database transaction is explicitly opened, but they are explicitly committed.

Cursors Do NOT Control Database Transactions

It is important to understand that database transactions are managed through connections, not cursors. Cursors are simply tools to execute SQL statements and manage their results. Transactions are committed or rolled back at the connection level. While there is a commit() function on the Cursor object, it merely calls commit() on the cursor's parent Connection object. When commit() is called on a connection, all updates from all cursors on that connection are committed together. For example:

conn = connect('Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;', autocommit=False)

# Create two cursors on the same connection
cursor1 = conn.cursor()
cursor2 = conn.cursor()

# Execute statements on both cursors
cursor1.execute("DELETE FROM T1 WHERE...")
cursor1.execute("UPDATE T1 SET ...")
cursor2.execute("DELETE FROM T2 WHERE...")
cursor2.execute("UPDATE T2 SET ...")

# Call "commit()" on the first cursor (note, not directly on the connection)
cursor1.commit()  # This commits the results of ALL FOUR cursor1 and cursor2 "execute" statements as one transaction

conn.close()

Concurrent Database Transactions

If you need separate concurrent transactions, you will likely need to create a separate connection object for each transaction. For example:

# Create two connections (with cursors), one for each transaction
conn1 = connect('Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;', autocommit=False)
cursor1 = conn1.cursor()
conn2 = connect('Server=ServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;', autocommit=False)
cursor2 = conn2.cursor()

# Execute the first transaction, but don't commit
cursor1.execute("DELETE FROM T1 WHERE...")
cursor1.execute("UPDATE T1 SET ...")

# While keeping the first transaction open, execute and commit a second transaction
cursor2.execute("DELETE FROM T2 WHERE...")
cursor2.execute("UPDATE T2 SET ...")
conn2.commit()  # Note: cursor2.commit() would have the same effect

# Finally, commit the first transaction
conn1.commit()

conn2.close()
conn1.close()

Always Remember to Commit!

When autocommit is set to False, you must explicitly commit a transaction; otherwise, it will likely be rolled back eventually. For example, when a connection is closed using the close() function, a rollback is always issued. If a Connection object goes out of scope before being closed (e.g., due to an exception), it is automatically deleted by Python, and a rollback is issued as part of the deletion process. The default behavior of the database is to roll back transactions, so always remember to commit your transactions.