BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Using Transactions in SQL Server

The twentieth part of the SQL Server Programming Fundamentals tutorial describes the use of transactions. Transactions allow you to execute several related T-SQL statements and ensure that the database remains consistent if one of the commands fails.

Lock Basics

When a transaction is being executed it must be isolated. This means that commands executed on other connections must not be affected by the information that has not yet been committed. This is achieved by placing locks on the affected data.

When a lock is in place, statements on other connections are prevented from modifying the dirty data and, in some cases, from reading the information that is about to change. Locks will be explained in more detail later in a later article in this tutorial. However, it is important to understand the basic effect of locks and their impact on other processes.

To demonstrate the impact of a simple lock, execute the following statements. These start a new transaction and create a new job.

BEGIN TRAN

INSERT INTO Jobs
    (JobId, StandardJobId, ContractNumber, VisitDate)
VALUES
    ('073E2600-2426-4B86-B744-1DA722C73D7C', 3, 1, '2008-03-18')

With the transaction still open, we can simulate another user by creating a second connection. To do so, open a new query window and execute the following statement:

SELECT * FROM Jobs

You will see that the SELECT statement does not return any data and does not complete. The statement has been blocked by the locks created by the earlier transaction. Switch back to the original query window and roll back the transaction:

ROLLBACK TRAN

If you now switch back to the SELECT statement's query window, you should find that it has finished executing and returned the Jobs table's data.

The extent of the locks depends upon the amount of data being affected. Often individual rows are locked to minimise the impact on other processes. However, if large amounts of data are being modified, entire pages or tables may be locked to other users. For this reason, you should keep transaction durations short and limit the amount of data modified wherever possible.

8 January 2009