Commit modes in Python applications

You can control how groups of SQL statements are committed by specifying a commit mode for a connection resource. The ibm_db API supports two commit modes: autocommit and manual commit.

Autocommit mode

In autocommit mode, each SQL statement is a complete transaction, which is automatically committed. Autocommit mode helps prevent locking escalation issues that can impede the performance of highly scalable web applications. By default, the ibm_db API opens every connection in autocommit mode.

If autocommit mode is disabled, you can enable the autocommit mode by calling ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON), where conn is a valid connection resource.

Calling the ibm_db.autocommit function might affect the performance of your Python scripts because it requires additional communication between Python and the database management system.

Manual commit mode

In manual commit mode, the transaction ends when you call the ibm_db.commit or ibm_db.rollback function. This means that all statements executed on the same connection between the start of a transaction and the call to the commit or rollback function are treated as a single transaction.

Manual commit mode is useful if you might have to roll back a transaction that contains one or more SQL statements. If you execute SQL statements in a transaction and the script ends without explicitly committing or rolling back the transaction, the ibm_db extension automatically rolls back any work that is performed in the transaction.

You can turn off autocommit mode when you create a database connection by using the { ibm_db.SQL_ATTR_AUTOCOMMIT: ibm_db.SQL_AUTOCOMMIT_OFF } setting in the ibm_db.connect or ibm_db.pconnect options array. You can also turn off autocommit mode for a connection resource by calling ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF), where conn is a valid connection resource.

For more information about the ibm_db API, see https://github.com/ibmdb/python-ibmdb/wiki/APIs.

import ibm_db

array = { ibm_db.SQL_ATTR_AUTOCOMMIT : ibm_db.SQL_AUTOCOMMIT_OFF }
conn = ibm_db.pconnect("SAMPLE", "user", "password", array)
sql = "DELETE FROM EMPLOYEE"
try:
   stmt = ibm_db.exec_immediate(conn, sql)
except:
   print "Transaction couldn't be completed."
   ibm_db.rollback(conn)
else:
   ibm_db.commit(conn)
   print "Transaction complete."