Transactions

Some database operations involve carrying out a sequence of database commands. For example, information in two different tables may need to be updated. In these cases, it may be very important that if one update is carried out, the other is also. If only one is done, it may leave the data inconsistent. You can use database transactions to ensure that all the operations are carried out. In addition, you can use transactions as a way of backing out of the middle of a sequence of operations. This tutorial will demonstrate how to use transactions.

If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

SQLBeginTransaction[conn] begin an SQL transaction
SQLCommitTransaction[conn]permanently commit an SQL transaction to the database
SQLRollbackTransaction[conn]terminate an SQL transaction (do not change the database)

Functions for executing SQL transactions.

This loads DatabaseLink and connects to the demo database.

In[28]:=
Click for copyable input

This creates a table to use for testing.

In[30]:=
Click for copyable input

This uses SQLSelect to view data in the TEST table. There is one row.

In[7]:=
Click for copyable input
Out[7]//TableForm=

SQLBeginTransaction is used to start a transaction.

In[8]:=
Click for copyable input

Next, two different insert operations are carried out.

In[9]:=
Click for copyable input

This shows that two rows have been inserted.

In[11]:=
Click for copyable input
Out[11]//TableForm=

If SQLRollbackTransaction is used, the database is returned to the point before the transaction began. The two rows are no longer present.

In[12]:=
Click for copyable input
Out[13]//TableForm=

A transaction is closed when it is rolled back. If any more transactions are required, a new transaction must be started. Here, a new transaction is started and the two rows are reinserted.

In[14]:=
Click for copyable input

This uses SQLCommitTransaction to commit the data permanently.

In[17]:=
Click for copyable input
Out[18]//TableForm=

A transaction is closed when it is committed. If any more transactions are required, a new transaction must be started. In addition, once a transaction has been committed, it cannot be rolled back. Transactions may be split up using an SQLSavepoint; a rollback can be made to a specific savepoint.

The following begins a transaction and inserts some data.

In[19]:=
Click for copyable input

A savepoint is created.

In[21]:=
Click for copyable input
Out[21]=

Here some more data is inserted into the database.

In[22]:=
Click for copyable input
Out[23]//TableForm=

The transaction is rolled back to the savepoint using SQLRollbackTransaction.

In[24]:=
Click for copyable input

This shows that the last insert has not taken place.

In[25]:=
Click for copyable input
Out[25]//TableForm=

This drops the TEST table and closes the connection.

In[26]:=
Click for copyable input

Transaction Isolation

When working with database transactions with more than one concurrent user, various problems with reading data can occur. These problems are known as "dirty reads", "non-repeatable reads", and "phantom reads". There are two types of solutions to these problems: one involves setting options for the database connection to isolate transactions and the other involves other checks on data, for instance by checking timestamps. Each of these strategies has advantages and disadvantages; for example, setting database options can degrade the performance of the database for concurrent usage.

The actual details of these strategies are really outside the scope of this documentation. However, DatabaseLink has a number of ways to set options of the connection to help isolate transactions. This is done with the option of OpenSQLConnection. This option can also be set for an existing connection with SetOptions.

setting
description
ReadUncommittedno isolation
ReadCommittedprevent dirty reads
RepeatableReadprevent dirty reads and non-repeatable reads
Serializableprevent dirty reads, non-repeatable reads, and phantom reads

Settings of the option.

New to Mathematica? Find your learning path »
Have a question? Ask support »