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
This creates a table to use for testing.
This uses SQLSelect
to view data in the TEST
table. There is one row.
is used to start a transaction.
Next, two different insert operations are carried out.
This shows that two rows have been inserted.
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.
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
|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.