Transactions in SQL server is a process of grouping a tasks inside a single execution unit. Every SQL transaction starts with a specific task. Transactions are declared as over when the tasks are grouped successfully. The result of transactions is always binary and incomplete statements will result in transaction failure.
The statements given below are used to group two or more SQL statements under single transaction
- Begin Transaction
- Rollback Transaction
- Commit Transaction
All changes made during the transaction will be aborted if something goes wrong in the SQL group statements. The process of protecting the changes made in the database is called as rollback. The changes made in database are stored together when SQL statements present in the transaction are placed in order. The ordering of SQL statement in a transaction is called as commitment to the database.
The SQL code given below is an example for database transaction.
USE pubs
DECLARE @intEr INT
BEGIN TRAN
UPDATE Authors
SET Phone = ‘630-4500231’
WHERE au_id = ‘101’
SELECT @intEr = @@ERROR
IF (@intEr <> 0) GOTO PROBLEM
UPDATE Publishers
SET city = ‘New Jersey’, country = ‘U.S.A’
WHERE pub_id = ‘9999’
SELECT @intEr = @@ERROR
IF (@intEr <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@intEr <> 0) BEGIN
PRINT ‘Unexpected error occurred!’
ROLLBACK TRAN
END
The BEGIN and TRAN statements are used to treat the following instructions in the code as a single transaction. The UPDATE statements in the code are used to prevent errors during the updating process. The COMMIT TRAN statements are used to commit all the changes made in the database. After the commitment of changes made in database, the stored procedure of database transaction is completed. The problems occurred during the transaction process will be detected and executed by the PROBLEM label. The PROBLEM label will display the error message to the user.
Implicit Mode in SQL
The SQL server transactions can be either set ON or OFF by using the implicit mode in transactions. SET IMPLICIT_TRANSACTIONS to ON or OFF command is used to on implicit mode in SQL. A new transaction will be started after every implicit mode in database. A specific list of SQL statements like DELETE, UPDATE, SELECT and INSERT are used to execute implicit mode in SQL. The database transactions under implicit mode are automatically created by the SQL statements. Implicitly started transactions will continue until the commitment or rollback of the transaction. Disconnection without submitting SQL statement by the user will also result in the rollback of the transaction.
- Recommended: Final year Academic projects download
Autocommit Mode in SQL
In SQL, autocommit modes are classified as a separate transaction in SQL. SQL statements are capable of automatically committing the transactions in the database. Every SQL connection server uses autocommit by default for all implicit and explicit transactions.