What's a Transaction?
- A transaction is a unit of work that is performed against a database.
- A transaction ensures that either all of the commands succeed, or none of them.
- If one of the commands in the transaction fails all of the commands fail and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
Begin Transaction in SQL
The Begin Transaction is used with Explicit transactions as a kind of transaction mode.
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
BEGIN TRANSACTION increments @@TRANCOUNT by 1.BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. - If errors are encountered, all data modifications made after the
BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.
- Each transaction lasts until either it completes without errors and
COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.
BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.- Although
BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.
Explicit Transaction Example
This example will commit the query after the begin transaction.
BEGIN TRANSACTION;
DELETE FROM EMPLOYEE
WHERE EPMID= 10;
COMMIT;
Rollback Transaction Example
This example will rollback the query after the begin transaction.
BEGIN TRANSACTION;
INSERT INTO Table1 VALUES(1);
INSERT INTO Table1 VALUES(2);
ROLLBACK;
See also