Managing eXtremeSQL Transactions in Python

All eXtremeDB database access is performed within transactions to assure database integrity by enforcing the ACID principles (see the eXtremeDB User Guide for a detailed description of transaction processing). When using the eXtremeSQL API, transaction processing is implicit, i.e. transactions are started and committed by the runtime without requiring explicit function calls. Specifically, the Cursor method execute() starts and closes a READ_ONLY transaction when performing an SQL SELECT (unless the FOR UPDATE clause is specified), and a READ_WRITE transaction when performing INSERT, UPDATE or DELETE statements.

These implicit transactions are managed internally by the SQL engine and for most database operations the application developer need not be concerned with the transaction interface. However, some applications need to perform updates that require transaction blocking that spans more than one SQL statement.

To take control of SQL transaction processing in Python, the SQL start transaction, commit transaction and/or rollback transaction statements are used. For example:

 
    cursor = conn.cursor()
    cursor.execute("START TRANSACTION");
    cursor.execute("UPDATE MyTable SET b=5 WHERE a=4");
    cursor.execute("CHECKPOINT TRANSACTION");
    cursor.execute("INSERT INTO MyTable VALUES(1,2)");
    cursor.execute("COMMIT TRANSACTION");
     

Checkpoint and Autocheckpoint

As explained in the eXtremeSQL Transaction Control page, a checkpoint operation may be necessary in complex transactions to update indexes prior to the transaction commit. For example:

     
    cursor = conn.cursor()
    cursor.execute("START TRANSACTION");
    cursor.execute("UPDATE MyTable SET b=5 WHERE a=4");
    cursor.execute("CHECKPOINT TRANSACTION");
    cursor.execute("SELECT * FROM MyTable WHERE a=4");
    ...
    cursor.execute("COMMIT TRANSACTION");
     

To enable the autocheckpoint feature, call the SQL function autocheckpoint() with argument value 1; to disable use argument value 0. For example:

 
    cursor = conn.cursor()
    cursor.execute("select autocheckpoint(1)")
    cursor.execute("START TRANSACTION");
    cursor.execute("UPDATE MyTable SET b=5 WHERE a=4");
    cursor.execute("SELECT * FROM MyTable WHERE a=4");
    ...
    cursor.execute("COMMIT TRANSACTION");