Batch and Transaction Management

Perhaps the most compelling reason to use a batch SQL approach is the need for a finer level of transaction control. When using an Access database with a single user, transaction management is a minor concern at best. However, when multiple users are accessing a data source, with the potential of simultaneous updates, transaction management can have a huge impact on the performance and reliability of SQL Server.

In the previous example, the transaction is committed to the server—i.e. the changes are actually applied to the data—as soon as it is executed. In many cases this is perfectly acceptable. However, if we need a finer level of control over when a transaction is committed, either for performance reasons or due to dependencies between records, we can choose to define the beginning and the end of each transaction.

We looked briefly at transactions in the previous chapter. To define the beginning and end of transaction, we use the BeginTrans and CommitTrans methods of the Connection object:

Conn.BeginTrans 
Conn.Execute SQLQuery, lRecs
Conn.CommitTrans

Here we explicitly define the beginning of a transaction. By doing so, the underlying ODBC provider will no longer automatically commit a transaction. To apply changes to a database, the Connection object's CommitTrans method is called. If an error occurs, the RollbackTrans method is available. RollbackTrans will 'undo' any changes made as a result of the SQL that has been submitted since the last CommitTrans was called.

Things to Consider When Using Transactions

To get the maximum performance when using transaction statements to process database records, we need to structure them well. The following is a list of guidelines that may be helpful as you experiment with transactions:

Keep the transaction processing blocks as short as possible. Remember that as long as a transaction is open on a series of records (especially in the case of an update), other people cannot access or change them. For example:

Conn.BeginTrans
  Statement1...
  Statement2...
  Statement3...
  Statement4...  '*** If you have a number of statements,
                 '*** you could slow processing and increase 
                 '*** the chance of errors occurring.
Conn.CommitTrans  (or RollbackTrans)

When working with transaction statements, don’t create a new connection or perform further database processing until the transaction is complete. Jumping out of an open transaction can lead to contention and lockout conditions:

Conn.BeginTrans
  Statement1...
  Statement2...

  '*** Don’t create a new transaction within an open tranaction

  Set RS = Server.CreateObject("ADODB.RecordSet")
  RS.Open "select * from Message", DBConn, 3, 1, 2
  If NOT RS.EOF Then 
    Statement3...
    Statement4...
  End if
Conn.CommitTrans  (or RollbackTrans)

When working with a Recordset object, don't refresh the object in the middle of a series of transactions. Doing so will create the "Attempt to Commit or Rollback without BeginTrans" error:

Conn.BeginTrans
  Statement1...
  If UserReset Then 
    Rs.Refresh  '*** Can cause an error
  End If 
  Statement2...
  Statement3...
Conn.CommitTrans  (or Rollback)

When using transactions to control server updates, don't execute a Close in the middle of a transaction. This can lead to problems when attempting to commit the transaction:

Conn.BeginTrans
  Statement1
  If Error
    db.Close  '*** Commit will fail after this
  End If
  Statement2...
  Statement3...
Conn.CommitTrans  (or Rollback)

As you can see, we can now specify when and under what conditions data is applied to our data server. In addition, and perhaps more importantly, we can undo these changes at any point in the process.