Databases Reference
In-Depth Information
Some of these statements might not be familiar to you. The FETCH and
OPEN statements are used with server-side
cursors,
which provide a way of pro-
cessing individual rows within a result set. GRANT and REVOKE are used with
security management to allow and remove permissions. Cursor use and security
management are beyond the scope of this chapter.
The biggest drawback of using implicit transactions is that you don't control
the start of the transaction, but you must identify the end of the transaction.
This can lead to very long running transactions which, in turn, can result in
access problems and impair performance.
The main reason for enabling implicit transactions is that they are required
by some legacy applications. You should not develop new applications to use
implicit transactions. The ODBC API even includes commands to place the data-
base in implicit transaction mode. The OLE DB API can be used with implicit
transactions, but does not include a way of putting the database into implicit
transaction mode. It can, however, issue commands that disable implicit trans-
actions. The ADO and ADO.NET APIs do not support implicit transactions.
Autocommit Transactions
With
autocommit transactions,
which is the default state for SQL Server and
many other DBMSs, each statement is treated as an individual transaction. If you
want a statement to run and, if no errors occur, commit—there's no need to do
anything special when you execute the statement.
When operating in an autocommit mode, explicit transactions are still sup-
ported. It's not uncommon to have some statements, like data retrieval queries,
run as stand-alone transactions in autocommit mode, while using explicit trans-
actions when multiple dependent statements are used.
Nested Transactions
Most DBMSs support nested transactions. A
nested transaction
occurs when
you explicitly start a new transaction while already operating within the scope
of a transaction. Nested transactions can be used with both explicit and implicit
transactions.
Here's an example. The line numbers are included for reference only. The
indents are included to make the nested transaction easier to see.
1 BEGIN TRAN
2 INSERT CUSTOMER VALUES ('1442', 'Get it here', '137','Memphis')
3
BEGIN TRAN
4
INSERT CUSTOMEREMPLOYEE VALUES
5
('1442', '3221', 'Thomas', 'Jane', 'Owner')
6
IF @@ERROR = 0
7
COMMIT TRAN
8
ELSE
