- SQL transaction is a single unit of work applied to a database. (Relational database like DB2, Oracle, MySQL, Microsoft SQL Server and so on).
- It is a sequence of ordered operations performed on the database.
- SQL statements are used to execute tasks such as update data or get data from a database.
- The SQL statements of a transaction will either Commit or Rollback using a rollback statement.
- The transaction comprises of two results, they are either success or failure.
These transactions can be Oracle transaction, SQL server transaction, DB2 transaction, and so on.
SQL Transaction Properties
The following are the standard SQL transaction properties, abbreviated as ACID.
- Atomicity
- Consistency
- Isolation
- Durability
Atomicity :
Transactions in SQL are terminated at a point of error. In this way, It ensures the operations of work units were a success.
Consistency :
A successful commit transaction makes sure data is unchanged when a transaction begins and when it ends.
Isolation :
Transactions will operate independently and transparently.
Durability :
If the system fails, It ensures the result of committed Transaction continued across the application.
SQL Transaction Commands
Below mentioned are the commands used to control the transactions.
COMMIT
This command will save the changes.
Syntax
COMMIT;ROLLBACK
This command will rollback(undo or revert) the changes.
Syntax
ROLLBACK;SAVEPOINT
This command creates save points for the group of transactions that has to be ROLLBACK.
Syntax
SAVEPOINT SAVEPOINT_NAME;SET
Sets a name on a transaction.
Syntax
SET TRANSACTION [ READ ONLY | READ WRITE]; SQL Transaction Management
When an initial or first executable SQL statement runs across the server, a transaction in the database begins. An executable SQL statement is the one that triggers calls to an instance, DML, and DDL query statements. The database assigns the transaction to an available undo tablespace to track the rollback entries for the new transaction.
A transaction completes, if any of the following occurs:
- When a User triggers a COMMIT command or ROLLBACK command without a SAVEPOINT clause.
- A user runs a DDL (Data Definition Language) statement such as CREATE, DROP, RENAME, or ALTER.
- The current transaction is committed if a user loses the database connection.
- The current transaction rolls back on an abnormal termination of the user process.
Also, note that transactions were not used on a SELECT statement or SELECT SQL script generally.
Typically transactions were effectively used in the stored procedure.
It is best practice to include try and catch block to allow performing a Rollback Transaction statement in case some error occurs in the stored procedure.
Commit Transactions
Committing a transaction means making permanent changes within the transaction by SQL statements.
The following occurs before a transaction that modifies data through commit command
- The database has generated the undo information, and the undo information contains the legacy or old data values changed by the SQL statements of the transaction.
- The database has generated redo log file entries in the redo log buffer of the SGA(System Global Area).
- The redo log file record contains the changes made to the data and rollback block.
- The Changes made to the SGA database buffers, these changes may go to disk before a transaction committed.
When a transaction commit command executes, the following occurs:
- The internal transaction table for the respective undo tablespace records that the transaction has committed, and the corresponding unique SCN (System Change Number) of the transaction is assigned and recorded in the table.
- The LGWR (Log Writer Process) writes redo log entries in the SGA’s redo log buffers to the redo log file. It also .writes the transaction’s SCN to the redo log file
- The database releases locks held on rows and tables.
- The database marks the transaction complete.
Savepoints in Transactions
The intermediate points of transactions are declared as Savepoints. Savepoints separate a lengthy transaction into smaller parts. Savepoints are equally useful in application programs. We can create a savepoint before each function starts If a specific procedure contains numerous functions.
Then, if a function fails, it will be easy to return the data to its actual state before the function began and re-run with revised parameters.
These savepoints are especially useful for avoiding data loss when dealing with recovery models or backup actions. When a commit rollbacks to a specific savepoint. The database releases the data locks obtained by the rollback statements. So transactions waiting for previously locked resources can proceed and stored back.
The following occurs when a transaction is rolled back to a specific savepoint :
- Only the statements that were executed after the savepoint get rollback.
- A database maintains the specified savepoint, but all savepoints that set after the specified one are lost.
- The database releases all table and row locks acquired since that savepoint but holds all data locks acquired earlier to the savepoint.
What is SQL Transaction Log?
The SQL transaction log is a file that contains the logs generated during the logging process for the respective transaction processing that occurred in a database application.
SQL transaction logs are sequential in nature and can split into chunks called a virtual log file.
The SQL transaction log supports the below-mentioned procedure :
- Recovering Incomplete Transactions.
- Rollback SQL Transaction.
- High Availability.
- Rolling a restored database
The respective relational Database will have its corresponding transaction processing log like the SQL server database captures the transactions using the SQL Server transaction log, and the Oracle database has an Oracle transaction log, and so on.
Transaction log backup is a procedure for database backup of transactions that occurred since the last transaction log backup was created.
These database back-ups are performed in Full and Incremental mode.
SQL Transaction Concurrency
Multiple transactions that were running together may access the same database rows during the same period of time, Such simultaneous access events cause Collisions and may result in inconsistency or error, which causes transaction concurrency.
The transaction concurrency rate depends on the overlapping of multiple transactions, the more the overlapping the more transactional concurrency.
Proper handling of collisions requires some work on the part of the application programmer. It is possible to leave the whole concurrency matter in the hands of the DBMS. However, that would almost lead to performance, which everyone would call unacceptable.
So application programmer has a vital role to play while handling collisions properly by using available SQL options, which can increase concurrency. It will eventually help the DBMS along with a wide variety of application plans and connections.
Implementation and Execution of Locking, Timestamp mechanisms are the best way to avoid or delete the majority of transaction concurrency problems automatically.
Read more for SQL wiki @ SQL Guide
Frequently Asked Questions
What is an SQL Transaction?
An SQL Transaction is a set of database operations that are treated as a single logical unit. It ensures data integrity and consistency in databases.
How do I start an SQL Transaction?
To start an SQL Transaction, use the ‘BEGIN TRANSACTION’ command. This indicates the start of a transaction block.
What is the 'COMMIT' command in SQL Transactions?
The ‘COMMIT’ command in SQL Transactions is used to save all the changes made during the transaction permanently to the database.
What does the 'ROLLBACK' command do in SQL Transactions?
The ‘ROLLBACK’ command in SQL Transactions undoes all changes made in the current transaction, reverting the database to its previous state before the transaction started.
What is a 'SAVEPOINT' in SQL Transactions?
A ‘SAVEPOINT’ in SQL Transactions allows partial rollbacks within a transaction. It creates a point to which a transaction can be rolled back without affecting the entire transaction.
How do 'TRY…CATCH' blocks work with SQL Transactions?
‘TRY…CATCH’ blocks in SQL Transactions provide error handling. If an error occurs in the ‘TRY’ block, control passes to the ‘CATCH’ block where you can rollback the transaction.
What are the states of a Database Transaction?
A Database Transaction goes through states: Active, Partially Committed, Committed, Failed, and Terminated. These states represent the lifecycle of a transaction.
What is 'autocommit' in SQL Transactions?
‘Autocommit’ is a feature where SQL statements are automatically committed to the database. No transactions are created and data cannot be rolled back with this feature enabled.
What is the purpose of '@@TRANCOUNT' in SQL?
‘@@TRANCOUNT’ in SQL returns the number of active transactions in the session. It helps in monitoring and managing transaction counts.
Can SQL Transactions be nested?
Yes, SQL Transactions can be nested, allowing for multiple transactions to be opened within one another, each with its own commit and rollback scope.