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).

Trigger difference between SQL Server and Oracle

(From the guide of migrating Oracle to SQL Server 2008)

For DML triggers only.

1. Row level trigger vs statement level (table) trigger

The first major difference between Oracle and SQL Server triggers is that the most common Oracle trigger is a row-level trigger (FOR EACH ROW), which fires for each row of the source statement. SQL Server, however, supports only statement-level triggers, which fire only once per statement, irrespective of the number of rows affected.

Oracle Row-level triggers are emulated with a cursor loop in SQL Server.
The following code is for after insert.

DECLARE ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT [ROWID], , .. FROM inserted

OPEN ForEachInsertedRowTriggerCursor
FETCH NEXT FROM ForEachInsertedRowTriggerCursor INTO v1,v2...
WHILE @@fetch_status = 0
BEGIN
...
FETCH NEXT FROM ForEachInsertedRowTriggerCursor INTO v1,v2...
END

CLOSE ForEachInsertedRowTriggerCursor DEALLOCATE ForEachInsertedRowTriggerCursor

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
    - Make a complex security check on the current time or user
    - Generate a single audit record


2. temporary tables used in trigger

In a row-level trigger, Oracle uses an :OLD alias to refer to column values that existed before the statement executes, and to the changed values by using a :NEW alias. SQL Server uses two pseudotables, inserted and deleted, and each can have multiple rows.

If the triggering statement is UPDATE, a row's older version is present in deleted, and the newer in inserted. But it is not easy to tell which pair belongs to the same row if the updated table does not have a primary key or the primary key was modified. PK is important if you want to implement triggers on that table.


3 No Before trigger in SQL Server

The third major difference between Oracle and SQL Server triggers comes from Oracle BEFORE triggers. Because Oracle fires these triggers before the triggering statement, it is possible to modify the actual field values that will be stored in the table, or even cancel the execution of the triggering statement if it is found to be unnecessary. To emulate this in SQL Server, you must create INSTEAD OF triggers. All triggers for a specific event should go into one target instead of trigger.

4. No column sensitive trigger in SQL Server

Sometimes an Oracle trigger is defined for a specific column with the UPDATE OF column [, column ]... ] clause. To emulate this, it can be done with the following SQL Server construction:


IF (UPDATE(column) [OR UPDATE(column) . . .]
BEGIN

END

(
Update(column)
Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions
)

Partition

Check information on partitions.

--view partition funcitons
select * from sys.partition_functions

--view how the partition has happened

select * from sys.partition_range_values

--view the partition schema

select * from sys.partition_schemes

--view data spaces used by partition schema

select * from sys.destination_data_spaces

--check which partition the given value range in partition column is in

$PARTITION

$PARTITION.function_name(value)

An example to check how rows in table are distributed to partitions

SELECT $partition.pf(PT.column used for partition) --function name
                  AS [Partition Number]
      , min(PT.column used for partition) AS [MinID]
      , max(PT.column used for partition) AS [MaxID]
      , count(*) AS [Rows In Partition]
FROM dbo.partitionedTable AS PT (NOLOCK)
GROUP BY $partition.pf(PT.column used for partition) --partition number
ORDER BY [Partition Number]

--Verify rows inserted in partitions

select * from sys.partitions

where object_name(object_id)='partitionTable'

--switch a certain partition to a table
alter table partitiontable switch partition partitionnumber to destinationtable

(
--a technique to move table to a different file group: move the content while dropping clustered pk
alter table tablename drop clusteredPK with (move to filegroup)

)

Transaction impacted by broken network connection

In the middle of a transaction, if the connection to the database is broken, the corresponding SPID will be cleared, the uncommitted transaction will be rolled back.

If there are processed being blocked by the transaction from the broken connection, the blocking will be removed.

SQL Agent Job -- Log More Information

1. in the script it runs, just add the print statement
2. change the step property to Apend output to step history.(Or define an output file for it.)
3. view the execution log using script. Here's one can be further modified.

select l.step_id,l.step_name,l.message,run_date,run_time,run_duration
from msdb.dbo.sysjobhistory l join msdb.dbo.sysjobs_view j
on l.job_id=j.job_id
where j.name='xxxxx'
and l.run_date='20120101'
and l.run_time>'10000'
order by l.run_time desc

The output will be appended to the job history. But make sure to limit the length under 1024 characters.

examine query plan

the following query can be modified to examine more content of cached query plans.

--sql statement and its query plan

SELECT t.[text], qp.query_plan
    FROM sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp;

SQL Agent Alert

This is an example of sending email to dbteam if a process is blocked for longer than 10 minutes.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'xxxIsBlocked',
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @notification_message=N'A process''s waiting for lock for more than 10 minutes',
        @performance_condition=N'SQLServer:Locks|Lock Wait Time (ms)|Key|>|600000',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'xxxIsBlocked', @operator_name=N'DBTeam', @notification_method = 1
GO