SQL Server

A Database is a software system that defines a collection of predefined operations. Mainly it includes following operations
1.Efficient management of large amount of persistent data in a persistent storage (database)
2.Transaction Management which includes Concurrency Control, Atomicity and backup recovery procedure
3.A DataModel which gives a separate level of abstraction

What is a Transaction?

In .NET environment we can define transaction boundary by Transaction object.

1.If you are using SqlClient (namespace System.Data.SqlClient) Managed Provider you can SqlTransaction object.
2.If you are using Oledb (namespace System.Data.Oledb) Managed Provider you can OledbTransaction object.
3.If you are using Odbc (namespace Microsoft.Data.Odbc) Managed Provider you can OdbcTransaction object

A transaction must follows this properties:-

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:

Atomicity 
A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.

Consistency 
A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.

Isolation 
Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state.

Durability 
If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.

Introduction to Transactions

Atomic means that all the work in the transaction is treated as a single unit. Either it is all performed or none of it is. Consistent means that a completed transaction leaves the database in a consistent internal state. Isolations means that the transaction sees the database in a consistent state. This transaction operates on a consistent view of the data. If two transactions try to update the same table, one will go first and then the other will follow. Durability means that the results of the transaction are permanently stored in the system.

The simplest transaction in SQL Server is a single data modification statement. All my examples use the pubs database. The following

UPDATE  authors
SET au_fname = 'John'
WHERE au_id = '172-32-1176'

is a transaction even though it doesn't do much. It's called an Autocommit transaction. SQL Server first writes to the log file what it's going to do. Then it does the actual update statement and finally it writes to the log that it completed the update statement. The writes to the log file are written directly to disk but the update itself is probably done to a copy of the data that resides in memory. At some future point that database will be written to disk. If the server fails after a transaction has been committed and written to the log, SQL Server will use the transaction log to "roll forward" that transaction when it starts up next.

Multi-Statement Transactions

To make transactions a little more usefull you really need to put two or more statements in them. These are called Explicit Transactions. For example,

BEGIN TRAN

UPDATE  authors
SET au_fname = 'John'
WHERE au_id = '172-32-1176'

UPDATE  authors
SET au_fname = 'Marg'
WHERE au_id = '213-46-8915'

COMMIT TRAN

Note that we have a BEGIN TRAN at the beginning and a COMMIT TRAN at the end. These statements start and complete a transaction. Everything inside these statements is considered a logical unit of work. If the system (Note: change statement to system for clarity) fails after the first update, neither update statement will be applied when SQL Server is restarted. The log file will contain a BEGIN TRAN but no corresponding COMMIT TRAN.

Rolling Back

You can also roll back a transaction if it doesn't do what you want. Consider the following transaction:

BEGIN TRAN

UPDATE  authors
SET au_fname = 'John'
WHERE au_id = '172-32-1176'

UPDATE  authors
SET au_fname = 'JohnY'
WHERE city = 'Lawrence'

IF @@ROWCOUNT = 5
  COMMIT TRAN
ELSE
  ROLLBACK TRAN

Suppose that for whatever reason, the second update statement should update exactly five rows. If @@ROWCOUNT, which hold the number of rows affected by each statement, is five then the transaction commits otherwise it rolls back. The ROLLBACK TRAN statement "undoes" all the work since the matching BEGIN TRAN statement. It will not perform either update statement. Note that Query Analyzer will show you messages indicating that rows were updated but you can query the database to verify that no actual data modifications took place.

Stored Procedures

Hopefully most of your transactions will occur in stored procedures. Let's look at the second example inside a stored procedure.

Create Proc TranTest1
AS
BEGIN TRAN

INSERT INTO [authors]([au_id], 
 [au_lname], 
 [au_fname], 
 [phone], 
 [contract])
VALUES ('172-32-1176', 
 'Gates', 
 'Bill', 
 '800-BUY-MSFT', 
 1)

UPDATE  authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'

COMMIT TRAN
GO

The problem with this stored procedure is that transactions don't care if the statements run correctly or not. They only care if SQL Server failed in the middle. If you run this stored procedure, it will try to insert a duplicate entry into the authors database. You'll get a primary key violation error message. The message will even tell you the statment has been terminated. But the transaction is still going. The UPDATE statement runs just fine and SQL Server then commits the transaction. The proper way to code this is:

Create Proc TranTest2
AS
BEGIN TRAN

INSERT INTO [authors]([au_id], 
 [au_lname], 
 [au_fname], 
 [phone], 
 [contract])
VALUES ('172-32-1176', 
 'Gates', 
 'Bill', 
 '800-BUY-MSFT', 
 1)

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 10
   END

UPDATE  authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'

IF @@ERROR <> 0
   BEGIN
 ROLLBACK TRAN
 return 11
   END

COMMIT TRAN
GO

You'll notice that we check each statement for failure. If the statement failed (i.e. @@ERROR <> 0) then we rollback the work performed so far and use the RETURN statement to exit the stored procedure. It's very important to note that if we don't check for errors after each statement we may commit a transaction improperly.

Stored Procedure and Transactions

I just overheard the following statement – “I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO
-- Create 3 Test Tables
CREATE TABLE TABLE1 (ID INT);
CREATE TABLE TABLE2 (ID INT);
CREATE TABLE TABLE3 (ID INT);
GO
-- Create SP
CREATE PROCEDURE TestSPAS
INSERT INTO TABLE1 (ID)VALUES (1)INSERT INTO TABLE2 (ID)VALUES ('a')INSERT INTO TABLE3 (ID)VALUES (3)GO-- Execute SP
-- SP will error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.
CREATE PROCEDURE TestSPTranAS
BEGIN TRAN
INSERT INTO TABLE1 (ID)VALUES (11)
INSERT INTO TABLE2 (ID)VALUES ('b')
INSERT INTO TABLE3 (ID)VALUES (33)
COMMIT
GO
-- Execute SP
EXEC TestSPTran
GO
-- Check the Values in Tables
SELECT *FROM TABLE1;
SELECT *FROM TABLE2;
SELECT *FROM TABLE3;
GO
-- Clean up
DROP TABLE Table1DROP TABLE Table2DROP TABLE Table3
GO

In this case, there will be no entries in any part of the table.