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
DatabaseExamples` package, as described in
"Using the Example Databases".
Functions for executing SQL transactions.
This loads
DatabaseLink and connects to the
demo database.
This creates a table to use for testing.
This uses
SQLSelect to view data in the
TEST table. There is one row.
Out[7]//TableForm= |
| |  |
SQLBeginTransaction is used to start a transaction.
Next, two different insert operations are carried out.
This shows that two rows have been inserted.
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.
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.
This uses
SQLCommitTransaction to commit the data permanently.
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.
| Out[21]= |  |
Here some more data is inserted into the database.
Out[23]//TableForm= |
| |  |
The transaction is rolled back to the savepoint using
SQLRollbackTransaction.
This shows that the last insert has not taken place.
Out[25]//TableForm= |
| |  |
This drops the
TEST table and closes the connection.
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.
| |
| ReadUncommitted | no isolation |
| ReadCommitted | prevent dirty reads |
| RepeatableRead | prevent dirty reads and non repeatable reads |
| Serializable | prevent dirty reads, non repeatable reads, and phantom reads |
Settings of the TransactionIsolationLevel option.
