SQL Transactions
A transaction in SQL represents a sequence of one or more SQL statements that are executed as a single, atomic unit of work. The term “atomic” here implies that the transaction is treated as a single, indivisible operation. Either all the changes made by the transaction are committed to the database, or none of them are.
In the below PDF we discuss about SQL Transaction in detail in simple language, Hope this will help in better understanding.
Properties of Transaction:
- Atomicity: Atomicity ensures that a transaction is treated as a single, indivisible unit. If any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged. This property guarantees consistency in the database state.
- Consistency: Consistency ensures that the database remains in a valid state before and after the transaction. If the transaction violates any integrity constraints or rules, it is rolled back to maintain the overall consistency of the data.
- Isolation: Isolation ensures that the intermediate state of a transaction is not visible to other transactions until it is committed. This property prevents interference between concurrent transactions and maintains data integrity.
- Durability: Durability guarantees that once a transaction is committed, its changes are permanent and survive any subsequent failures. The committed changes are stored in a way that can withstand system crashes or power outages.
SQL transaction commands:
Here are the main SQL transaction commands:
- BEGIN TRANSACTION: The BEGIN TRANSACTION command marks the beginning of a transaction. It indicates the start of a sequence of SQL statements that should be treated as a single unit.
- COMMIT: The COMMIT command is used to save all the changes made during the current transaction to the database. Once a COMMIT statement is executed, the changes become permanent.
- ROLLBACK: The ROLLBACK command is used to undo all changes made during the current transaction. It restores the database to its state before the transaction began.
- SAVEPOINT: The SAVEPOINT command is used to set a point within a transaction to which you can later roll back. It allows you to create intermediate points in a transaction for more granular control over the rollback process.
- ROLLBACK TO SAVEPOINT: The ROLLBACK TO SAVEPOINT command is used to undo all changes made after a specific SAVEPOINT within the current transaction.
- SET TRANSACTION: The SET TRANSACTION command is used to control characteristics of a transaction, such as isolation level and access mode.
Related Question
A transaction in SQL is a sequence of one or more SQL statements executed as a single unit of work. It ensures the consistency and integrity of a database by allowing a series of operations to be completed successfully or rolled back as a whole.
The beginning of a transaction is indicated by the SQL command BEGIN TRANSACTION or simply BEGIN.
The COMMIT command is used to save the changes made during the transaction to the database.
The ROLLBACK command is used to undo the changes made during the current transaction. It restores the database to its state before the transaction began.
A primary key is a unique identifier for a record in a table. It ensures that each row in a table can be uniquely identified and helps establish relationships between tables. A primary key column cannot contain null values, and there can be only one primary key in a table.
Relevant
What is SQL Injection? SQL
Types of SQL Keys Structured
Joins In SQL With Examples
SQL INSERT Statement The SQL
ORDER BY in SQL Structured
SQL Clauses In SQL (Structured
SQL SELECT Statement The SQL
