Database Reference
In-Depth Information
Also, you cannot use
spdboption
to set database options or use any system procedures that modify
the master database inside explicit or implicit transactions.
Local Transactions in SQL Server 2012
All database engines are supposed to provide built-in support for transactions. Transactions that are
restricted to only a single resource or database are known as
local transactions.
Local transactions can be
in one of the following four transaction modes:
•
Autocommit transactions
: Autocommit mode is the default transaction
management mode of SQL Server. Every T-SQL statement is committed or rolled
back when it is completed. If a statement completes successfully, it is committed;
if it encounters any errors, it is bound to roll back. A SQL Server connection
operates in autocommit mode whenever this default mode has not been
overridden by any type transactions.
•
Explicit transactions
: Explicit transactions are those in which you explicitly control
when the transaction begins and when it ends. Prior to SQL Server 2000, explicit
transactions were also called
user-defined
or
user-specified
transactions.
T-SQL scripts for this mode use the
BEGIN TRANSACTION
,
COMMIT TRANSACTION
, and
ROLLBACK TRANSACTION
statements. Explicit transaction mode lasts only for the
duration of the transaction. When the transaction ends, the connection returns to
the transaction mode it was in before the explicit transaction was started.
•
Implicit transactions
: When you connect to a database using SQL Server
Management Studio and execute a DML query, the changes are automatically
saved. This occurs because, by default, the connection is in autocommit
transaction mode. If you want no changes to be committed unless you explicitly
indicate so, you need to set the connection to implicit transaction mode.
You can set the database connection to implicit transaction mode by using
SET
IMPLICIT TRANSACTIONS ON|OFF
.
After implicit transaction mode has been set to
ON
for a connection, SQL Server
automatically starts a transaction when it first executes any of the following
statements:
ALTER TABLE
,
CREATE
,
DELETE
,
DROP
,
FETCH
,
GRANT
,
INSERT
,
OPEN
,
REVOKE
,
SELECT
,
TRUNCATE TABLE
, and
UPDATE
.
The transaction remains in effect until a
COMMIT
or
ROLLBACK
statement has been
explicitly issued. This means that when, say, an
UPDATE
statement is issued on a
particular record in a database, SQL Server will maintain a lock on the data scoped
for data modification until either a
COMMIT
or
ROLLBACK
is issued. In case neither of
these commands is issued, the transaction will be automatically rolled back when
the user disconnects. This is why it is not a best practice to use implicit transaction
mode on a highly concurrent database.
•
Batch-scoped transactions
: A connection can be in batch-scoped transaction
mode, if the transaction running in it is Multiple Active Result Sets (MARS)
enabled. Basically, MARS has an associated batch execution environment,
because it allows ADO.NET to take advantage of SQL Server 2012's capability of
having multiple active commands on a single connection object.
