"...I'm unclear about when to use ROLLBACK..."

You use ROLLBACK whenever you want to "undo" the transaction.  Most often
this is because an error has occurred, but there may be other reasons; your
logic may detect a situation where you want to back out all the work that's
been done because some application condition has been detected 'downstream'.

A transaction which is uncommitted means that though the updates were
written to the transaction log, they were never applied to the database.
The only record of the failed updates is in the log, and when the log gets
purged, the updates will get purged as well.  The locks associated with the
abandoned transaction however, will only be released when the connection is
closed (or explicitly released).  This is one of the reasons it's not a good
idea to hold a connection for "too" long, or to have "hanging" transactions
in your code (or for that matter having a transaction open for "too" long).
These "hanging" locks could adversely impact the concurrency of other
connections or processes.

Your question about the multiple updates is an interesting one, and one that
reveals a common misconception about the behavior of transactions in SQL
Server (I don't know what other products do in this situation).

Consider your code:

	BEGIN TRANSACTION
	  INSERT . . . Something into a table
	  UPDATE. . . Another table
	  UPDATE. . . Some other table
	COMMIT TRANSACTION

Now, some people might expect that if an error occurred and the INSERT
failed (because of a foreign key constraint, say) that the UPDATEs would be
aborted as well.  They won't, and because there is an unconditional COMMIT
at the end of the batch, they (although not the INSERT), if successful will
get committed.

Which may not be what you want.

To insure that the updates only get executed (and subsequently committed),
you must test for errors appropriately, as, for example:

	BEGIN TRANSACTION
	  INSERT . . . Something into a table
	    If (@@ERROR<>0) GOTO An_error
	  UPDATE. . . Another table
	    If (@@ERROR<>0) GOTO An_error
	  UPDATE. . . Some other table
	    If (@@ERROR<>0) GOTO An_error
	COMMIT TRANSACTION
	RETURN 0

An_error:
	ROLLBACK TRANSACTION
	RETURN 1

which is pretty ugly.  You can also use SET_XACT_ABORT ON to cause the
entire batch to abort if any error occurs, but this can be a bit of a
sledgehammer approach, as you relinquish control over what happens, and your
transaction (and more importantly its locks) can still be left hanging.

Error handling is ugly in every language...
--
Jeff Mason              Custom Apps, Inc.
Jeff@c...


-----Original Message-----
From: Larry Guayante [mailto:larry@k...]
Sent: Sunday, November 24, 2002 12:05 PM
To: sql language
Subject: [sql_language] RE: transaction


Thanks for that explanation Jeff. I'm unclear about when to use
ROLLBACK--If I don't use ROLLBACK and an error occurs within the
transaction, will the transaction remain uncommitted forever. For
example:

BEGIN TRAN
  INSERT . . . Something into a table

  UPDATE. . . Another table

  UPDATE. . . Some other table
COMMIT TRAN

If the INSERT didn't take place and an error occurred, would the
transaction be left hanging without ROLLBACK. I've experimented in Query
Analyzer; if I don't use ROLLBACK, I get a message about "uncommitted"
transactions" when I try to close Query Analyzer. I can't find a better
explanation in BOL.

Larry Guayante
Knowledge Outlook
Performance, Process And Learning Solutions
(xxx) xxx-xxxx


-----Original Message-----
From: Jeff Mason [mailto:je.mason@a...]
Sent: Sunday, November 24, 2002 5:33 AM
To: sql language
Subject: [sql_language] RE: transaction


A transaction defines a single unit of work. This can include one or
more SQL statements.  In a transaction, all statements are applied, or
none at all.

At the beginning of a transaction, the server writes any changes the SQL
statements make to a transaction log file.  If any statement fails, then
the entire transaction is "rolled back" and none of the data
modifications are made to the database.  If the transaction succeeds,
the transaction is "committed" and the modifications are made to the
database.

In theoretical terms, the acronym "ACID" is used to define the
characteristics of a transaction. "ACID" stands for Atomicity,
Consistency, Isolation, and Durability. Atomicity means transactions are
all or nothing; either the transaction fully succeeds (commits) or it
fully aborts (rollback).  Consistency means that a transaction won't
allow the database to enter a logically inconsistent state by violating
any constraint rules even if the system fails. Isolation means that
transactions are not dependent on other transactions being run
concurrently. Durability means that errors within the transaction will
cause partial updates or modifications to be undone.

In SQL Server (TSQL), the syntax is pretty simple:  you begin a
transaction with BEGIN TRANSACTION, you commit it with COMMIT
TRANSACTION, and you roll it back (undo it) with ROLLBACK TRANSACTION.
You can give transactions a name to 'improve readability', but this is
all that naming really does. You give the name when you begin the
transaction, and refer to that name when you commit it or roll it back.
Transactions can be nested, one within another, but as of SQL Server
2000, only the outermost transaction is actually honored on a COMMIT,
although any are honored on a ROLLBACK.

TSQL has a related command (SET TRANSACTION ISOLATION LEVEL) to set the
'level' of the transaction; this really is mostly to do with database
locking (which goes hand in hand with transactions), and defines the
level of sensitivity your code has to the changes made by others and
consequently when locks are acquired and released.  This affects overall
database concurrency.  What this all means is that while *your* updates
adhere to the ACID principle, you nonetheless can "see" the updates made
by other transactions whether they have been committed or not, depending
on the isolation level you set.  See BOL.

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...


-----Original Message-----
From: Mike [mailto:mikeos1000@h...]
Sent: Sunday, November 24, 2002 2:34 AM
To: sql language
Subject: [sql_language] transaction


Hi,

Would someone be kind enough to explain what transaction are, and maybe
give us an example? It's the syntax I'm interested in.

Cheers
---
Change your mail options at http://p2p.wrox.com/manager.asp or to
unsubscribe send a blank email to