Transactions and Concurrency control in SQL Server

Mostly from Guide to Migrating Oracle to SQL Server. Credit to MS.

A transaction is closed by COMMIT, ROLLBACK, how is that started?

Choosing a Transaction Management Model

In Oracle, a transaction automatically starts when an insert, update, or delete operation is performed. An application must issue a COMMIT command to save changes to the database. If a COMMIT is not performed, all changes are rolled back or undone automatically.This is known as implicit transaction control.

By default, SQL Server 2005 automatically performs a COMMIT statement after every insert, update, or delete operation. Because the data is automatically saved, you cannot roll back any changes.This is called autocommit transaction control.

You can start transactions in SQL Server 2005 as autocommit, implicit, or explicit transactions. Autocommit is the default behavior; you can use implicit or explicit transaction modes to change the default behavior.

Autocommit Transactions

Autocommit transactions are the default mode for SQL Server 2005. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.

Implicit Transactions

As in Oracle, an implicit transaction starts whenever an INSERT, UPDATE, DELETE, or other data manipulating function is performed. In SQL Server, to allow implicit transactions, use the SET IMPLICIT_TRANSACTIONS ON statement.

If this option is ON and there are no outstanding transactions, every SQL statement automatically starts a transaction. If there is an open transaction, no new transaction will start. The user must explicitly commit the open transaction with the COMMIT TRANSACTION statement for the changes to take effect and for all locks to be released.

Oracle by default is implicit transaction.

Explicit Transactions

An explicit transaction is a grouping of SQL statements surrounded by BEGIN TRAN/WORK and COMMIT or ROLLBACK commands. 

Therefore, for the complete emulation of the Oracle transaction behavior, use a SET IMPLICIT_TRANSACTIONS ON statement.

Choosing a Concurrency Model

This is regarding to how the database engine handles the situation when multiple users update same resource at same time. There are two models for updating data in a database:Pessimistic and Optimistic.

Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

Lower level of isolation will boost concurrency, but with a harm of data integrity/consistency. Higher level of isolation have better guaranty on data consistence but with a cost of resource overhead and performance reduction. Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level.

(Maybe we should not category the concurrency to be pessimistic and optimistic because he definition to them are always ambiguous. They are more meaningful when they are used to describe cursor behaviors. I might be wrong on the following definitions on pessimistic and optimistic definitions.)

 Pessimistic concurrency involves locking the data at the database when you read it so that other user can't modify them during your reading process. You exclusively lock the database record and don't allow anyone to touch it until you are done modifying and saving it back to the database. You have 100 percent assurance that nobody will modify the record while you have it checked out. Another person must wait until you have made your changes(SQL Server exclusively locks the data when it updates them no matter which isolation level that is within.). 

Pessimistic concurrency complies with ANSI-standard isolation levels as defined in the SQL-99 standard. Microsoft SQL Server 2005 has three pessimistic isolation levels:

·         READ COMMITTED

·         REPEATABLE READ

·         SERIALIZABLE

Optimistic concurrency means that you read the database record but don't lock it. Anyone can read and modify the record at any time, so the record might be modified by someone else before you modify and save it. If data is modified before you save it, a collision occurs. Optimistic concurrency is based on retaining a view of the data as it is at the start of a transaction. SQL Server has three optimistic isolation levels, which does not lock data while reading:
(Read operations require only SCH-S table level locks and no page or row locks.)
. READ UNCOMMITTED

. READ_COMMITTED_SNAPSHOT
. SNAPSHOT


This model is embodied in Oracle. The transaction isolation level that implements an optimistic form of database concurrency is called a row versioning-based isolation level.

Since SQL Server 2005 has completely controllable isolation-level models, you can choose the most appropriate isolation level. To control a row-versioning isolation level, use the SET TRANSACTION ISOLATION LEVEL command. SNAPSHOT is the isolation level that is similar to Oracle and does optimistic escalations.

Simulating Oracle Autonomous Transactions

This section describes how SSMA Oracle 3.0 handles autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION). These autonomous transactions do not have direct equivalents in Microsoft SQL Server 2005.

When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. The block becomes an independent transaction started by another transaction, referred to as the main transaction.

To mark a PL/SQL block as an autonomous transaction, you simply include the following statement in your declaration section:

PRAGMA AUTONOMOUS_TRANSACTION;

SQL Server 2005 does not support autonomous transactions. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection.

To convert a procedure, function, or trigger with an AUTONOMOUS_TRANSACTION flag, you split it into two objects. The first object is a stored procedure containing the body of the converted object. It looks like it was converted without a PRAGMA AUTONOMOUS_TRANSACTION flag and is implemented as a stored procedure. The second object is a wrapper that opens a new connection where it invokes the first object. It is implemented via an original object type (procedure, function, or trigger).