Handling Transactions with Connector/Python

Transactions

A transaction is a way to execute a set of SQL statements such that either all of the statements execute successfully or none at all. In other words, all the statements in a transaction are executed as a single unit. If any one of the statements in a transaction fails, then the database is rolled back to the point at which transaction began.

Note: In MySQL, only InnoDB table supports transaction.

autocommit mode in MySQL

By default, whenever you execute a SQL statement, MySQL immediately writes the result of the statement to the database. Another way to think about this is that the MySQL executes each statement as a separate transaction which implicitly starts when you execute the statement and ends as soon as the statement is done executing. This behavior is called the *autocommit* mode.

To view the current autocommit setting, type the following in the MySQL shell:

As you can see, the @@autocommit system variable is set to 1 which means that the autocommit mode is enabled.

If you disable the autocommit mode then the SQL statements that follow will be part of a transaction, and you will have to commit them manually using the COMMIT statement before the MySQL session ends, otherwise, the changes made by the statements will be rolled back.

To disable the autocommit mode set AUTOCOMMIT to 0, as follows:

To verify the changes, let’s print the value of @@autocommit system variable again.

The autocommit mode is now disabled. From now on, you will have to explicitly commit the statements before the MySQL session ends. If you fail to do so, changes made by the statements will not be saved into the database.

Let’s see this behavior in action:

Right now we have 4080 rows in the city table.

Let’s now see what would happen if we try to execute a SQL statement which deletes all the rows from the city table.

It looks like all the rows from the city table is deleted. However, this is not case as we haven’t committed the transaction (via the COMMIT statement) yet.

Quit the current MySQL session by hitting Ctrl+D or typing exit.

Relaunch the mysql-client and query the city table again.

As expected, no rows are deleted from the city table. However, if we hadn’t disabled the autocommit mode (SET AUTOCOMMIT=0) before executing the DELETE statement, all the rows from the city table would have been deleted.

Also, when we restart a new session the autocommit mode is again turned on. Thus, again individual SQL statements will be executed in a separate transaction.

Now, you know how the autocommit works, let’s now shift our attention to how MySQL Connector/Python deals with the transaction.

Performing Transactions with MySQL Connector/Python

By default, Connector/Python turns the autocommit mode off. This is the reason, why we had to use the commit() method of the connection object to commit the changes. You can also use the rollback() method of the connection object to rollback the transaction.

To enable the autocommit mode set autocommit argument of the connection object to True.

The following listing demonstrates the behavior of Connector/Python when autocommit=True.

Expected Output:

This script creates an employees table and inserts two rows into it. Since we have set autocommit=True, we don’t need to call the commit() method manually to commit the changes.

If you look at the employees table, it should look like this:

Now let’s see what would happen if we try to execute the same script with autocommit=False.

First off, drop the employees table using the DROP statement:

Next, set the autocommit=False and run the script again.

This time you will get the same output as before. However, if you look at the world database you will find that the employees table is still gets created but without any rows.

This happens because DDL statements such as CREATE, DROP, TRUNCATE etc. always perform an implicit commit. This behavior is not just specific to MySQL, in fact, Oracle does the same thing. For this reason, you should not try to include such statements in a transaction.

start_transaction() method

If autocommit is enabled (i.e autocommit=True) and you want to execute a set of statements as a single unit, then you must explicitly start the transaction using the start_transaction() method of the connection object.

Once you have executed all the statements in a transaction you can either commit them by calling the commit() method or undo them by calling the rollback() method.

Once you have executed all the statements in a transaction you can commit them using the commit() method. To undo the changes call the rollback() method instead of commit().

If a transaction is already in progress then invoking start_transaction() raises ProgrammingError exception. You can check for an ongoing transaction using the in_transaction property. The in_transaction returns True, if a transaction is active, otherwise False.

The following listing demonstrates start_transaction() method in action.

Expected Output:

This script tries to execute two INSERT statement in a transaction, which means that either both of the statements execute successfully or none at all.

The start_transaction() method can also be used when autocommit is disabled to change the default behavior of the transaction. To learn more about options available visit the documentation.

Leave a Comment