Types of Transactions
If you are new to SQL Server development, you will probably now be wondering how in previous chapters, and in any code you were writing in T-SQL or with an object model like ADO.NET, you were able to get away with not writing transactions. Well, the truth is that you have been writing transactions, or at least that’s how SQL Server sees it. SQL Server considers everything executed against it a transaction. Any code you send to the server that is not part of a user-defined transaction is automatically placed into an autocommit transaction. In other words, SQL Server automatically handles the transaction management for you. If your code executes properly, SQL Server automatically commits any data changed or inserted; if not, it rolls back and you get an error.
Three types of transaction are supported by SQL Server: explicit, implicit, and autocommit. Let’s look at the autocommit first because it is the easiest to work with. You don’t have to do any transaction management.
Autocommit Transactions
The autocommit transaction is the default transaction mode of SQL Server. No matter what statement is issued to SQL Server, changes are committed if the transaction completes properly or rolled back if it encounters an error. The autocommit transaction is also the default of any statement transmitted to the server from ADO.NET, ADO, OLE DB, ODBC, or the old legacy DB-Library.
However, in autocommit mode, SQL Server takes the batch of SQL statements and processes each one as a separate transaction. For example, in the following code SQL Server encounters a syntax error and is unable to formulate an execution plan for the entire batch, so the entire batch is aborted and rolled back:
CREATE TABLE TestTable (Coll INT PRIMARY KEY, Col2 VARCHAR (10) INSERT TestTable VALUES (1, 'apples') INSERT TestTable BALUES (2, 'bananas') SELECT * FROM TestTable
The query at the end fails and no result is returned because the optimizer was unable to compile the batch on the second INSERT because of the syntax problem. In the next example, the first statement is committed even though there is an error in the batch. It does this because in autocommit mode every statement is considered a separate transaction:
CREATE TABLE TestTable (Coll INT PRIMARY KEY, Col2 VARCHAR (10) INSERT TestTable VALUES (1, 'apples') INSERT TestTable VALUES (1, 'bananas') SELECT * From TestTable
Autocommit mode compiles the batch because the error in the primary key (1) is only trapped at the constraint check after the query has been compiled and optimized (this is known as delayed name resolution). There is no syntax error, and if you run the preceding code in Management Studio, it will tell you that the code checks out. If we run this code, perform the SELECT, and then examine the table, we see in the second batch that the first INSERT is committed but not the second.
Running this code in Management Studio returns the following error, which indicates that one row was committed:
(1 row(s) affected) Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK test_table 5EBF139D'. Cannot insert duplicate key in object 'test_table'. The statement has been terminated. (1 row(s) affected)
Now this behavior may seem contrary to what we have just learned about transactions and the atomicity rule. But if you examine the results, is it not clear that the database is still in a durable state, even after the error? It is; this is not the issue. What if the first statement and the second statement are both required by your application or code? Consider the following code for a voice message system:
UPDATE Extensions SET PrimaryExt='555–1212' INSERT NewExtension (VoiceMailExt) VALUES ('5551212') If the phone extension numbers are required to be in 3+4 notation separated by a dash and we install a check constraint on the columns to catch the mask error, then in autocommit mode the NewExtension will never receive a call that is not answered on the primary extension. The problem with this is that only a portion of the query code will result in data being committed to the database when the entire batch should have done so. This would clearly be an error by the voice mail system because it would risk leaving an extension without message-taking capability. This is a simple example, but it illustrates the point that in autocommit mode you might have only part of your data committed to the table, leaving the application, as opposed to the data, in an inconsistent state. The way to ensure that all the values or none get installed is with an explicit transaction.
Explicit Transactions
To use an explicit transaction, you (and not SQL Server) explicitly define the beginning and end of the transaction. In T-SQL or with DB-Library, you use the following statements:
-
BEGIN TRANSACTION This statement marks the starting point of your transaction.
Note SQL Server supports the abbreviated TRAN in place of TRANSACTION.
-
COMMIT TRANSACTION; COMMIT WORK These statements are used to signal to the transaction manager that the transaction completed successfully and the data insertions or modifications should be committed to the database.
-
ROLLBACK TRANSACTION; ROLLBACK WORK These statements signal to the transaction manager that the transaction failed and the work should be rolled back; completely undone, get ready to start over.
Using our simple example demonstrated earlier, we ensure that either the entire batch is “kosher” or it all goes into the trash can. The following code rolls back everything between BEGIN TRANSACTION and ROLLBACK TRANSACTION:
BEGIN TRANSACTION INSERT Extensions (PrimaryExt) VALUES ('555–1212') INSERT Extensions (VoiceMailExt) VALUES ('5551212') ROLLBACK TRANSACTION | Note | SQL Server returns to autocommit mode automatically after an explicit or rimplicit transaction completes. |
Implicit Transactions
A third mode for transactions is called implicit mode. To operate in implicit mode, you do not need to delineate a transaction with the BEGIN TRANSACTION statement, but you need to specifically place the session into implicit mode with the SET IMPLICIT_TRANSACTIONS ON statement. But you need to signal the end of each transaction with the ROLLBACK TRANSACTION or COMMIT TRANSACTION statement.
Using the implicit mode allows you to issue a long batch of statements, enclosing collections of statements in implicit transactions. When you are done in the implicit mode, you need to issue the SET IMPLICIT_TRANSACTIONS OFF statement.
SQL Server also automatically starts a transaction every time it encounters one of the statements in Table 17–5 while in implicit mode.
| ALTER TABLE | REVOKE |
| CREATE | OPEN |
| DELETE | INSERT |
| DROP | TRUNCATE |
| FETCH | UPDATE |
| GRANT | SELECT |
Transactions and SQL Server Development
The database APIs, such as ODBC, ADO, ADO.NET and OLE DB, each contain functions or methods that allow you to manage transactions from the client application. Consult each API or object model for the peculiarities of the function or method call. Naturally, you cannot mix and match the API calls; you cannot begin a transaction in ODBC and end it with a call to the ADO API
The ACID rules no doubt extend to the client connection and the client. If a client is in the middle of an extensive transaction and then gets squashed by a steamroller, the transaction manager detects the failure and rolls back the transaction. Thus in mission-critical applications, with unreliable network connections, it makes sense not to use the default autocommit transaction mode.
An extensive update or insert to, say, a bank account, over the Internet or even filling in a form, requires you to prevent a situation in which only part of the data is committed to the database. Often it happens that as a browser fills in a form on the Internet, the user loses the connection and has to start all over. Then when the user tries to reissue the user ID that was available before, he or she gets the message that the user ID is taken-case of the partly committed form.
EAN: 2147483647
Pages: 239