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 "DatabaseLink`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.
This creates a table to use for testing.
This uses SQLSelect to view data in the TEST table. There is one row.
SQLBeginTransaction is used to start a transaction.
Next, two different insert operations are carried out.
This shows that two rows have been inserted.
If SQLRollbackTransaction is used, the database is returned to the point before the transaction began. The two rows are no longer present.
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.
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.
Here some more data is inserted into the database.
The transaction is rolled back to the savepoint using SQLRollbackTransaction.
This shows that the last insert has not taken place.
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 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 TransactionIsolationLevel option of OpenSQLConnection. This option can also be set for an existing connection with SetOptions.
setting | description |
| 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.