Defining Transactions in SQL Server


Now we will have a look at how transactions can be defined and handled in SQL Server 2005. SQL Server provides different ways to handle transactions, which can be defined on a per-connection basis. Every connection can use the mode it needs to accomplish its requirements. The different modes are:

  • Auto-commit transactions

  • Explicit transactions

  • Implicit transactions

Auto-Commit Mode

SQL Server handles everything as a transaction. It never modifies data outside of a transaction. Therefore, SQL Server has to define a transaction on its own when a transaction is not defined by the developer. Transactions defined by SQL Server are called auto-commit transactions. Auto-commit mode is the default mode in SQL Server.

Exploring Auto-Commit Transactions

1.

From the Start menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Open a New Query window by pressing the New Query button on the toolbar.

2.

Enter and execute the following CREATE TABLE statement to create a small table, which you will use in the next procedure to examine transaction behavior. The code for this example is included in the sample files as ExploringAutoCommit.sql.

USE tempdb; GO CREATE TABLE table1 (     i    int         NOT NULL PRIMARY KEY,     col1 varchar(20) NOT NULL,     col2 varchar(20) NULL);


3.

Now insert three new rows into table1. To do so, enter the following statements in the query window and execute all three together.

USE tempdb; GO INSERT INTO table1 (i,col1,col2) VALUES (1,'First row','First row'); INSERT INTO table1 (i,col1,col2) VALUES (2,NULL,'Second row'); INSERT INTO table1 (i,col1,col2) VALUES (3,'Third row','Third row');


4.

You will get a message, shown below, saying that SQL Server is not allowed to insert a NULL value into col1 because it is defined as NOT NULL.

5.

Enter and execute the following SELECT statement to examine whether the records were successfully inserted.

USE tempdb; GO SELECT i,col1,col2 FROM table1;


6.

As you can see, the second row was not inserted, but the first and the third rows were successfully inserted. When SQL Server uses auto-commit transactions, every statement is a transaction of its own. If one statement produces an error, its transaction is rolled back automatically. If the statement executes successfully without any errors, the transaction is automatically committed. Therefore, statements 1 and 3 were committed and statement 2, which produced an error, was rolled back. Note that this behavior occurred even though the three statements were submitted together as a batch. Batching doesn't define whether or not the statements within the batch are processed as a single transaction.

Explicit Transactions

In explicit transaction, the developer defines where a transaction starts and when it has to be committed or rolled back. This is done by using the T-SQL statements BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION. An explicit transaction is independent from a batch. It can span more than one batch, or there can be more than one explicit transaction in one batch.

Note

You can use the abbreviation TRAN in place of the TRANSACTION keyword.


Defining Explicit Transactions

1.

Start SQL Server Management Studio and open a New Query window.

2.

Enter and execute the following statement to truncate table1. The code for this example is included in the sample files as DefineExplicitTransactions.sql.

USE tempdb; GO TRUNCATE TABLE table1;


3.

Now insert the same three rows as before into table1. This time group the statements in an explicit transaction because you want to either have all the records inserted into the table or none inserted at all. Enter the following statements in the query window and execute all the statements at once.

USE tempdb; GO BEGIN TRAN INSERT INTO table1 (i,col1,col2) VALUES (1,'First row','First row'); INSERT INTO table1 (i,col1,col2) VALUES (2,NULL,'Second row'); INSERT INTO table1 (i,col1,col2) VALUES (3,'Third row','Third row'); COMMIT TRAN;


4.

You will get the same message back as before stating that SQL Server is not allowed to insert a NULL value into col1 because it is defined as NOT NULL.

5.

Enter and execute a SELECT statement to examine whether records were inserted.

USE tempdb; GO SELECT i,col1,col2 FROM table1;


6.

You see that the result is the same as with auto-commit mode. Two of the rows are inserted and the one violating the NULL constraint is not. What happened here? As stated earlier, it is the developer's responsibility not only to define the length of a transaction, but also to determine whether it should be rolled back. So you need to add an error handler to your transaction. Without an error handler, SQL Server will simply process the next statement after the error because the batch is not aborted. In the previous batch, SQL Server simply processes each INSERT statement and afterwards processes the COMMIT TRAN statement. Therefore, you have the same result as with auto-commit mode.

7.

To add an error handler, you can use the new TRY and CATCH block feature of SQL Server 2005 T-SQL. Truncate the table again and execute the transaction with an error handler as shown below.

--truncate TRUNCATE TABLE table1 --transaction with error handler BEGIN TRY     BEGIN TRAN     INSERT INTO table1 (i,col1,col2)     VALUES (1,'First row','First row');     INSERT INTO table1 (i,col1,col2)     VALUES (2,NULL,'Second row');     INSERT INTO table1 (i,col1,col2)     VALUES (3,'Third row','Third row');     COMMIT TRAN; END TRY BEGIN CATCH     ROLLBACK TRAN END CATCH;


In this case you don't get any error messages back, since the error was trapped by the CATCH block.

8.

Enter and execute the following SELECT statement to examine whether the transaction was rolled back.

USE tempdb; GO SELECT i,col1,col2 FROM table1;


No records are returned. As you can see, then, the whole transaction was rolled back. When the violation occurred in the second INSERT statement, SQL Server jumped to the CATCH block and rolled back the transaction.

9.

The only problem that remains with this code is that you don't get any message telling you that an error occurred. This can be managed in the CATCH block where you can use special functions to retrieve the error, and you can also use the RAISERROR function to raise a custom error. Alter the CATCH block as shown below.

BEGIN CATCH     SELECT  ERROR_NUMBER() AS ErrorNumber,             ERROR_SEVERITY() AS ErrorSeverity,             ERROR_STATE() as ErrorState,             ERROR_PROCEDURE() as ErrorProcedure,             ERROR_LINE() as ErrorLine,             ERROR_MESSAGE() as ErrorMessage;     RAISERROR('Error in Transaction!',14,1)     ROLLBACK TRAN END CATCH;


10.

Execute the whole transaction from step 9 again. Now you get a record back with all the information for the error and a custom error message stating that an error occurred. Of course, it is also possible to include the actual error message in the RAISERROR statement. In that case, the CATCH block would look like this:

BEGIN CATCH     DECLARE @er nvarchar(max)     SET @er = 'Error: '+ ERROR_MESSAGE();     RAISERROR(@er,14,1);     ROLLBACK TRAN END CATCH;


Implicit Transactions

The third mode is called implicit transaction mode because, in this mode, SQL Server starts a transaction if no transaction exists but doesn't perform a COMMIT or ROLLBACK statement automatically the way it does in auto-commit mode. The transaction has to be ended explicitly. The following statements start a transaction implicitly when no transaction exists:

ALTER TABLE

GRANT

FETCH

DELETE

CREATE

REVOKE

INSERT

SELECT

DROP

OPEN

UPDATE

TRUNCATE TABLE


Using Implicit Transactions

1.

Start SQL Server Management Studio and open a New Query window.

2.

Enter and execute the following statement to set the connection to the implicit transactions mode. The code for this example is included in the sample files as UsingImplicit Transactions.sql.

SET IMPLICIT_TRANSACTIONS ON; GO


3.

Execute the following code to create a table to check if a transaction has started.

CREATE TABLE T1     (i int PRIMARY KEY);


4.

To test if a transaction is open, @@TRANCOUNT can be used; execute the SELECT statement shown below.

SELECT @@TRANCOUNT AS [Transaction Count];


5.

The result is 1, which means that the connection has an open transaction. 0 would mean that no transaction is open at the moment, and a number higher than 1 would mean that there are nested transactions (which will be explained later).

6.

Now insert a row into the table and check @@TRANCOUNT again by executing the following statements.

INSERT INTO T1 VALUES(5); GO SELECT @@TRANCOUNT AS [Transaction Count];


The value of @@TRANCOUNT is still 1. SQL Server didn't start a new transaction because there was an open transaction already.

7.

Now roll back the transaction and check @@TRANCOUNT again by executing the following code. You will see that @@TRANCOUNT is 0 after the ROLLBACK TRAN statement.

ROLLBACK TRAN GO SELECT @@TRANCOUNT AS [Transaction Count];


8.

Try to SELECT table T1.

SELECT * FROM T1;


9.

You will get an error message because the table doesn't exist anymore. The implicit transaction started with the CREATE TABLE statement and the ROLLBACK TRAN statement cancelled the work done since the first statement.

10.

Turn implicit transactions off by executing the following code:

SET IMPLICIT_TRANSACTIONS OFF;


Caution

Be careful when using implicit transactions. Don't forget to commit or roll back your work. Because there is no explicit BEGIN TRANSACTION statement, these steps are easy to forget, leading to long-running transactions, unwanted rollbacks when the connection is closed, and blocking problems with other connections.


Nesting Transactions

Explicit transactions can be nested, which means that it is possible to start explicit transactions within other explicit transactions. One of the main reasons that this is supported is to allow transactions within stored procedures regardless of whether the procedure is itself called from within a transaction. But how are nested transactions handled in SQL Server? Let us explore nested transactions with two simple examples.

Exploring Nesting Transactions

1.

Start SQL Server Management Studio and open a New Query window.

2.

Use @@TRANCOUNT to figure out how SQL Server is handling nested transactions. Enter and execute the following batch. The code for this example is included in the sample files as NestingTransactions.sql.

PRINT 'Trancount before transaction: ' + CAST(@@trancount as char(1)) BEGIN TRAN     PRINT 'After first BEGIN TRAN: ' + CAST(@@trancount as char(1))     BEGIN TRAN         PRINT 'After second BEGIN TRAN: ' + CAST(@@trancount as char(1))     COMMIT TRAN     PRINT 'After first COMMIT TRAN: ' + CAST(@@trancount as char(1)) COMMIT TRAN PRINT 'After second COMMIT TRAN: ' + CAST(@@trancount as char(1))


3.

In the result, you can see that every BEGIN TRAN statement increments @@TRANCOUNT by 1 and every COMMIT TRAN statement decrements the count by 1. As you have seen before, a @@TRANCOUNT of 0 means that there are no open transactions. Therefore, the transaction ends when @@TRANCOUNT decrements from 1 to 0, which happens when the outermost transaction is committed. Thus, every inner transaction needs to be committed. The outermost transaction determines whether the inner transactions are fully committed because the transaction starts with the first BEGIN TRAN and commits only with the last COMMIT TRAN. If this outermost transaction is not committed, the nested transactions within will not be committed either.

4.

Type and execute the following batch to examine what happens when a transaction is rolled back.

USE AdventureWorks BEGIN TRAN     PRINT 'After 1st BEGIN TRAN: ' + CAST(@@trancount as char(1))     BEGIN TRAN         PRINT 'After 2nd BEGIN TRAN: ' + CAST(@@trancount as char(1))             BEGIN TRAN             PRINT 'After 3rd BEGIN TRAN: ' + CAST(@@trancount as char(1))             UPDATE Person.Contact             SET EmailAddress = 'test@test.at'             WHERE ContactID = 20             COMMIT TRAN         PRINT 'After first COMMIT TRAN: ' + CAST(@@trancount as char(1)) ROLLBACK TRAN PRINT 'After ROLLBACK TRAN: ' + CAST(@@trancount as char(1)) SELECT EmailAddress FROM Person.Contact WHERE ContactID = 20;


5.

In this example, the e-mail address of a contact is updated in a nested transaction that is immediately committed. Then a ROLLBACK TRAN statement is issued. The ROLLBACK TRAN decrements @@TRANCOUNT to 0 and rolls back the whole transaction with all nested transactions regardless of whether they were already committed. Therefore, the update made in the nested transaction is rolled back and no data is changed.

Always keep in mind that with nested transactions only the outermost transaction determines whether the inner transactions will be committed. Each COMMIT TRAN statement always applies to the last executed BEGIN TRAN. Therefore, a COMMIT TRAN has to be called for every BEGIN TRAN executed to commit a transaction. The ROLLBACK TRAN statement always belongs to the outermost transaction and therefore always rolls back the whole transaction regardless of how many nested transactions are open. Because of this, managing nested transactions can be tricky. As stated at the beginning of this section, nested transactions mostly happen in nested stored procedures when every procedure starts a transaction on its own. Nesting transactions can be avoided by deciding if a transaction needs to be started or not by checking @@TRANCOUNT at the start of the procedure. If @@TRANCOUNT is higher than 0, it is not necessary to start a transaction because the procedure is already in a transaction and the calling instance can roll back the transaction if an error occurs.




Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net