3.8.2. The Read/Write Transaction

The write transaction trWrite that we use for our FDUpdateSQL object must be as short as possible to prevent the oldest active transaction from getting “stuck” and inhibiting garbage collection. High levels of uncollected garbage will lead to lower performance. Since the write transaction is very short, we can use the SNAPSHOT isolation level. The default value of the Options.DisconnectAction property, xdCommit, is not appropriate for write transactions, so it should be set to xdRollback. We will not rely on starting and ending transactions automatically. Instead, we will start and end a transaction explicitly. Thus, our transaction should have the following properties:

Options.AutoStart = False
Options.AutoCommit = False
Options.AutoStop = False
Options.DisconnectAction = xdRollback
Options.Isolations = xiSnapshot
Options.ReadOnly = False

SNAPSHOT vs READ COMMITTED Isolation

It is not absolutely necessary to specify SNAPSHOT isolation for simple INSERT/UPDATE/DELETE operations. However, if a table has complex triggers or a stored procedure is executed instead of a simple INSERT/UPDATE/DELETE query, it is advisable to use SNAPSHOT. The reason is that READ COMMITTED isolation does not ensure the read consistency of the statement within one transaction, since the SELECT statement in this isolation can return data that were committed to the database after the transaction began. In principle, SNAPSHOT isolation is recommended for short-running transactions.