Share via


Types of Transactions

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) supports two types of transactions: explicit and autocommit.

Explicit Transactions

An explicit transaction is one in which you explicitly define both the start and end of the transaction. This can be specified by using either SQL statements or database API functions.

SQL Statements

By using SQL Server Management Studio, the following SQL statements can be used to define explicit transactions:

  • BEGIN TRANSACTION
    Marks the starting point of an explicit transaction for a connection.
  • COMMIT TRANSACTION
    Ends a transaction successfully if no errors are encountered. All data modified by the transaction becomes a permanent part of the database. Resources held by the transaction are freed.
  • ROLLBACK TRANSACTION
    Erases a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.

ADO.NET and OLE DB

You can also use explicit transactions in ADO .NET and OLE DB.

In ADO .NET, use the BeginTransaction method on a SqlCeConnection object to start an explicit transaction. To end the transaction, call the Commit or Rollback methods of the SqlCeTransaction object.

In OLE DB, call the ITransactionLocal::StartTransaction method to start a transaction. Call either the ITransaction::Commit or ITransaction::Abort method with fRetaining set to FALSE to end the transaction without automatically starting another transaction.

Note

You can create multiple transactions on a single ADO .NET connection and assign them to individual commands.

Important

When you commit transactions by using the ADO .NET API, all open data readers and result sets within that transaction should be closed.

Autocommit Transactions

Autocommit mode is the default transaction management mode of SQL Server Compact Edition. Every SQL statement is committed or rolled back when it finishes. A SQL Server Compact Edition connection operates in autocommit mode whenever this default mode has not been overridden by explicit transactions. Autocommit mode is also the default mode for ADO .NET and OLE DB.

A SQL Server Compact Edition connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction. When the explicit transaction is committed or rolled back, SQL Server Compact Edition returns to autocommit mode.

Compile and Run-time Errors

Unlike Microsoft SQL Server, SQL Server Compact Edition does not process statements in batches. SQL Server Compact Edition processes the statements one at a time and executes each statement individually. If a particular statement returns an error, that does not affect the state of any other statements that are included in that batch. For example, if SQL Server Management Studio is used to execute the following set of queries in SQL Server Compact Edition, the first two queries would succeed but the third would fail because of the syntax error.

CREATE TABLE TestData (col int);

INSERT INTO TestData VALUES (1);

INSERTT INTO TestData VALUES (1);

Note

If these queries were sent as a batch to SQL Server, all would fail because the queries would be parsed together. The syntax error would result in SQL Server not being able to construct an execution plan for the whole batch.

Higher level transactions in which an operation is occurring neither roll back nor commit the transaction.

See Also

Concepts

Transactions (SQL Server Compact Edition)
Controlling Transactions
Transactions and Connectivity

Help and Information

Getting SQL Server Compact Edition Assistance