TRANSACTION: The transaction helps to group the set of task, either all transaction are success or all revert back.
In this article, I have created a table with two columns ID and NAME also primary key apply on ID.
- Begin transaction is a starting point of transaction.
- In this transaction, we insert some value into the table. Both insert query in one logical unit.
- Rollback transaction is a reverted back transaction if either failure or mistake occurs.
- This transaction will revert back due to a violation of the primary key.
- Commit transaction to save changes to the database. In this transaction successfully inserted values in the table.
Nested Transaction means transaction inside a transaction.
- Any rollback applies on an inner transaction it rollbacks everything. If we executed nested transaction it rollbacks everything.
CHECKPOINT: The checkpoint is a logical completion point where someone wants to rollback it.
- A checkpoint is a process that writes current in-memory dirty pages (modified pages) and transaction log records to a physical disk.
- SQL Server checkpoints are used to reduce the time required for recovery in the event of system failure.
- In checkpoint, If an error occurs that statement has terminated and others will be executed.
- Test3 and Test4 have been terminated because of a violation of the primary key.
- Test5 and Test6 successfully inserted in the table.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -
