This is documentation for Mathematica 6, which was
based on an earlier version of the Wolfram Language.
View current documentation (Version 11.1)


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 DatabaseExamples` 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.
Click for copyable input
This creates a table to use for testing.
Click for copyable input
This uses SQLSelect to view data in the TEST table. There is one row.
Click for copyable input
SQLBeginTransaction is used to start a transaction.
Click for copyable input
Next, two different insert operations are carried out.
Click for copyable input
This shows that two rows have been inserted.
Click for copyable input
If SQLRollbackTransaction is used, the database is returned to the point before the transaction began. The two rows are no longer present.
Click for copyable input
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.
Click for copyable input
This uses SQLCommitTransaction to commit the data permanently.
Click for copyable input
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.
Click for copyable input
A savepoint is created.
Click for copyable input
Here some more data is inserted into the database.
Click for copyable input
The transaction is rolled back to the savepoint using SQLRollbackTransaction.
Click for copyable input
This shows that the last insert has not taken place.
Click for copyable input
This drops the TEST table and closes the connection.
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 can be termed as 'dirty reads', 'non-repeatable reads', and 'phantom reads'. There are two types of solution to these problems, one involves setting options for the database connection to isolate transactions, and the other involves other checks on data or instance by checking timestamps. Each of these strategies have 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 TransactionIsolationLevel option of OpenSQLConnection. This option can also be set for an existing connection with SetOptions.
ReadUncommittedno isolation
ReadCommittedprevent dirty reads
RepeatableReadprevent dirty reads and non repeatable reads
Serializableprevent dirty reads, non repeatable reads, and phantom reads

Settings of the TransactionIsolationLevel option.