Transactions in SQL Server

Transactions in SQL Server

Transactions in SQL server are a single or consecutive set of processes completed in a rational definite flow that can be performed manually or by creating an automatic database programming. Every transaction in SQL starts as a flow from a particular job and should complete with another working job. If any or all of the jobs are incomplete, the transaction status is said to be ‘fail’. A Transaction is said to be ‘complete’ or ‘success’ if and only if all the jobs are completed successfully.

The transaction, once committed, cannot be a rollback; it can be a rollback only if the transaction is not committed.

Syntax:

SET autocommit = 0;

Properties of the Transaction

Following are the important properties of the transactions; every transaction must follow these properties.

1. Atomicity

   A transaction must be atomic; mean data manipulation should be completed for a certain logical unit. This property ensures that data changes taken place completely; otherwise, roll back the transaction.

2. Consistency

   Once the Transaction finishes, all the available records will be consistent throughout the transaction. This property ensures that the database property switched state after a successful commit or not.

3. Isolation

   Isolation refers to data changes at a certain logical unit that should not affect on another unit. It allows a transaction to execute independently.

4. Durability

   Changes made during transactions should be permanent in the system. In case of a system error, this property also ensures that data changes take place or not.

   The above-given property of the transaction is also known as ACID property.

Steps of Transaction

Below are the steps:

1. Begin

   A transaction may occur in multiple SQL executions, but all SQL should run at once. If any of the transactions fails, then the whole transaction would be reverted. The statement for starting the transaction is “START TRANSACTION”,, .begins the acronym for the START TRANSACTION.

   Syntax:

   START TRANSACTION;

2. Commit

   Commits permanently reflect the changes to the database. The statement for starting the transaction is “COMMIT”.

   Syntax:

   COMMIT;

3. Rollback

   Rollback is used to revert the changes, i.e. record will not be changed, It would be in the previous state. The statement for starting the transaction is “ROLLBACK”.

Syntax:

ROLLBACK;

4. Savepoint

   SAVEPOINT is also a transaction statement. This statement used to create a store point in the system so that the ROLLBACK operation can achieve the state of the savepoint.

5. Release Savepoint

   RELEASE SAVEPOINT is a statement to release the savepoint& memory consumed by the system in creating a save point.

   Syntax:

   RELEASE SAVEPOINT SP

Notes –

SP was the name of the savepoint when this savepoint was created before the transaction start.

6. Set Transaction

   The SET TRANSACTION command is used to specify the transaction attribute, such as the given transaction is a read-only or read-write session.

Syntax:

SET TRANSACTION [ READ-WRITE | READ ONLY ];

The transaction is used to perform complex changes in the database. Its mainly used in banking-related information changes into a relational database.

The transaction is supported by the MYSQL engine InnoDB. By default, auto-commit remains enabled; that’s why whenever any SQL executes after execution, commits automatically take place.

Benefits of using Transaction in SQL

• Using Transaction improves the performance; when inserting 1000 records using transactions, in that case, time taken would be lesser than normal insertion. As in a normal transaction, every time COMMIT would take place after each query execution & it would increase the time of execution every time while in a transaction, no need to execute COMMIT statement after each SQL query. COMMIT at the end would reflect all the changes to the database permanently at once. Also, if using a transaction, then reverting the changes would be much easier than the normal transaction. ROLLBACK will revert all the changes at once & keep the system in the previous state.
• The transaction ensures data integrity in the relational database. Most of the database uses multiple tables to maintain the data, and while making updates, there may have been changes in the multiple tables at that time; if any of the SQL queries fail, then the transaction would keep the data unchanged.