What Is Transaction And Type Of Transaction In SQL?

A transaction in SQL (structured query language) is a larger unit of database processing that contains one or more database access operations like insertion ,deletion, retrieval and modification operations.

These transaction are required to fulfil certain characteristics and they are :

Atomicity: The transaction is either performed entirely or not performed at all.
Isolation: The transaction should not be interfered by any other transaction executing concurrently.
Durability: The changes made by the transaction should be permanently committed in the database.
Consistency preservation: If the database was consistent before the execution of the transaction .It should remain consistent after the complete execution of that transaction.

There are two types of transactions: explicit and implicit

Explicit are those that need to be specified like : commit and roll-back

Commit transaction signals that the transaction was successfully executed and the changes/ updates (if any) made by the transaction have been committed to the database and these changes cannot be undone.

Roll-back signals that the transaction was not successfully executed , and the changes/updates made by the transaction have to be undone.

Implicit transactions are those that mark beginning and end of the transaction, each statement like update, delete , insert run within these implicit transactions.,However, you can explicitly specify the beginning and ending of the transaction by "begin transaction" and "end transaction" statements respectively. All the statements within these two boundaries will be marked as one transaction.

Transaction means being able to execute multiple statements as a single unit, so that either all the statements are executed or none are executed.

Transactions are required in applications that collect or manipulate data in order to ensure data integrity. Data integrity is enforced when no two people are able to update/change a record at the same time. Also, modified data cannot be changed by anyone until it is saved (committed) to database.

SQL Server writes the changes of a transaction to a log file first. If the transaction is successful the changes are COMMITTED and tables are updated. If for some reason the transaction is unsuccessful the transaction undergoes a ROLLBACK and the previous data is restored (before the changes took place).

There are two types of transactions in SQL server :

1) Implicit transactions
2) Explicit transactions.

IMPLICIT TRANSACTIONS - by default all INSERT, DELETE, UPDATE statements run as implicit transactions. For example if an update query is run, and a primary key constraint is violated, a rollback will occur and changes will not occur.

EXPLICIT TRANSACTIONS- These are written by the programmer. In order to execute a group of statements as a transaction we write the statements within a BEGIN TRANSACTION and COMMIT TRANSACTION block. If an error occurs we can use ROLLBACK, so that previous data state is restored.

Transaction is a unit of sql statement .There are two type of Transaction in sql
1.explicity
2.implicity

SQL wires transactions as distinct by the SQL customary. A transaction is a series of SQL statements that Oracle treats as a solitary element of effort. As quickly as you attach to the database with SQL plus, a transaction begins. At one time the transaction begins, each SQL DML (Data Manipulation Language) declaration you subject consequently becomes a fraction of this transaction. A transaction ends when you detach from the database, or when you use a COMMIT or ROLLBACK command.

COMMIT makes permanent any database changes you complete throughout the present transaction. In anticipation of you entrust your changes, other users cannot view them. The ROLLBACK command ends the existing transaction and undo any changes prepared since the transaction began.

After the present transaction has broken with a COMMIT or ROLLBACK, the first executable SQL statement that you consequently subject will robotically begin another transaction. The same system is for designating the end of a transaction (SAVEPOINT/ COMMIT/ROLLBACK) and the beginning of it.

This save point can be second hand in combination with the rule ROLLBACK TO undo parts of a transaction.

Collection of data to perform single logical unit of work is called transcation