Transactions in SQL Server 2000

Synopsis

Taking forward the topic of the previous article Transactions, within this article we will discuss Transactions with respect to databases and will also see an example which would be implemented in SQL Server displaying how to use Transactions.

Defining the Problem

Transactions can implemented either on the database side or it can be implemented on the front-end side by using the Transaction object support that is provided by ADO.NET. The previous article The Transaction Object in ADO.NET, detailed the usage of Transaction objects provided by ADO.NET. Within this article we will be seeing usage of the Transactions on the database side.

Defining the Solution

The idea of the Transaction has become one of the core concepts of modern databases. The concept of the transaction is to consider multiple tasks as a single unit. The transaction when completes, it either performs all of the tasks within it or none. Thus a transaction is either implemented completely or is not implemented at all and has no intermediate state.

Transactions in SQL Server are mostly implemented within the stored procedures. Normally three statements are mostly used for implementing transactions, which are as follows;

1). Begin Transaction

The statement indicates the starting of a new transaction.

2). Commit Transaction

The statement indicates the successful completion of a transaction. Upon commit a transaction is considered  to be successfully completed and all changes made to the database are implemented.

3). Rollback Transaction

The statement indicates the unsuccessful completion of a transaction. Upon receiving a rollback statement, all changes are discarded and objects are returned to the previous state since the most recent Begin Transaction.

Example:

Let's now have a look at an example displaying the usage of transaction. Note that all the example is being carried out in SQL Server, I will not be using ASP.NET in the examples anywhere. Open up SQL Server and create a new table by the name of books. Create fields within the table as shown in the figure below.

(Figure 1: Schema of the Books Table)

Now fill up the table with some records. I have filled it up with some raw data. The figure below shows the records in the table.

(Figure 2: Records filled up in the table)

Having done all this its time to create a stored procedure. So below is the code given for the stored procedure.

The stored procedure has been named as UpdatePrices. The stored procedure updates the prices of the books having price $45 and $50. The first Update statement simply updates the price of $45 to $60.

UPDATE Books SET Price = 60 WHERE Price = 45

The second Update statement however has a condition that it must first fulfill in order to have the price of $60 updated to $75. According to the condition first of all the total number of books that have a price of $50 are counted. If the number of the books having price $50 is less than 3, the transaction is cancelled and a rollback occurs, otherwise the prices of the books will be updated to $75 and the transaction would be successfully completed by calling commit.

Now to test this, open up the Query Analyzer in SQL Server and execute the stored procedure by writing the following;

exec UpdatePrices

Make sure you have the database selected, in which you have created the table and the stored procedure, as the current database.

Upon executing the instruction, you would see as shown in the figure below.

(Figure 3: Successful completion of the Transaction)

As you can see the results display successful execution of both the Update statements. This is because all the requirements that had been coded in the stored procedure were met.

Next Page